Subject Re: [firebird-support] Performance with heavily updated table
Author Steve Wiser
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 Wiser
Specialized Business Software
6325 Cochran Road, Unit 1
Solon, OH 44139
(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:
> 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]