Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sub Query VS Left Outer Join Expand / Collapse
Author
Message
Posted Tuesday, June 8, 2010 4:25 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:06 AM
Points: 211, Visits: 1,109
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?




Thanks in advance.
Post #933882
Posted Tuesday, June 8, 2010 5:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:20 AM
Points: 1,264, Visits: 3,567
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. Get your answers faster.
Post #933906
Posted Tuesday, June 8, 2010 7:18 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:06 AM
Points: 211, Visits: 1,109
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.


Post #933981
Posted Tuesday, June 8, 2010 7:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 42,753, Visits: 35,841
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 2008, MVP
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

Post #933987
Posted Tuesday, June 8, 2010 9:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:40 AM
Points: 358, Visits: 2,775
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
Post #934061
Posted Tuesday, June 8, 2010 9:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 42,753, Visits: 35,841
Nope. All join types are possible.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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



  Post Attachments 
CorrelatedSubquery.png (388 views, 33.89 KB)
Post #934084
Posted Tuesday, June 8, 2010 10:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:40 AM
Points: 358, Visits: 2,775
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
Post #934090
Posted Tuesday, June 8, 2010 10:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 42,753, Visits: 35,841
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 2008, MVP
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

Post #934094
Posted Tuesday, June 8, 2010 10:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:40 AM
Points: 358, Visits: 2,775
Yes yes.. thanks a lot.


Thanks & Regards,
MC
Post #934098
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse