Performance - Left Outer Joins VS Updates

We currently are in a debate on what is quicker, doing a query in a SP
that has Nth left outer joins or doing a insert followed by update
queries. let me lay out a simple scenerio. Please advise, the DBA
group is set on saying example 2 is better (they keep telling me Im
doing too many table scans).
Table "Store" has 1 Record
StoreNum: 1
Table "StoreDate" has many records that are associated to the Store
table; we will say 3 records fo this example
StoreNum: 1
DateType:1
DateValue: 1/1/2001
StoreNum: 1
DateType:2
DateValue: 2/2/2002
StoreNum: 1
DateType:3
DateValue: 3/3/2003
EXAMPLE 1
Create table X
StoreNum int not null,
OpenDate datetime null,
CloseDate datetime null,
LastInspectionDate datetime null)
Insert into X (StoreNum, OpenDate, CloseDate, LastInspectionDate)
Select S.StoreNum, SD1.DateValue As OpenDate, SD2.DateValue As
CloseDate, SD3.DateValue As LastInspectionDate
>From Store S Left Outer Join StoreDate SD1 ON
S.StoreNum = SD1.StoreNum And SD1.DateType = 1 Left Outer Join
StoreDate SD2 ON
S.StoreNum = SD2.StoreNum And SD2.DateType = 2 Left Outer Join
StoreDate SD3 ON
S.StoreNum = SD3.StoreNum And SD3.DateType = 3
EXAMPLE 2
Create table X
StoreNum int not null,
OpenDate datetime null,
CloseDate datetime null,
LastInspectionDate datetime null)
Insert into X (StoreNum, OpenDate, CloseDate, LastInspectionDate)
Select S.StoreNum, null As OpenDate, null As CloseDate, null As
LastInspectionDate
>From Store S
Update X
set opendate = datevalue
from storedates sd
where x.storenum = sd.storenum
and sd.datetype = 1
Update X
set closedate = datevalue
from storedates sd
where x.storenum = sd.storenum
and sd.datetype = 2
Update X
set lastinspectiondate = datevalue
from storedates sd
where x.storenum = sd.storenum
and sd.datetype = 3Did you test it for yourself with SET STATISTICS IO ON?
You could easily improve Ex 2 by combining the three UPDATEs but I
would try something different from either solution:
INSERT INTO X (storenum, opendate, closedate, lastinspectiondate)
SELECT storenum,
MAX(CASE WHEN datetype = 1 THEN datevalue END),
MAX(CASE WHEN datetype = 2 THEN datevalue END),
MAX(CASE WHEN datetype = 3 THEN datevalue END)
FROM Store
WHERE datetype BETWEEN 1 AND 3
GROUP BY storenum ;
David Portas
SQL Server MVP
--|||The examples are not equivalent. The first example will insert the number
of rows in Store, leaving existing rows in the inserted table unaffected.
The second example will insert the same number of rows, but will update
EVERY row in the inserted table (that joins successfully). I can't imagine
that the insert/update logic is "superior" in any way. Is there an
assumption that the inserted table is empty?
I'll ignore the potential logic flaws based on the design assumptions (e.g.,
store has a 1:1 relationship with storedate for rows where type = 3). The
3rd datetime column named "LastInspectionDate" seems to contradict the
assumed 1:1 relationship.
As David indicated, you don't even need to join store to storedate to
generate the correct information (unless there is some "irregular" aspect to
your design/schema that was not mentioned).