Hi PenWin,

Just to rule out:

I once noticed the same behaviour (sudden slowness of a query), but it
turned out to be someone on the network was busy copying allmost an
entire harddisk to the server... IOW, a network congestion problem and a
very busy server....

regards,
John





PenWin wrote:

> 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
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>

--
John vd Waeter
www.jvdw.nl
www.gps-carpool.net
www.shotinthedark.nl
www.pdaforms.nl
www.dbapocket.nl

john@...