The only thing that I notice probably could be optimized, is that I doubt it will ever be useful to use an index for validfrom (due to your description). So, change to

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+0<='now' AND h.validto+0>'now'
WHERE COALESCE(d.code,0)<>COALESCE(h.code,0)
GROUP BY d.datakey

Other than that, well, if you run an identical query (note the word identical, it is not identical if it e.g. uses different parameters) twice on the same database with vastly different execution time, then I'd expect either cache (quicker on second run), different plan or something external to the query (e.g. sweep) to be the reason.

One thing I noted on Firebird 1.5 not all too long ago, was that indexed fields with lots of nulls made one of my queries very slow. However, that was not slow sometimes, it was always slow.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of PenWin
Sent: 25. november 2009 13:19
To: Firebird Support
Subject: [firebird-support] Sudden increase in execution times

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