> -----Original Message-----
> From: Chris Landowski [mailto:chrisl@...]
> Sent: Miércoles 10 de Enero de 2001 15:46
>
> 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.

The reason is because the two statement aren't the same. You changed the
semantics in the conversion. This means the results you should expect from
the two statements are different. More below:

> 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)))

This is a LEFT [OUTER] JOIN. The JOIN/ON syntax is the recommended way to
handle things. There's no way you can produce an OUTER JOIN in the WHERE
clause in IB with one condition, because it adheres to the ANSI-SQL way of
doing things. In MsSql, you can use non-ANSI, proprietary *= and =*
operators in the WHERE clause to do the same. However, in MsSql v7, MS
itself dumped them in favor of the JOIN syntax.

> SELECT *
> FROM NAME_ADDRESS N
> , BILLING_DATA B
> WHERE 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 JOIN (N ORDER NAME_ADDRESS_00,B INDEX (BILLING_DATA_00))

This is a simple [INNER] JOIN. It's equivalent is the first command without
the LEFT keyword. So, this is fast, but IT'S NOT YOUR DESIRED RESULT.

> Both tables have an index over CO, YR, PARCEL.

That's not the table, but a result of how IB handles left joins.

> The LEFT JOIN example takes forever to execute and just about cripples the
> server. The second example executes almost instantly and the impact on the
> server is almost nothing. I am not all that familiar with optimizing PLANs
> and cannot figure out how to get the LEFT JOIN example to not use NATURAL
> ??????

The short answer is YOU CAN'T. When IB is given an [INNER] JOIN, it builds
PLAN JOIN (t1 ORDER index1,t2 INDEX (index2))
but when it sees a LEFT [OUTER] JOIN, it builds
PLAN SORT (JOIN (t1 NATURAL,B INDEX (index2)))
and there's no way to tell it to use index1 for t1. If you try to force the
supposed good plan, IB refuses it.

> I would prefer to use LEFT JOINs for readability, but the majority of the
> tables in my database have these same CO, YR, PARCEL fields and LEFT JOINs
> are causing me allot of grief. What kind of problems can I expect by using
> join criteria in the WHERE clause of a TIB_Query component ?

You will have to think in another solution because your left join as it
seems to use all fields from both tables, cannot be decomposed in a main
select plus an inner select (subquery) that returns just one field. So,
performance cannot be enhanced easily. So, the only workaround I can suggest
to you is:

create procedure <proc_name>
returns (<list of fields>)
as begin
for SELECT *
FROM NAME_ADDRESS N
ORDER BY N.CO, N.YR, N.PARCEL
into :f1, :f2, :f3, ... :f8
do begin
select * from BILLING_DATA B
where B.CO = :f1
AND B.YR = :f2
AND B.PARCEL = :f3
into :f9, :f10, :f11, :f12, :f13;
suspend;
end
end

And do a
select * from <proc_name>
as it were a table.

C.