> Ah, alright. Thought so. It has been discovered (and discussed a few
> weeks/months? ago) that preparing a query on largish tables can take a
> quite some time. I can't recall the outcome of the discussion at the
> moment, but I think the recommendation is to strictly use prepared
> queries and re-use them and/or have some sort of a prepared statement
> cache at the client-side.

Will it be enough to put the query into the stored procedure with STARTDATE / STOPDATE parameters?

> Another questionable setting is a page size of 4K. Especially with your
> huge table, my chrystal ball tells me, that indexes on that table might
> have an index depth > 3, which is not good. Run gstat -r -i and check
> out the index depth. You can also send me the output privately, if you like.

Here are the stats:

Database "E:\7\777.FDB"
Database header page information:
Flags 0
Checksum 12345
Generation 2658
Page size 4096
ODS version 11.2
Oldest transaction 2641
Oldest active 2642
Oldest snapshot 2642
Next transaction 2646
Bumped transaction 1
Sequence number 0
Next attachment ID 6
Implementation ID 26
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Oct 4, 2012 8:57:47
Attributes force write

Variable header data:
Sweep interval: 20000
*END*


Database file sequence:
File E:\7\777.FDB is the only file

Analyzing database pages ...
TASK (205)
Primary pointer page: 693, Index root page: 694
Average record length: 182.34, total records: 56321425
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 3155184, data page slots: 3155184, average fill: 87%
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 3155183

Index IDX_IDS_DATE_RA (13)
Depth: 4, leaf buckets: 84402, nodes: 56321425
Average data length: 0.06, total dup: 56095180, max dup: 3416489
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 84401

Index IDX_TASK_IDCONTRACT (10)
Depth: 3, leaf buckets: 82924, nodes: 56321425
Average data length: 0.00, total dup: 56321424, max dup: 56321424
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 82923

Index IDX_TASK_IDRFID (11)
Depth: 3, leaf buckets: 82924, nodes: 56321425
Average data length: 0.00, total dup: 56321424, max dup: 56321424
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 82923

Index IDX_TASK_IDSHEDULE (9)
Depth: 3, leaf buckets: 83101, nodes: 56321425
Average data length: 0.01, total dup: 56152049, max dup: 13102888
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 83100

Index IDX_TASK_RA_IDC_NN (14)
Depth: 4, leaf buckets: 100214, nodes: 56321425
Average data length: 1.03, total dup: 41870112, max dup: 1848518
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 100213

Index IDX_TASK_RFID_CODE (12)
Depth: 3, leaf buckets: 83185, nodes: 56321425
Average data length: 0.01, total dup: 56321424, max dup: 56321424
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 83184

Index PK_TASK (0)
Depth: 4, leaf buckets: 97215, nodes: 56321425
Average data length: 1.01, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 97214

Index TASK_DODATE_A (1)
Depth: 3, leaf buckets: 83062, nodes: 56321425
Average data length: 0.01, total dup: 56321083, max dup: 4510863
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 83062

Index TASK_DODATE_D (2)
Depth: 3, leaf buckets: 83062, nodes: 56321425
Average data length: 0.01, total dup: 56321083, max dup: 4510863
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 83062

Index TASK_IDCLIENT_A (3)
Depth: 3, leaf buckets: 85451, nodes: 56321425
Average data length: 0.17, total dup: 47105057, max dup: 2002164
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 85450

Index TASK_IDCLIENT_D (4)
Depth: 3, leaf buckets: 85935, nodes: 56321425
Average data length: 0.17, total dup: 47105057, max dup: 2002164
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 85934

Index TASK_IDLOCATION_A (5)
Depth: 3, leaf buckets: 85458, nodes: 56321425
Average data length: 0.17, total dup: 47103478, max dup: 2005487
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 85457

Index TASK_IDLOCATION_D (6)
Depth: 3, leaf buckets: 85929, nodes: 56321425
Average data length: 0.17, total dup: 47103478, max dup: 2005487
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 85928

Index TASK_RECORD_ACTIVE_A (7)
Depth: 3, leaf buckets: 82932, nodes: 56321425
Average data length: 0.00, total dup: 56321423, max dup: 39119519
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 82931

Index TASK_RECORD_ACTIVE_D (8)
Depth: 3, leaf buckets: 82932, nodes: 56321425
Average data length: 0.00, total dup: 56321423, max dup: 39119519
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 82931

> Increasing the page size will reduce the index depth for largish
> indexes. Changing the page size can be done via a backup/restore cycle
> with gbak.

I've never done that before. So I am firsty backing up my database and druing restore I have an option to setup this?

> I have an excel sheet, which calculates the approx. RAM usage based on
> different settings and environment pre-requisites. So, if you tell me
> the following variables:

I will tell you for my both configurations, one is developer machine and second is the server machine. Please run your magic formulas for both of the configurations:)

> RAM available for Firebird

By RAM available for Firebird you mean total RAM of the systems? I assume that here.

DevMachine: 8 GB, Server: 8 GB

> Number of Firebird server instances

1 server instance on both of the systems.

> AVG number of databases per Firebird server instance

1 database per instance on both of the systems

> Approx. AVG number of connections per database

It is a bit hard for me to determine...because I do not know if I think correctly here... On both systems we have classic server instance. So when I see fb_inter_server.exe that means we have one connection per proccess? Or can I have multiple connections inside this one proccess? (Those processess are spawning threads which can connect to a database simultanously). So I need to make an assumption here also.

DevMachine 2 connetions, Server 20 connections.

> >
> > What is interesing: I need to restart my machine to recreate the slow execution time.
>
> Or unpreparing the query and/or disconneting in case of Classic to purge
> the local per-connection page cache.

As I've mentioned earlier I've restarted Firebird (without restarting machine) and after this, query was running fast.


Thank you Tomas!:)