At 12:16 PM 02-05-02 +0000, sean_brad wrote:

>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?
>
>Thanks for any help,

As others have rightly explained, you are comparing apples and
oranges. But, if you have an index on the column F, you can speed up that
outer join (if it is what you need) by using the STARTING WITH predicate
instead of LIKE. LIKE does not use an index, whereas STARTING WITH does.

Also, that framework has ambiguous syntax: Firebird will not buy it
without a table identifier on column F.

regards,
Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________