Hi Cao!

How much work does each transaction do? A gap of 10000-20000 may be fine
if each transaction doesn't do to much, but if it inserts/update/deletes
lots of records this can be the cause.

If you don't want to disable and reenable indexes, I guess you could try
SET STATISTICS, but I kind of doubt it will help.

Another thing that can be the cause, is if the selectivity changes so
that Firebird decides to use a different plan for a certain query. Plans
can heavily influence how much work the server has to do, and if the
optimizer gets it wrong, things may become very slow.

Set

mailgroupza wrote:

> Hi Set / Alan
>
>
>> So: What happens if you take a backup/restore of the database? Is the
>> restored database slow when accessing it through your application?
>
>> 1) If yes, then I suspect suboptimal queries. Have you added or
> removed
>> any indexes (making them inactive counts as removing) lately or
> changed
>> some queries? Any particular query that is slow? Have your database
>> grown substantially so that queries now have to do considerably more
> work?
>
> No queries have changed for a while now
> All indexes are active
> No the db has not grown so that the queries need to do more work
> though we have added a table that grows app 1000 records per day
> but is 99.9% inserts very little selects
>
>> 2) If no, then I suspect poor transaction handling. What is your
>> statistics, is there a big gap between oldest interesting/active and
>> next transaction?
>
> Page size 4096
> ODS version 10.1
> Oldest transaction 123867
> Oldest active 135048
> Oldest snapshot 129882
> Next transaction 144569
> Bumped transaction 1
>
>> Please report back your findings and - if the problem remains -
> whether
>> you are using SuperServer or Classic.
>
> Superserver
>
>> I take it that you've not changed server or operating system lately.
> No
>
> Cao