Post reply

Outer Join performance

  • 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

     

     

     

     

  • December 13, 2006 at 7:39 am

    #677766

    *= 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!

  • December 13, 2006 at 7:40 am

    #677768

    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.

  • December 13, 2006 at 8:27 am

    #677786

    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!

  • December 13, 2006 at 8:46 am

    #677800

    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

  • December 14, 2006 at 6:34 am

    #678053

    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.

  • December 14, 2006 at 8:13 am

    #678088

    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!

  • December 14, 2006 at 3:04 pm

    #678221

    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]

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

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