Subject Re: [ib-support] Speed of Joins
Author Daniel Rail
At 05/02/2002 09:16 AM, you wrote:
>Hi
>
>I use a join select statement of the following form:
>
>SELECT A,B,C
>FROM Table1 LEFT OUTER JOIN Table2
>ON Table2.D = Table1.D and
> Table2.E = Table1.E
>Where F like 'text%'
>
>But have found it to be very slow.
>
>(The fields used in the join and in the Where clause are indexed)
>
>I've tried replacing it with:
>
>SELECT A,B,C
>FROM Table1, Table2
>Where Table2.D = Table1.D and
> Table2.E = Table1.E and
> F like 'text%'
>
>and the result set is returned much quicker.
>
>Can any one explain why the first method is so much slower. And, if
>there is a good reason for using it, is there a way of speeding it up?

First thing I see. These 2 queries will not return the same results. The
first one is an outer join, so two conditions has to be met: equal or not
equal. The second is the same as an inner join, so one condition has to be
met: equal. Try restructuring the second query so it would match the same
comparisons that the first query would do and compare the speed after doing so.


Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.accramed.ca)