Hi!

Under Firebird 1.5.5 running under Windows Vista I have a query which
exhibits great variance in execution times - sometimes it takes 1.5
seconds, sometimes much longer (I have seen more than 9 minutes). The
query tries to find records in the data table which are linked to
obsolete records in the history table. Something like:

Data ( DATAKEY integer, DATA1, DATA2, ..., CODE integer )
History ( HISTKEY integer, HISTDATA1, HISTDATA2, ..., CODE integer,
VALIDFROM timestamp, VALIDTO timestamp, DELETED integer )

The query:

SELECT d.datakey, MAX(h.histkey) histkey
FROM data d
LEFT JOIN history h ON h.code=d.code AND h.deleted=0 AND
h.validfrom<='now' AND h.validto>'now'
WHERE COALESCE(d.code,0)<>COALESCE(h.code,0)
GROUP BY d.datakey

Alternative version, with almost the same behavior (I do the COALESCEd
compare in application rather than the query itself):

SELECT d.datakey, (SELECT MAX(h.histkey) FROM history h WHERE
h.code=d.code AND h.deleted=0 AND h.validfrom<='now' AND
h.validto>'now') histkey
FROM data d

"Data" has some 70000 rows. "History" has about 10 rows for each "Data"
row, with indexes on code, on validfrom and on validto (three distinct
indexes). The execution plan (for the former query) looks fine to me:

PLAN SORT (JOIN (Data NATURAL,History INDEX (codeidx,validfromidx)))

What could be the problem?


Thanks, Pepak