Hi,

If this is really big update count and pages of that table are filled near 100% then row version go to another page and to process one row engine must read all pages as many times as versions pages

But may be i'am wrong - i do not know super internal details about Firebird work

Regards,
Karol Bieniaszewski
Ps. Show us stats of this table when it work slow and stats form mon$ tables

----- Reply message -----
Od: "Tim Ward" <tdw@...>
Do: <firebird-support@yahoogroups.com>
Temat: [firebird-support] Performance with heavily updated table
Data: czw., sie 8, 2013 11:14

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]











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