Left Outer Join vs. pkey in (...

[EoRaptor013]

We've got this whole mess trying to export data from Lotus Notes to
Great Plains. As part of the process, we need to ensure that a Notes
record is exported only once, but have the ability to re-export it if
something breaks. So, for that reason (and some others) I decided to
use a simple log table:

create ExpLog (
ExpDateTime Datetime not null,
DBID varchar(16) not null,
docUNID varchar(32) not null,
)

So, the code records the records datetime, and the DBID and docUNID
(which uniquely identifies every Lotus Notes "record"). My question is
in the sql that exports the records. Which is likely to be more
efficient?

select
field1,
...
from LotusNotes LN left outer join ExpLog EL on LN.DBID = EL.DBID and
LN.docUNID = EL.docUNID
where EL.docUNID is null

or

select
field1,
...
from LotusNotes LN
where
ln.DBID not in (select DBID from EL)
and
ln.docUNID not in (select docUNID from EL)

How would one go about figuring this out assuming that the SQL is going
into a DTS package. (I think that means the plan isn't saved?)
Thanks.

[Erland Sommarskog]

EoRaptor013 (rc*******@patmedia.net) writes:

So, the code records the records datetime, and the DBID and docUNID
(which uniquely identifies every Lotus Notes "record"). My question is
in the sql that exports the records. Which is likely to be more
efficient?

select
field1,
...
from LotusNotes LN left outer join ExpLog EL on LN.DBID = EL.DBID and
LN.docUNID = EL.docUNID
where EL.docUNID is null

or

select
field1,
...
from LotusNotes LN
where
ln.DBID not in (select DBID from EL)
and
ln.docUNID not in (select docUNID from EL)
For one thing, they don't seem to mean the same thing to me. Maybe
they do, but it's difficult to understand. There is also a gotcha when
NULL values are involved.

I would write this as:

SELECT field1, ...
FROM LotusNotes LN
WHERE NOT EXISTS (SELECT *
FROM EL
WHERE LN.DBID = EL.DBID
AND LN.docUNID = EL.docUNID)

Not because this is guaranteed to be the most effective, but because
it expresses what you are trying to achieve. Some people claim that
the LEFT JOIN method is more effective. On the other hand, the
optimizer may rewrite one into the other, so it may not make any
differnce at all.
How would one go about figuring this out assuming that the SQL is going
into a DTS package. (I think that means the plan isn't saved?)

I can't see that it would matter whether this is in DTS or elsewhere.
Then again, I don't know DTS, so I may have missed something.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp