> Karol Bieniaszewski wrote

>
> 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
>

Karol is exactly right - at least I believe he is.

When you do a "count (*)" on a table with no back versions, the pages of the table are read one by one, so the load on the page cache is minimal - some metadata, parts of the RDB$PAGES table, a pointer page, and one active data page. So the total number of reads is about the total number of pages in the table and nothing is ever forced out of cache.

When you update a record, the old version (or the difference between the two versions) is written elsewhere. In the best case, elsewhere is on the same page - if the full back version is preserved, then it's not re-written at all, Firebird just rearranges some pointers. If you update so many record on a page that there is no place for the old version (full or difference) the the old version goes on an overflow page. If you do all your updates in storage order, then all the old versions from page "n" go to the same overflow page. If, as is more likely, you update the records in random order, each overflow page will have records from many different primary data pages. In your case, I'd guess that each overflow page has back versions from about 100 different data pages.

So, my guess is that you read a record, which causes Firebird to chase down the old versions to see if any can be removed. That brings in an overflow page, but there's still an older version on a different overflow page. That's three page reads for that record. The next record also has two back versions on two different overflow pages ... two more page reads. And so on. Sooner or later, the cache fills and pages get released. Naturally, given the way the world works, the next overflow page you need is the one that was just released.

question: How many writes do you see when you run the "count (*)"?


Good luck,

Ann

<irrelevant>Sorry this is so late, Jim and I have beens sailing on the Saint John River in Canada, warm fresh water, out beyond the reach of cell phones. We're back in civilization temporarily so he can replace the wireless mouse my dog ate and resume coding his next project.</irrelevant>