>> 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.

Pepak