Please read the rules of this list at www.yahoogroups.com/community/IB-Architect. This question is off-topic.

For SQL help and other support questions re IB and Firebird, please feel welcome to subscribe and post at the ib-support list. (www.yahoogroups.com/community/ib-support).

Thanks.
Helen
(Mod., WW)

At 10:50 AM 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
>
>-- 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
>
>I believe that I am doing something wrong in the direct query (the second query would take too much work!). Could someone pls explain why the second method is so much faster?
>
>Thanks in advance.
>
>Ray Mond
>
>
>To unsubscribe from this group, send an email to:
>IB-Architect-unsubscribe@onelist.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________