I was very surprised by what I observed in Firebird (probably 2.5.4) yesterday and today. After importing 3000 records into a temporary table (on commit preserve), I wrote an (selectable, but containing update) EXECUTE BLOCK that updated a normal table (updating 3000 of 1 million records) based on the contents of this temporary table. I expected the update to take a seconds or at worst a few minutes, but when I left work after two hours it was still running in Database Workbench (most of the time running 100%). When I returned to work today, it had finished (DBW reported it took 15 minutes, but I'm positive no results were visible after two hours, so I guess the 15 minutes were just for the first 25 records it returned) and I ran an almost identical EXECUTE BLOCK that updated a different table. This finished in about one second.
Why such a huge difference in execution speed? The main difference between the two queries is that the quick joins on two integer fields, whereas the slow joins on one VARCHAR(40) field. The temporary and normal table use different collations (NO_NO and ISO8859_1) for this varchar field. There is a composite index for the integer fields and this index has great selectivity. The varchar field also has an index, for records that have a value in this field, it is almost unique, but the field was introduced to the table not all too long ago, so between 70 and 95% of the records will be <null> for this field (hence worse selectivity on the table as a whole). However, since the plan reports use of this index and all the 3000 records in the temporary table contains a value, I wouldn't expect it to matter that irrelevant records in the normal table is null. I also tried running the slow query on a fresh restore of the database. I stopped the query after 15-20 seconds, since that was enough to convince me that it actually was significantly slower than the quick query. The execute blocks had similar similar content to: for select mt.PK from <temporary table> tt join <normal table> mt on <join criteria> where mt.integerfield = 1 into :PK do begin suspend; update <normal table> set field1 = 'K', field2 = '-' where pk = :pk; MyCount = MyCount + rows_affected; end The join criteria were (slow) tt.VarChar40 = mt.VarChar40 (quick) tt.int1 = mt.int1 and tt.int2 = mt.int2 Running the select part without the update is quick in both cases. Can anyone enlighten me regarding what the cause of the performance difference may be? I'm simply puzzled and clueless... Thanks in advance, Set
