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?

If you are deleting in one of the tables regularly, then you might
suffer from garbage collection at SELECT time, depending on the
architecture (SuperServer, ClassicServer) you use.



--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com
My blog:
http://blog.upscene.com/thomas/