April 10, 2009 at 10:31 am
I have a question from a developer that I'm not able to answer....I was hoping someone here could help out.
Is it more efficient to use multiple joins in a query or to use multiple selects? Below is an example. I prefer the second syntax - easier to read and less IO when I view in profiler. This is just a personel preference though - the IO is 5x more in the first example, but the CPU is a bit higher.
--query using multiple selects
SELECT A.Pro,
(SELECT b.Map_Shape FROM Map_Geography b
WHERE b.Map_Name = 'Area' AND LEFT(A.Ld_Orig_Zip,3) = b.Base) as Orig_Area,
(SELECT b.Map_Shape FROM Map_Geography b
WHERE b.Map_Name = 'Area' AND LEFT(A.Dest_Zip,3) = b.Base) as Dest_Area
FROM Frt_Bill_Master a
WHERE a.Delivery_Depart_Ts IS NULL
--query using joins
SELECT A.Pro,
B.map_shape AS Orig_Area,
C.map_shape AS Dest_Area
FROM Frt_Bill_Master a
left outer join map_geography b
on b.map_name = 'area' and LEFT(A.Ld_Orig_Zip,3) = b.base
left outer join map_geography c
on c.map_name = 'area' and LEFT(A.Dest_Zip,3) = c.base
WHERE a.Delivery_Depart_Ts IS NULL
Thanks for taking the time to answer!
Kim
April 10, 2009 at 10:37 am
you'll want to simply Add these commands to the top:
SET STATISTICS IO ON
SET STATISTICS TIME ON
--SET SHOWPLAN_TEXT ON
then run your two queries together....actually run them a couple of times in a row to take advantage of the cache settings, and then compare the numbers.
one might be faster than the other
after that, you can uncomment out the SET SHOWPLAN_TEXT ON , and then compare how SQL decided to go get the data;
i would guess that the join will end up being faster, but i'd like to see you post back some info letting us know for sure.
Lowell
April 10, 2009 at 11:31 am
kim.talley (4/10/2009)
I have a question from a developer that I'm not able to answer....I was hoping someone here could help out.Is it more efficient to use multiple joins in a query or to use multiple selects? Below is an example. I prefer the second syntax - easier to read and less IO when I view in profiler. This is just a personel preference though - the IO is 5x more in the first example, but the CPU is a bit higher.
--query using multiple selects
SELECT A.Pro,
(SELECT b.Map_Shape FROM Map_Geography b
WHERE b.Map_Name = 'Area' AND LEFT(A.Ld_Orig_Zip,3) = b.Base) as Orig_Area,
(SELECT b.Map_Shape FROM Map_Geography b
WHERE b.Map_Name = 'Area' AND LEFT(A.Dest_Zip,3) = b.Base) as Dest_Area
FROM Frt_Bill_Master a
WHERE a.Delivery_Depart_Ts IS NULL
--query using joins
SELECT A.Pro,
B.map_shape AS Orig_Area,
C.map_shape AS Dest_Area
FROM Frt_Bill_Master a
left outer join map_geography b
on b.map_name = 'area' and LEFT(A.Ld_Orig_Zip,3) = b.base
left outer join map_geography c
on c.map_name = 'area' and LEFT(A.Dest_Zip,3) = c.base
WHERE a.Delivery_Depart_Ts IS NULL
Thanks for taking the time to answer!
Kim
Situationally either could work better. You'll need to test it as Lowell outlined. Check each execution plan to ensure they're both using indexes properly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 10, 2009 at 2:37 pm
You're using the left function in a join, which might prevent proper use of indexes. You might also want to consider an approach like this. Warning: It's typed freehand and untested.
SELECT A.Pro, B.map_shape AS Orig_Area, C.map_shape AS Dest_Area
FROM (select PRO, LEFT(Ld_Orig_Zip,3) as loZip3, LEFT(Dest_Zip,3) as dZip3
from Frt_Bill_Master
where Delivery_Depart_Ts IS NULL ) a
left outer join map_geography b
on b.map_name = 'area' and a.loZip3 = b.base
left outer join map_geography c
on c.map_name = 'area' and a.dZip3 = c.base
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 11, 2009 at 10:05 am
Quesry using joins would be much more sclable...based on the little knowledge that i have but..but again its really depends on the use.
Ex: If i have millions of records then i would use Joins... If i have small look up tables then i would use Sub query....
Please correct me 🙂
April 11, 2009 at 10:15 am
Vijaya Kadiyala (4/11/2009)
Quesry using joins would be much more sclable...based on the little knowledge that i have but..but again its really depends on the use.Ex: If i have millions of records then i would use Joins... If i have small look up tables then i would use Sub query....
Often the execution plans are exactly the same.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2009 at 10:23 am
Gail, do you have some authority for us on the joins using the LEFT function?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 11, 2009 at 10:38 am
Bob Hovious (4/11/2009)
Gail, do you have some authority for us on the joins using the LEFT function?
Authority? I have some experience with it, if that's sufficient.
Joins are the same as where clause predicates, no index seek if there's a function on the column.
Your alternative changes nothing. The join is still between a function (now aliased) and a column. In fact, it makes that join on a function much harder to notice.
CREATE TABLE T1 (
LongCode CHAR(6)
)
CREATE INDEX idx_T1 ON T1 (LongCode)
GO
CREATE TABLE T2 (
ShortCode CHAR(3)
)
CREATE INDEX idx_T2 ON T2 (ShortCode)
GO
Insert into T1 values ('123456')
Insert into T1 values ('123789')
Insert into T1 values ('123000')
Insert into T2 values ('123')
SELECT LongCode, ShortCode FROM T1 INNER JOIN T2 ON LEFT(LongCode,3) = ShortCode
SELECT LongCode, ShortCode FROM (SELECT LEFT(LongCode, 3) as TrimmedCode, LongCode FROM T1) sub INNER JOIN T2 ON TrimmedCode = ShortCode
Exec plans identical
It may be possible to use LIKE instead of = for the join in cases like this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2009 at 11:27 am
I was thinking about the LIKE solution earlier, but that would make the join be
INNER JOIN T2 ON LongCode like ShortCode+'%'
Doesn't the ShortCode+'%' expression create the same problem as the LEFT(LongCode,3) ??
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 11, 2009 at 11:37 am
Bob Hovious (4/11/2009)
I was thinking about the LIKE solution earlier, but that would make the join be
INNER JOIN T2 ON LongCode like ShortCode+'%'
Yup.
Doesn't the ShortCode+'%' expression create the same problem as the LEFT(LongCode,3) ??
Why don't you test it out and tell me? 😉
Might be an idea to increase the no of rows in the test tables if you decide to do so. Difference will be hard to see on 3 rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2009 at 11:40 am
I just tested it and answered my own question. The Join on ShortCode+'%' did NOT interfere with the proper use of the index. It produced an index seek in the execution plan. That's the way to do it.
CREATE TABLE #T1 (LongCode CHAR(5))
CREATE INDEX #idx_T1 ON #T1 (LongCode)
Insert into #T1
select right('000000'+cast(N as varchar(5)),5)
from dbo.tally
where N < 100000-- one hundred thousand
CREATE TABLE #T2 (ShortCode CHAR(3))
CREATE INDEX #idx_T2 ON #T2 (ShortCode)
INSERT into #T2
select '123'
set statistics time on;
print '--------------------Left'
SELECT LongCode, ShortCode FROM #T1 INNER JOIN #T2 ON LEFT(LongCode,3) = ShortCode
print '--------------------SubQuery'
SELECT LongCode, ShortCode FROM (SELECT LEFT(LongCode, 3) as TrimmedCode, LongCode FROM #T1) sub INNER JOIN #T2 ON TrimmedCode = ShortCode
print '--------------------like'
SELECT LongCode, ShortCode FROM #T1 INNER JOIN #T2 ON LongCode LIKE ShortCode+'%'
set statistics time off;
drop table #t1
drop table #t2
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 11, 2009 at 11:42 am
By the way, Gail. I asked for authority instead of experience because you can usually quote chapter and verse from BOL. 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 11, 2009 at 11:43 am
Bob Hovious (4/11/2009)
I just tested it and answered my own question. The Join on ShortCode+'%' did NOT interfere with the proper use of the index. It produced an index seek in the execution plan. That's the way to do it.
Yup. Expressions with only trailing wildcards are SARGable. Expressions with leading wildcards are not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2009 at 1:55 pm
The answers about sargeable predicates have already been pretty well covered on this thread, so I'll not go there.
Going back to the original question, the first code snippet uses correlated subqueries based on equality. Unless I'm missing something horrible with the Mark I Mod I eyeball, they will resolve to the same execution plan and run in the same amount of time as the second snippet.
That brings us back to what some call "best practices" which can be a real oxymoron. Most will suggest that correct JOIN syntax in accordance with the latest ANSI standards would be the best from a maintainability standpoint. I would agree with that from a readability standpoint.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2009 at 2:03 pm
Hey... who are you callin' an oxymoron!? :unsure:
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply