Subject Re: [firebird-support] Performance with heavily updated table
Author Helen Borrie
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
>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

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.

Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"