[firebird-support] Query slowness
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 2. 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? Is 9 minutes a typical time to sweep a table with 131822 records in a 546MB database? Regards, Gareth Marshall
Odp: [firebird-support] Query slowness
Hi, 1. Garbage collection - many old record versions after update or massive deletion. 2. You have another connection - and after first select data pages go into RAM but you say that you use classic is tis true or may be you use superserver or superclassic? regards, Karol Bieniaszewski - Reply message - Od: garethm gare...@acm.org Do: firebird-support@yahoogroups.com Temat: [firebird-support] Query slowness Data: pon., cze 3, 2013 08:56 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 2. 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? Is 9 minutes a typical time to sweep a table with 131822 records in a 546MB database? Regards, Gareth Marshall [Non-text portions of this message have been removed]
Re: [firebird-support] Query slowness
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 2. 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 __
