Have you checked transaction statistics when things are slow? The typical reason for gradual slowdown is that there is one or more transactions that started quite a long time ago and hasn't finished yet. This prevents garbage collection and may make Firebird very slow. It is easily visible by subtracting the value reported for the oldest (active) transaction from the next transaction. If that gap is too large, you probably have one or more programs that doesn't commit their transactions frequently enough, something that can lead to there being many versions of each record being around and a lot of work for the Firebird engine (in Firebird, each record can exist in multiple versions although each transaction only sees one version of the record). Deleting records will not help in such circumstances, restarting Firebird will quickly see a speed increase, but often followed by a new slowdown. The real way to fix this problem is to rewrite the programs accessing Firebird so that they handle transactions properly.
If you delete lots of records, some cleanup has to be done. Typically, this is done the next time something accesses the table. You can prevent users from seeing this kind of slowdown by e.g. doing a SELECT COUNT(*) from the table after the deletions (but deletions will not fix situations like the one mentioned in the first paragraph). Another thing that sometimes give the perception of a slow database are queries written in ways that Firebird dislikes (e.g. SELECT ... FROM ... WHERE <field> NOT IN <subselect>) or indices being not appropriate for a particular query. 1.000.000 records is not too much, there are Firebird databases containing many times as many records that have no speed issues. The biggest tables I normally work with probably has 20 or 30 million records and some would even consider that a fairly small Firebird table. HTH, Set tor. 30. jan. 2020 kl. 12:48 skrev Matthias Winkler spmm...@gmail.com [firebird-support] <firebird-support@yahoogroups.com>: > > > Hello, > > I have a table with 1´000´000 entries. > As a cleanup I now delete all except for the most recent ones. > > Do I need to do anything else to improve performance fo the table? > > Rebuilding indices is not possible if the primary key is a autogenernarted > int, right? > > Any other suggestions? > > BR > > MAtthias > > >
