Do you have automatic sweeps turned on? Not sure if that matters or not.

If not, maybe run the gfix sweep command and try your select count again.

-steve


--
Steve Wiser
President
Specialized Business Software
6325 Cochran Road, Unit 1
Solon, OH 44139

www.specializedbusinesssoftware.com
www.docunym.com
(440) 542-9145 - fax (440) 542-9143
Toll Free: (866) 328-4936

On Wed, Aug 7, 2013 at 5:04 AM, Tim Ward <tdw@...> wrote:

> **
>
>
> 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
>
> Thanks
>
> --
> Tim Ward
>
>
>
> This message and any files transmitted with it may contain information
> that is privileged, confidential, and exempt from disclosure under
> applicable law. They are intended solely for the use of the intended
> recipient. If you are not the intended recipient, distributing, copying,
> disclosing, or reliance on the contents of this communication is strictly
> prohibited. If this has reached you in error, kindly destroy this message
> and notify the sender immediately. Thank you for your assistance.
>
> We attempt to sweep harmful content (e.g. viruses) from e-mail and
> attachments, however we cannot guarantee their safety and can accept no
> liability for any resulting damage. The recipient is responsible to verify
> the safety of this message and any attachments before accepting them.
>


[Non-text portions of this message have been removed]