Hello,

>> 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?

What I meant is not to use literals/constants in your statement and
re-create the entire SQL with each change of the values in your WHERE
clause, because changing the SQL necessarily means to unprepare the
statement, which needs to be prepared again for executing.

>> 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

The indexes:

PK_TASK
IDX_IDS_DATE_RA
IDX_TASK_RA_IDC_NN

have a depth of 4, so I strongly recommend to increase the page size to
at least 8K or even 16K.

And, the indexes IDX_TASK_IDRFID, IDX_TASK_IDCONTRACT and
IDX_TASK_RFID_CODE are the worst in respect to index usefulness due to
their selectivity, because there is only one (!) distinct value stored
in the indexed column. As long as these indexes aren't the result of a
FOREIGN KEY constraint, drop them.

The indexes TASK_RECORD_ACTIVE_D, TASK_RECORD_ACTIVE_A and
IDX_TASKIDSHEDULE also index a longish duplicate chain.

>> 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?

Right. Backup the database with gbak. Use the -PAGE_SIZE option upon
restore for the new page size.

>> 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.

Of the system or exluding other RAM intensive processes.

> 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.

You could give the following settings a try:

Page Size: 16K
Page Buffers: 2048
TempCacheLimit in firebird.conf: 134217728
LockHashSlots in firebird.conf could be increased as well, e.g: 10009

>>> 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.

Could be cooperative garbage collection with Classic as well. Is the
large table frequently updated with larger batches?


--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/

Do you care about the future of Firebird? Join the Firebird Foundation:
http://www.firebirdsql.org/en/firebird-foundation/