--- In [email protected], Svein Erling Tysvær wrote: > > >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))) > > > >If anybody has any ideas on how to speed up the initial query, I'd be most > >grateful. > > > Well, Eric, > your query confuses me: > > 1) Why do you group on something not an output field? I didn't think that was > allowed. > 2) LEFT JOIN followed by referencing to a field in the table being NOT NULL > makes the LEFT JOIN in reality become a (inner) JOIN, sometimes that's OK for > optimization, but you haven't mentioned that being the case here. > 3) IN is a potential slowdown > > Try changing to something like: > > with biditems32829 as > (select distinct payid from biditems where jobid=32829) > select bp.contkey, avg(bp.price) AvgPrice > from bidprices bp > join master m on m.jobid=bp.jobid > join biditems bi on bi.jobid=bp.jobid > and bi.sequence=bp.sequence > join biditems32829 bi32829 on bi32829.payid = bi.payid > join payitems pi on pi.payid=bi.payid > join bidders b on b.jobid=bp.jobid > and b.contkey=bp.contkey > where m.biddate>='9/17/2006' > and m.distnum=10 > and pi.itemnum is not null > and bp.contkey=4426 > and bi.price<>0 > group by bp.contkey > order by bp.contkey > > Please report back whether this helps or not (gets the right result and is > quick). As I said, your query confuses me, so I'm uncertain whether my query > gets the right result. > > HTH, > Set > Svein, Your version of the query works MUCH better. I had simplified my query just a bit and didn't see that I was grouping on a column I had taken off, so I put it back in.
When I modify the query to group on 4 different "contkey"'s the query will take 49 seconds to execute on the first try (down from 2.5 minutes) and subsequent queries are taking .33 seconds. I will try to tweak this some more, but you have helped me out tremendously, as I've been dealing with this issue for quite some time. Thanks, Eric
