SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sub Query VS Left Outer Join


Sub Query VS Left Outer Join

Author
Message
Abrar Ahmad_
Abrar Ahmad_
SSC Eights!
SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)

Group: General Forum Members
Points: 900 Visits: 1305
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.

calvo
calvo
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3312 Visits: 4018
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.
Abrar Ahmad_
Abrar Ahmad_
SSC Eights!
SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)

Group: General Forum Members
Points: 900 Visits: 1305
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.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225619 Visits: 46321
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


only4mithunc
only4mithunc
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1750 Visits: 2803
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225619 Visits: 46321
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


Attachments
CorrelatedSubquery.png (586 views, 33.00 KB)
only4mithunc
only4mithunc
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1750 Visits: 2803
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225619 Visits: 46321
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


only4mithunc
only4mithunc
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1750 Visits: 2803
Yes yes.. :-) thanks a lot.

Thanks & Regards,
MC
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search