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

Table summary1 is never used. There is something wrong or missing in join
clause.

> 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

What about:

SELECT desc1, SUM(qty) FROM cti_ A
join trxmdate B on B.trxmdate=A.trxmdate
where B.fcode IN ('A1','A2','A3')
GROUP BY desc1
ORDER BY 2

Maybe you have to create the appropriate indexes on cti_ to speed up.

Luiz.

----- Original Message -----
From: <yeohray@...>
To: <ib-support@yahoogroups.com>
Sent: Sunday, April 29, 2001 9:09 AM
Subject: [ib-support] Query joins


> 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-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>