From: Vlad K. <hv...@us...> - 2008-02-27 20:27:50
|
>>> Is this a known issue? >>> >> >> Slow execution with empty cache is always expected. Note also, turning off >> FileSystemCache also turned off any read-ahead made by OS. >> >> Regards, >> Vlad >> > But should it be 60 times slower? And should it matter whether the table > within the EXISTS contained a BLOB field (a BLOB field that wasn't > referenced in the query at all, the query was 20 times faster if the > BLOB field were NULL for all records). Doing COUNT(*) and then the query > can be done in 20 seconds, without the COUNT(*) it takes 10 minutes. > Turning off FileSystemCache was just a way to try to get the slowness to > appear without having to restart the computer, and may (for all I know) > be unrelated to the problem with the query being slow when run as the > first query after a computer restart. When you run COUNT(*) you fill FB's own cache by pages of this table. So subsequent execution is fast. Also note that COUNT(*) didn't read blobs stored off data page. The same effect if all blobs in table is null's. Simple COUNT(*) with cold FB's cache is faster then more complex query because COUNT(*) made one scan of table pages in more or less disk order (especially if database just restored) which is much more faster than many random reads issued by more complex query. Many databases (almost all for my knowledge) are able to perform read-ahead (prefetch, multy-block reads) to improve such scenario. In Firebird code base there is old Borlands attempt to implement prefetch (ifdef'ed by SUPERSERVER_V2) but it never worked, AFAIK. Read-ahead implementation is subject for R&D for the near future, it is second by importance after SMP, if you want to know my opinion ;) Regards, Vlad |