Hello,

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

* How long does it take to only prepare the query and not executing?
* What's the execution plan?
* What's the page buffers value of your database (run gstat -h)?
* What Firebird architecture do you use?
* Did you adjust anything in firebird.conf in respect to RAM usage for
in-memory sorting?
* I guess we need to see your stored procedure in more detail ...



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