Re: optimizing join query statement

binh_at_cod.nosc.mil (Binh H. Tran) wrote:

>Hi All:
 

>I need help on optimizing a join select statement.
>What I have are three tables A, B, and C with
>112,000, 11,000, and 5,00 records, respectively.
>
>Currently, the select statement we have is:
 

> select count(distinct A.rowid) from A, B, C where
> (A.column1 = B.column1 and A.column2 = C.column2 and
> A.column3 = B.column3 and A.column4 = C.column4)
>
>The problem with this is it takes way too long (>10 minutes)
>to produce the result. I created a few indices for the
>three tables, but It didn't seem to improve the performance
>at all.
 

>Any suggestions?
 

>Thanks,
 

>binh_at_nosc.mil

I've not tried it with a three table join, just two. But I've found it's usually faster to list the table with the least rows first in the FROM clause.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Incoming fire has the right of way!