> ---In firebird-support@yahoogroups.com, <mbrink@...> wrote : > > Hello, > > After spending more than a week trying to resolve the following issue we hope > someone has an answer on this. > > We have a database with about 230 tables and 200 stored procedures which is > being used by our customers. > Each customer have a dedicated installation with it's own database varying in > size from 50MB up to 2GB. > At the moment they use Firebird 2.5.1.26351 Super-Classic Win32 on different > platforms (Win 7 Pro, Win 10 Pro, Windows server 2012 R2). > > The issue occurs when we execute a complex (recursive) stored procedure after > the database engine has been idle for about 20 minutes.
Define "idle": no active connections ? no connections at all ? What about other activities on this host ? > Executing the stored procedure after those 20 minutes can take around 18-20 > seconds to complete, while it normally takes around 0,1 seconds. > Even after restarting the Firebird service executing the stored procedure > immediately never takes longer then 3-4 seconds (unless the 20 minutes are > passed). Looks like database file is removed from system cache after 20 min, probably due to load by another processes. You may use Sysinternals RamMap utility to check this guess. > Things we tried: > > * Restarting the Firebird service. > Even if we restart the Firebird service between those 20 minutes, once the > 20 minute mark is reached it simply takes around 20 seconds again. > * Installed firebird and the database on an SSD (Samsung Evo 500GB) under > Windows 10 Pro (Fall Creators update) with default configuration. > - No difference. > * Turned off inactivity settings in Windows for the HDD, so Windows doesn't > turn off the HDD. > - No difference. > * Upgraded Firebird to version 2.5.7.27050. > - No difference. > * Upgraded Firebird to version 3.0.2 using the migration guide > (backup/restore, etc.). > - No difference. > > When we insert a record into a random table within the same connection and > transaction before executing the stored procedure, > after those 20 minutes the insert is fast and executing the stored procedure > takes around 20 seconds again. > > >This is the trace of what happens when we execute the stored procedure after >20 minutes (watch the prepare of statement 70). ... Trace show that prepare call takes 18 sec, execute is fast. Prepare could be slow if - there is one or more huge tables affected by the query - optimizer should estimate cardinality of tables and it should read all Pointer Pages of the given table(s) - there is a lot of different privieges granted to the objects affected by the query (stored procedure itself, all tables\views\SP's it uses and all objects of whole call tree) - even if there is not much provileges but execution tree is big - engine should load all corresponding metadata objects and it could take time Regards, Vlad
