Post reply

What is the difference between SubQuery and Joins in terms of Performance

  • April 5, 2012 at 9:36 pm

    #266883

    Hi All,

    What is the difference between SubQuery and Joins in terms of Performance,

    I know that joins are good rather than sub queries, but how it make the dissference while executing internally ?

    Thanks in advance

    Ravi@sql

  • April 5, 2012 at 10:18 pm

    #1469953

    in general. sub selects have to be executed first or for each row depending on the case and then joined to the outer query. a join is executed once as the outer query. but it is not a blanket statement that joins are better than sub queries. there are cases where a sub query will be faster (not many but they are out there). as always test your solutions before rolling them out to production.

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • April 6, 2012 at 3:06 am

    #1469986

    Usually none.

    The idea that subqueries execute once per row of the outer query is wrong in most cases (there are only two cases where that is true). The vast majority of subqueries are treated by the optimiser just like a join)

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 6, 2012 at 3:18 am

    #1469994

    Thanks Gail Shaw.

    Can you share me some example or any links which describes what you have said.

    Its one of best practice in my comapny to not use sub queries any where. Even while tuning

    i have removed and replaced with joins which increases the query performance.

    But your statements are contradictory so can you explain around it ?

  • April 6, 2012 at 3:35 am

    #1470001

    That 'best practice' is nothing of the sort. Subqueries are very useful and usually very well performing.

    The type that you should avoid is the type that have (SELECT TOP (1) ... ORDER BY) and are in the select clause. Those are just about the only ones that really do execute once per row of the outer query.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

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