--- In firebird-support@yahoogroups.com, "nathanelrick" wrote: > > > Just run this query and show us results: > > > > SELECT R.RDB$RELATION_NAME, COUNT(*) > > FROM RDB$PAGES PG JOIN RDB$RELATIONS R > > ON PG.RDB$RELATION_ID = R.RDB$RELATION_ID > > WHERE PG.RDB$PAGE_TYPE = 4 > > AND R.RDB$RELATION_NAME IN ('DESC_VARCHAR', 'DESC_EMPTY') > > GROUP BY R.RDB$RELATION_NAME > > DESC_BLOB: 1872 > DESC_EMPTY: 135 > DESC_VARCHAR: 2166 > > > > Also, could you repeat the test using bigger page cache, say > 1200 pages > > ? > > With page buffer to 1200 pages, nothing change too much, but with a buffer of > around 3000 page yes stat become to be better (but not for the first select, > we need to do several select to see the speed go down, i thing the time to > read the page from disk to the cache)
Well, it fully explains the issue. When Firebird prepared the query it calculates estimate number of rows in relation. To do it, engine reads all pointer pages and calculate number of data pages in relation. So, to fix your issue, it is enough to use prepared queries as already was suggested here. Large page cache allows to cache all PP's after first read and demonstrate that conclusion above is correct. I don't offer you to set big page cache at worker machine, it was needed just for confirmation. Hope this helps, Vlad