>> 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.
>
> Aggregate functions can be time consuming on databases using MVCC like Firebird. The database has to look at the individual records (and probably several versions of some of them - depending on your use of updates and transactions). I've no clue why things are 14 times slower on first execution than later executions, but maybe Thomas or someone else might answer that question when we get to learn more about your system.
>
> However, one way that sometimes may speed up queries like yours (it cannot be used in all situations, we don't know enough about your database to tell whether it can be useful for you or not), is to use aggregate tables that are populated through triggers. E.g.
>
> CREATE TABLE AggregateTable
> (MyPK INTEGER PRIMARY KEY, /* Have a generator and trigger to fill this */
> UNIQUE_ID INTEGER,
> MySum INTEGER);
>
> INSERT INTO AggregateTable(UNIQUE_ID, MySum) /* A one time operation */
> SELECT UNIQUE_ID, SUM(MyField)
> FROM FortyMillionsRecordsTable
> GROUP BY 1;
>
> CREATE TRIGGER InsertAggregate FOR FortyMillionsRecordsTable ACTIVE AFTER INSERT
> AS BEGIN
> INSERT INTO AggregateTable(UNIQUE_ID, MySum)
> VALUES(new.UNIQUE_ID, new.MyField);
> END;
>
> CREATE TRIGGER DeleteAggregate FOR FortyMillionsRecordsTable ACTIVE AFTER DELETE
> AS BEGIN
> INSERT INTO AggregateTable(UNIQUE_ID, MySum)
> VALUES(old.UNIQUE_ID, -old.MyField);
> END;
>
> CREATE TRIGGER UpdateAggregate FOR FortyMillionsRecordsTable ACTIVE AFTER UPDATE
> AS BEGIN
> IF (old.UNIQUE_ID IS DISTINCT FROM new.UNIQUE_ID OR old.MySum IS DISTINCT FROM new.MySum) THEN
> BEGIN
> INSERT INTO AggregateTable(UNIQUE_ID, MySum)
> VALUES(old.UNIQUE_ID, -old.MyField);
> INSERT INTO AggregateTable(UNIQUE_ID, MySum)
> VALUES(new.UNIQUE_ID, new.MyField);
> END
> END;
>
> Then, modify your procedure to do SUM(AggregateTable) rather than SUM(FortyMillionsRecordsTable), and regularly (it might be daily, it might be monthly depending on how often updates happens) do something like:
>
> EXECUTE BLOCK AS
> DECLARE VARIABLE I INTEGER;
> BEGIN
> I = SELECT GEN_ID(AggregateTableMyPK, 0);
>
> INSERT INTO AggregateTable(UNIQUE_ID, MySum)
> SELECT UNIQUE_ID, SUM(MyField)
> FROM AggregateTable
> WHERE MyPK< :I;
>
> DELETE FROM AggregateTable
> WHERE MyPK< :I;
> END

Aggregate tables (materialized views), hmm lovely. One of my preferred
topic from the past: ;-)

http://www.ibphoenix.com/resources/documents/general/doc_1



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