First query slow on Firebird

The first query run on a large dataset on a Firebird database after starting our application is always very slow. Subsequent calls to the same query (it is a stored procedure) are fine. I assume that this is to do with something 开发者_Python百科being loaded into memory but I could do with a explanation of what and whether there is anything that can be done to get around the issue.

If is a stored procedure the first query it compiles the stored procedure also it fetches the buffers and caches the result. On the second query the procedure is not compiled again (precached) and the results are instant (the fetches are also in memory for some operating systems so no need for disk io)

one way is to optimize the sp or the tables How larger are they? (number of records for each table)

one simple way to optimize this is to put a cron script that will run once per day/hour to prefill the caches so you will get fast sp

Maybe it's not about the query, but the connection time (delay) is long? There was such a problem with [old] Firebird/Interbase engines.

You didn't explain which Firebird version you are using but, in version 2.50, there is a bug (CORE 3227 - slow compilation of stored procedures) that can be the cause of your problem. More details: http://www.firebirdnews.org/?p=5282&utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+FirebirdNews+%28Firebird+News%29