Subject Re: [firebird-support] Sudden increase in execution times
Author PenWin
>> 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" 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.
There are no deletes on either of the tables. If hiding from the user is
required, it is done by setting history.deleted to 1.

Architecture is SuperServer.