On 08/08/2013 10:07, Helen Borrie wrote:

>
> At 08:18 p.m. 8/08/2013, Tim Ward wrote:
> >On 07/08/2013 20:21, Steve Wiser wrote:
> >> 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.
> >I understand that we tried running a sweep but it didn't make any
> >difference.
> >
> >But even if it's a sweep that's needed, we still don't understand why
> >that would, apparently, cause the database to re-read the same pages 100
> >times.
>
> Tables <> pages
>
> select count(*) scans rows or, more specifically, row versions. Rows
> are stored on pages. One page will contain only rows from a particular
> table but the pages could be anywhere and the row density is quite
> variable.
>
> In Firebird, select count(*) is about the least useful thing you could
> do to test for overall performance, especially if the symptom is
> habitual progressive degradation. Focus on the tips people are giving
> you about transaction management and write yourself a test suite that
> queries your data in means and mass similar to the production conditions.
>

We understand all that, but I'm afraid I don't see how it addresses my
question, which is

(a) are we understanding the SET STATS ON and gstat output correctly
(b) and if so, why does a table scan read 100 times as many pages as the
table contains after, but not before, heavy update activity?

As explained in a previous post, COUNT(*) *is* "a test suite that
queries your data in means and mass similar to the production
conditions", as we're looking at queries that do table scans on that
particular table which is exactly what COUNT(*) does, except that we've
turned off all concurrent activity and are just testing it on its own.

--
Tim Ward



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