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
_______________________________________________________