> From: David Berg [mailto:DaveBerg@...]

Hi,

> I've definitely found performance problems with Interbase and SQL Server
> when doing:
>
> Select ... From Table T Join LookupTable L On T.Lookup_ID = L.Lookup_ID
>
> I don't exactly where the optimizer is getting confused, but the following
> query runs MUCH faster on both platforms, even though the RI relationship
> between the tables guarantees that the queries are identical:
>
> Select ... From Table T Left Outer Join LookupTable L On T.Lookup_ID =
> L.Lookup_ID

You post got me looking through my code to see what I am doing in these
'Lookup table situation. But I get the opposite results to you when using
an ORDER BY, and I think it is the optimiser messing up..

SELECT
DATA_TBL.ID,
LOOKUP_TBL.NAME,
DATA_TBL.DATE
FROM DATA_TBL
JOIN LOOKUP_TBL ON DATA_TBL.LOOKUP_ID = LOOKUP_TBL.ID
ORDER BY DATA_TBL.DATE DESC

Uses this PLAN, as expected..

PLAN JOIN (DATA_TBL ORDER IDX$DATA_TBL_DATE,LOOKUP_TBL INDEX
(RDB$PRIMARY31))

Adding the LEFT OUTER JOIN..

SELECT
DATA_TBL.ID,
LOOKUP_TBL.NAME,
DATA_TBL.DATE
FROM DATA_TBL
LEFT OUTER JOIN LOOKUP_TBL ON DATA_TBL.LOOKUP_ID = LOOKUP_TBL.ID
ORDER BY DATA_TBL.DATE DESC

Uses this PLAN...

PLAN SORT (JOIN (DATA_TBL NATURAL,LOOKUP_TBL INDEX (RDB$PRIMARY31)))

Which Takes about 4 times as long.


Cheers

Phil Shrimpton
------------------------------
Project JEDI DCOM Team Captain
Project JEDI Library Team
<www.delphi-jedi.org>
Registered Linux User #155621