Hello guys.

I am having problem with the query which is constructed like this:

SELECT S.Column1, S.Column2, STORED_PROC.Column1, STORED_PROC.Column2
FROM TABLE SomeTable S
LEFT JOIN STORED_PROC(S.UniqueID) ON 1=1
WHERE S.Data >= '01.07.2012' AND S.Data <='09.07.2012'

Table SomeTable does not have much records, like few hundred, however procedure STORED_PROC is doing some calculations on a table which is having 40 million of records. The calculations are simply a SUM() of columns of some records which foreign key is equal to S.UniqueID.

My problem is that when I run this query for the first time it takes a lot of time to execute and hard disk activity is veary heavy. When I run it for the second time it executes much more faster and hard disk activity is unnoticeable.

I have not measured the HD activity with some tool, I just see that HD light is blinking when I fire up the query for the first time. I guess this is caused by memory swapping?

When I restart my firebird (2.5.1) and run the query it runs as fast as for the second time. However, when I increase the number of records returned by left table (by changing the dates) the query slows down (swapping again?). I need to re-run it and then it is again running fast.

Here are the stats for the first and second execution of the query for the same date range. Nothing in data has changed between the two executions:

Executing statement...
Statement executed (elapsed time: 0.000s).
1825600 fetches, 9 marks, 88863 reads, 6 writes.
0 inserts, 0 updates, 0 deletes, 856790 index, 3374 seq.
Delta memory: 265568 bytes.
Total execution time: 36.321s
Script execution finished.

Executing statement...
Statement executed (elapsed time: 0.000s).
1818330 fetches, 0 marks, 88768 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 856550 index, 410 seq.
Delta memory: 31192 bytes.
Total execution time: 2.527s
Script execution finished.

My question is: what can I do to speed up the query execution for the first time?

Thank you for your advices.

Regards.