November 20, 2007 at 4:44 am
Declare @a -- Same structure as B. In other words a temporary table with same structure as B
(
ID
)
Delete
B
From
@a TempA
inner join
B
on
TempA.ID = B.ID
-- This update will never execute as the common records have already been deleted .... My doubt
-- being that will this piece of code effect the execution time of a stored proc
Update
B
Set
ID = TempA.ID
From
@a TempA
inner join
B
on
TempA.ID = B.ID
-- In the following piece of code an unnecessary left outer join has been used ... will this piece of
-- add to the execution time of the code even in case there are no rows common to the database
-- there for the left outer join will result in all rows with B.ID as Null
Insert into B
(
ID
)
Values
(
Select
ID
From
@a TempA
left outer join
B
on
TempA.ID = B.ID
Where
B.ID is Null
)
My basic question being that even in case there are no rows common to the table will the performance of the query without the join be better than the performance of the query with the left join
November 20, 2007 at 5:58 am
Well, basically, if you don't need a join, don't use one. Even if there are no rows, the engine needs to look at the table to determine the fact that there are no rows. If you have to have two different queries to satisfy two different business requirements even though there is only a relatively small difference between them, it's usually better to have two of them. Just be sure that the performance hit is worth the effort. Test it both ways, watching the query plan (scans instead of seeks, lookup operations) and the I/O.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 21, 2007 at 6:42 am
I agree, usually the optimiser is clever enough to know what to do - however experience tells me this is not always the case so you should just check the created plans to be sure.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply