Sorry for the missed join criteria in the 2nd query. It was suppose
to read

SELECT b.desc1, SUM(b.qty) FROM summary1 a
INNER JOIN cti_ b ON a.trxmdate = b.trxmdate
GROUP BY b.desc1
ORDER BY 2

I'll try your suggestion. Thanks.

Ray Mond

--- In ib-support@y..., Helen Borrie <helebor@d...> wrote:
> At 12:09 PM 29-04-01 +0000, you wrote:
> >A query :)
> >
> >Table information: cti_
> >Size : ~ 6MB
> >Total rows : ~47000
> >Rows meeting sub query : 1500
> >Indexes : composite index on (fcode, trxmdate)
> > single index on (trxmdate)
> >
> >
> >-- direct query
> >SELECT desc1, SUM(qty) FROM cti_
> >WHERE trxmdate in (
> > SELECT trxmdate FROM cti_ WHERE fcode IN ('A1','A2','A3')
> > )
> >GROUP BY desc1
> >ORDER BY 2
> >
> >= 38 seconds
>
> The slowest part will be the selection criteria because IN cannot
use indexes. If desc1 is not indexed, then the aggregation would be
slow too.
>
> Does cti_ have a primary key? If so, is it (fcode, trxmdate)? In
that case, IB would have created a RDB$PRIMARYnnnnn index for it
which will raise an ambiguity for the optimizer with your other
indexes and the optimizer is probably using NATURAL sort order
anywhere it might use those indexes.
>
>
> >-- indirect query
> >CREATE TABLE summary1 (trxmdate VARCHAR(20))
> >
> >INSERT INTO summary1
> > SELECT trxmdate FROM cti_ WHERE fcode IN ('A1','A2','A3')
> >
> >SELECT b.desc1, SUM(b.qty) FROM summary1 a
> >INNER JOIN cti_ b
> >GROUP BY b.desc1
> >ORDER BY 2
> >
> >= 2 seconds
>
> This isn't a proper query - you have no JOIN criteria. Does it
return any results? You would need something like this:
>
> SELECT b.desc1, SUM(b.qty) as Something FROM summary1 a
> JOIN cti_ b
> ON b.Something = a.Something (b.trxmdate = a.trxmdate???)
> GROUP BY b.desc1
> ORDER BY 2
>
> Helen
>
> All for Open and Open for All
> InterBase Developer Initiative · http://www.interbase2000.org
> _______________________________________________________