| Subject | First query very slow |
|---|---|
| Author | edbuescher |
| Post date | 2013-02-04T22:06:14Z |
I have a query with an aggregate function (AVG) that I believe is the cause for the very slow execution times on the the first instance. The first execution will take around 2.5 minutes and subsequent queries will take around 6 seconds. I am using FB 2.5.2 in SuperServer mode with a page size of 8192. My configuration shows my buffers at 4000, but gstat reports 0, and if so then the buffers would be the default of 2048.
Here's the query:
select bidprices.contkey, avg(bidprices.price) AvgPrice
from bidprices
join master on master.jobid=bidprices.jobid
join biditems on (biditems.jobid=bidprices.jobid and biditems.sequence=bidprices.sequence)
left outer join payitems on (payitems.payid=biditems.payid and
(payitems.payid in (select payid from biditems where biditems.jobid=32829)))
join bidders on (bidders.jobid=bidprices.jobid and bidders.contkey=bidprices.contkey)
where (master.biddate>='9/17/2006') and (master.distnum=10) and
(payitems.itemnum is not null) and (bidprices.contkey=4426) and
(bidprices.price<>0)
group by bidprices.contkey, biditems.payid
order by bidprices.contkey, biditems.payid
The stats for the 1st execution are:
5769887 fetches, 4 marks, 61392 reads, 4 writes.
0 inserts, 0 updates, 0 deletes, 1592970 index, 0 seq.
Delta memory: 1637868 bytes
Total execution time: 0:02:41 (hh:mm:ss)
2nd time:
5769887 fetches, 2 marks, 61338 reads, 2 writes.
0 inserts, 0 updates, 0 deletes, 1592757 index, 0 seq.
Delta memory: -148 bytes
Total execution time: 5.168s
Here are the generated plan(s):
PLAN (BIDITEMS INDEX (BIDITEMS_PAYID, BIDITEMS_JOBID))
PLAN SORT (JOIN (JOIN (JOIN (BIDPRICES INDEX (BIDPRICES_CONTKEY2), MASTER INDEX (RDB$PRIMARY4), BIDITEMS INDEX (RDB$PRIMARY12)), PAYITEMS INDEX (RDB$PRIMARY11)), BIDDERS INDEX (RDB$PRIMARY13)))
Adapted Plan
PLAN (BIDITEMS INDEX (BIDITEMS_PAYID, BIDITEMS_JOBID)) PLAN SORT (JOIN (JOIN (JOIN (BIDPRICES INDEX (BIDPRICES_CONTKEY2), MASTER INDEX (INTEG_15), BIDITEMS INDEX (INTEG_40)), PAYITEMS INDEX (INTEG_38)), BIDDERS INDEX (INTEG_43)))
If anybody has any ideas on how to speed up the initial query, I'd be most grateful.
Thanks,
Eric
Here's the query:
select bidprices.contkey, avg(bidprices.price) AvgPrice
from bidprices
join master on master.jobid=bidprices.jobid
join biditems on (biditems.jobid=bidprices.jobid and biditems.sequence=bidprices.sequence)
left outer join payitems on (payitems.payid=biditems.payid and
(payitems.payid in (select payid from biditems where biditems.jobid=32829)))
join bidders on (bidders.jobid=bidprices.jobid and bidders.contkey=bidprices.contkey)
where (master.biddate>='9/17/2006') and (master.distnum=10) and
(payitems.itemnum is not null) and (bidprices.contkey=4426) and
(bidprices.price<>0)
group by bidprices.contkey, biditems.payid
order by bidprices.contkey, biditems.payid
The stats for the 1st execution are:
5769887 fetches, 4 marks, 61392 reads, 4 writes.
0 inserts, 0 updates, 0 deletes, 1592970 index, 0 seq.
Delta memory: 1637868 bytes
Total execution time: 0:02:41 (hh:mm:ss)
2nd time:
5769887 fetches, 2 marks, 61338 reads, 2 writes.
0 inserts, 0 updates, 0 deletes, 1592757 index, 0 seq.
Delta memory: -148 bytes
Total execution time: 5.168s
Here are the generated plan(s):
PLAN (BIDITEMS INDEX (BIDITEMS_PAYID, BIDITEMS_JOBID))
PLAN SORT (JOIN (JOIN (JOIN (BIDPRICES INDEX (BIDPRICES_CONTKEY2), MASTER INDEX (RDB$PRIMARY4), BIDITEMS INDEX (RDB$PRIMARY12)), PAYITEMS INDEX (RDB$PRIMARY11)), BIDDERS INDEX (RDB$PRIMARY13)))
Adapted Plan
PLAN (BIDITEMS INDEX (BIDITEMS_PAYID, BIDITEMS_JOBID)) PLAN SORT (JOIN (JOIN (JOIN (BIDPRICES INDEX (BIDPRICES_CONTKEY2), MASTER INDEX (INTEG_15), BIDITEMS INDEX (INTEG_40)), PAYITEMS INDEX (INTEG_38)), BIDDERS INDEX (INTEG_43)))
If anybody has any ideas on how to speed up the initial query, I'd be most grateful.
Thanks,
Eric