Outer Join performance

Question

Outer Join performance

Michael Carey-400169

Mr or Mrs. 500

Points: 512
More actions
December 13, 2006 at 7:12 am

#171226

Which is faster?
Using the words: Left Outer Join
or using: *=
to outer join tables?
I have heard that the *= is better, fewer characters to process.
Thanks, Mike

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

*= got deprecated in sql2005, so i'd suggest switching over to the standard format; it's more readable, and is also ANSI compliant, so you can use the same SQl statement in other database systems, where *= must be changed to comply with oracle, mysql, or PL/SQL. it's just a good practice i guess.

as far as number of characters, it will not make a difference...that's like saying declaring a variable with a 40 character name is less efficient thant a one character variable. in theory it might be true, but you can't really prove it. the commands are interprested into an execution plan, and the conversion from TSQL to execution plan is not measurable, only the time for the execution plan is.

the commands you enter are changed into machine code that uses pointers, so after it gets interpreted as an executaiton plan, there is no additional impact. the executation plans

does LEFT JOIN take more resources than LEFT OUTER JOIN (it's the same command); does INT take more resources than INTEGER?(same data type); I'd insist that the answer is no.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dlhatheway@mmm.com Old Hand Points: 382 More actions · Answer 2

I don't know which is faster.

However, I require the developers to use ANSI JOIN syntax because it's far easier to understand. Per our standard, the JOIN clause is the part of the SQL statement that describes how tables are related and the WHERE clause is the part of the SQL statement where we describe which rows to operate on.

Lowell SSC Guru Points: 323518 More actions · Answer 3

just a follow up; i got the estimated execution plans for a join against two of my big tables using the commands below, and they had both identical estimated execution plans and actual execution plans;

select * from gmproj,gmact where gmproj.prjtblkey*=gmact.prjtblkey

select * from gmproj

LEFT OUTER JOIN gmact on gmproj.prjtblkey = gmact.prjtblkey

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Carey-400169 Mr or Mrs. 500 Points: 512 More actions · Answer 4

Thank you, that's what i needed to see, a test. I appreciate the effort.

So in this instance, in practice, theory and practice were the same, meaning that the theory: "In Theory, theory and practice are the same...In practice, they are not" is proven untrue - in practice.

Thanks, Mike

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 5

As a developer, never mind any marginal gain/loss in performance - I think of the blood, sweat and tears of trying to debug it in future. As a general rule, never abbreviate anything! - it just ain't worth it in the long run if you consider total cost of ownership.

Lowell SSC Guru Points: 323518 More actions · Answer 6

very true; don't abreviate anything; readability is critical.

I have developers here who never heard of the *= join operator; ....someone might look at that, think it's a syntax error, then change it to an equals sign because it looks wrong, without knowing the impact of changing it from an outer join to an inner join.

It might be a while before anyone noticed discrepancies in the expected data.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Robert Davis One Orange Chip Points: 28027 More actions · Answer 7

I would definitely be worried if my server's performance was so poor that shortening a query by 13 characters made it run faster.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]