| Subject | Re: Slow execution of Stored Procedure after 20 min. inactivity |
|---|---|
| Author | |
| Post date | 2017-12-06T09:50:26Z |
> ---In firebird-support@yahoogroups.com, <mbrink@...> wrote :After spending more than a week trying to resolve the following issue we hope someone has an answer on this.>> Hello,
>
>
>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).
>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