At 01:46 PM 10-01-01 -0600, you wrote:

>I found the following statement in the help for the TIB_Query component
>which has me a little concerned: "This biggest gotcha is if you place join
>criteria in the WHERE clause. IBO does not like this in the buffered query
>and problems could result."
>
>The reason this has me concerned is because I cannot seem to get acceptable
>performance using LEFT JOIN with an order by clause instead of using join
>criteria in the WHERE clause.
>
>Here is an example to help clarify:
>
>SELECT *
>FROM NAME_ADDRESS N
>LEFT JOIN BILLING_DATA B
> ON B.CO = N.CO
> AND B.YR = N.YR
> AND B.PARCEL = N.PARCEL
>ORDER BY N.CO, N.YR, N.PARCEL
>
>Here is the resulting plan:
>PLAN SORT (JOIN (N NATURAL,B INDEX (BILLING_DATA_00)))

Specify it without "LEFT" and look at the plan then.

Also, although some say it makes no difference to joins, try turning the
match criteria around so that the sort fields are what the joined tables
fields are being compared with, i.e.

SELECT *
FROM NAME_ADDRESS N
JOIN BILLING_DATA B
ON N.CO = B.CO
AND N.YR = B.YR
AND N.PARCEL = B.PARCEL
ORDER BY N.CO, N.YR, N.PARCEL

I think these two ploys will make the optimizer use the N index for
sorting. According to SQL rules it shouldn't make any difference but
practice speaks otherwise...

fwiw, you could eliminate the ambiguities confronting the optimizer if you
normalized on that CO,YR,PARCEL key structure. If it is that prevalent
throughout the database, it really ought to be in its own table with a
surrogate key which the other tables use as a link entity. Still, maybe
you don't want to go there...

H.

Helen

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