Have you eliminated the possibility of a large gap between the oldest
active and next transaction? I think this is the most common cause for
queries gradually becoming slower, and the fix is to take better care of
your transactions so that no transactions last too long.

With proper indexing, most sensible queries will be reasonably fast more
or less regardless of the number of records in your tables (well, I
normally work with tables of about 1 million records, and only rarely
with a table with more than 10 million rows) - amongst the exceptions to
this are of course aggregations like 'select count(*)' taking longer if
it has to count a lot of records.

Show us some statistics, the query and plan, and someone on this list is
likely to help you find the culprit.

Set

Sean wrote:

> When the table is small, the query is very fast. After it grows to
> more than 10 million records, query becomes slower and slower. Since
> I don't query all records but only a portion, I think splitting table
> into smaller ones can help improve query speed?
>
> --- In firebird-support@yahoogroups.com, "Martijn Tonies" wrote:
>> But what makes you think your query will be faster if your table
>> data is split across more tables?
>>
>> Martijn Tonies