>I'm trying to understand why performance degrades dramatically during the lifetime of a heavily updated table.
>
>The query we're using as an example is:
>
>SELECT COUNT(*) FROM MYTABLE
>
>After the database has been backed up and restored, this query runs as expected, the stats from SET STATS ON
>showing that the number of page reads is about the same as the number of pages in the table reported by
>
>gstat <db> -t "MYTABLE" -r
>
>After the application has been running for a while, which involves lots of updates to MYTABLE, the query gets
>dramatically slower, and the stats from SET STATS ON show that the number of page reads has gone up by a factor
>of 100 or so (the query time has gone from a fraction of a second to many seconds, the table has around 100k
>records).
>
>However, the number of pages in the table as reported by gstat is only a few more, and gstat doesn't appear to
>be reporting a non-trivial number of "versions".
>
>So it looks to us like the COUNT(*) on a table scan is reading about 100 times as many pages as it needs to -
>surely a table scan only needs to read each page of the table once?
>
>What are we misunderstanding here please?
>
>(At the point where we're re-running and measuring the query the updating activity has been stopped for a while,
>so there's no issue with concurrent accesses to the database.)
>
>Firebird 2.1.5
>Dialect 3
>Classic
>Centos 5.9

I've got a few questions, Tim:

Is it a sudden or gradual slowdown?
Can you show us the statistics when the database is slow?

The most common reasons for gradual slowdowns are one or more long-running transactions - and it doesn't have to be a transaction that does updates, the only transactions that you safely could allow to run for a long time are those that are both read-only AND read committed. Sudden slowdowns are more likely to be due to sweep or someone running CPU intensive queries (e.g. something like SELECT COUNT(*) FROM MYTABLE CROSS JOIN MYTABLE, which should return 10 000 000 000 if MYTABLE contained 100 000 records, or many users simultaneously running SELECT COUNT(*) FROM MYTABLE).

Other things to consider, is whether you actually need COUNT(*). Sometimes it is needed and definitely useful, but it is also a fact that whilst it is extremely simple to write such a query for the programmer, returning the result of a COUNT requires Firebird to look at potentially many versions of each record that match the WHERE clause (or all records if there's no WHERE clause). Hence, my advice is to use COUNT when needed, but avoid it if you counted out of habit or curiosity (particularly if the result is large, normally a user doesn't need to know whether a table contains 80000 or 150000 records, just that the table contains more records than he will be able to look at today).

HTH,
Set