Post reply

Sub Query VS Left Outer Join

  • June 8, 2010 at 4:25 am

    #221879

    [font="Verdana"]Hi,

    I have tangled in the performace comparison between Sub Query and Left outer join.

    As you may know, we can easily substitue sub query from the SELECT clause to FROM clause with Left outer join.

    I reduced a Query intense(d) with sub quries and its execution plan was way out from the reach of humans to understand.

    So i just replaced sub queries into left outer join... Consequenlty, Execution plan and Physical Layout of the query was smoothed but........ Performace degraded between range of 1 second.

    So my concerns are

    Do Sub Query is efficient? Always or Depends?

    Left outer join will help gain performance, when there are large out put data sets?

    The Degree of Execution plans Nodes do not affect performance at all?

    :doze:

    Thanks in advance.[/font]

  • June 8, 2010 at 5:29 am

    #1177391

    Ensuring your tables are properly indexed and using inner joins where possible would help performance.

    I'm pretty sure it's very dependant on the structure of the tables and the format of the query also. Maybe more information would be helpful. Can you post the query?

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • June 8, 2010 at 7:18 am

    #1177455

    [font="Verdana"]

    calvo (6/8/2010)

    Ensuring your tables are properly indexed and using inner joins where possible would help performance.

    Yes, i appreciate it, but please let exclude proper indexing (including Clustered) or assume that indexes are fully compliant with both approaches, no problem/over head w.r.t indexes.

    And sub-query by default Returns a NULL same as that of Left Outer Join scenario where matching rows do not exist.

    I'm pretty sure it's very dependant on the structure of the tables and the format of the query also. Maybe more information would be helpful. Can you post the query?

    I wish to, but it is not possible to put even code segment as i stated earlier that the query is so complicated/confusing that it would not make any sense to your people time.

    Please, let try to be focus on generic questions stated in start of thread, so we can all benefit from it.

    Thank you.

    [/font]

  • June 8, 2010 at 7:28 am

    #1177460

    Abrar Ahmad_ (6/8/2010)

    Please, let try to be focus on generic questions stated in start of thread, so we can all benefit from it.

    The answer to your generic question is 'it depends'. You cannot say, 100% for certain in all cases that subqueries (and I assume you mean correlated subqueries in the select clause) are slower or faster than joins. It depends on what you're doing, data volumes, indexes, selected execution plans and likely a whole bunch of other things.

    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

  • June 8, 2010 at 9:08 am

    #1177521

    Along with the reply other friends provided I would like to add one more.

    As they mentioned it depends, we cant say one is better than other, but when you go for a sub query, you are limiting the query optimizer to use only Nested Loop join, but if you go with Join , the optimizer has options among Nested Loop, Merge and Hash joins so that it can choose one for the best plan

    Friends I'm I correct ?

    Thanks & Regards,
    MC

  • June 8, 2010 at 9:55 am

    #1177541

    Nope. All join types are possible.

    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

  • June 8, 2010 at 10:06 am

    #1177545

    Thanks a lot Gail,

    I remember I red it in some books the point I have mentioned any way thanks for the clarification. Are you sure that all joins are possible if the Sub query comes in WHERE clause or in FROM clause as well..?

    Thanks & Regards,
    MC

  • June 8, 2010 at 10:11 am

    #1177549

    only4mithunc (6/8/2010)

    Are you sure that all joins are possible if the Sub query comes in WHERE clause or in FROM clause as well..?

    Yes. Why don't you test it out and see for yourself? Not hard to do.

    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

  • June 8, 2010 at 10:15 am

    #1177553

    Yes yes.. 🙂 thanks a lot.

    Thanks & Regards,
    MC

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

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