--- In firebird-support@yahoogroups.com, 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