----- Reply message -----
Od: "&apos;Leyne, Sean&apos; Sean@... [firebird-support]" <firebird-support@yahoogroups.com>
Do: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Temat: [firebird-support] Re: Speed issues
Data: niedz., lut 8, 2015 21:58

Zoran,

> after full restore of the database, now with 8192 page size,  i got

What is the Index Depth after the restore?

Did you try the change to the order of the columns/fields in the compound index?

> Operations
> ------------------------------------------------
> Read   : 273
> Writes : 0
> Fetches: 1,791
> Marks  : 0

Well the number of reads has dropped by factor of 11 -- very good.

> Query Time
> ------------------------------------------------
> Prepare : 16.00 ms
> Execute : 3,588.00 ms
> Avg fetch time: 170.86 ms

This shows that you are just fighting the OS/hardware -- the time to load the data into OS file cache and then Firebird engine cache is your problem.

I don't see any way to further optimize the first run of the query (pending the answers on Index Depth and change to index column order).

The only thing you could do is to run the query using NULL as parameter values, at application start up, to see if you can force the necessary pages to be loaded into cache and so they are ready for the first user query (the second database level query)


Sean