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