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