At 06:56 p.m. 3/06/2013, garethm wrote:

>Hi,
>
>I am currently using Firebird 2.5.1.26351 using Classic Server 64-bit. The database file was created with Firebird 2.5.1.26351 and is 546MB with a page size of 8192. The server and client are both running on my Windows 7 64-bit development machine.
>
>I ran the following query using FlameRobin:
>
>SELECT COUNT(*)
>FROM T1
>
>This query took 8 minutes 56 seconds to run, and returned a result of 131822 rows.
>
>I then closed FlameRobin and reopened it. The same query ran in 0.117s.
>
>I know that doing a database sweep can cause queries to run slowly, and Flamerobin indicates that the sweep interval is set to 20000. Is the sweep the only possible reason for the performance difference between the two runs of the query with classic server if each run is done on a new connection to the database?

It's almost certainly not a sweep but cooperative garbage collection, following a (previous) transaction that did a large number of updates and/or deletes. In fact, those in the know will follow such an operation on Classic with a deliberate select count(*) query, precisely to clear the garbage on the table and avoid having another user take the hit.

>Is 9 minutes a typical time to sweep a table with 131822 records in a 546MB database?

It could be; but I'm pretty sure we aren't talking about a sweep here: it looks like GC on one table.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__________________________________________________________________