Sub Query VS Left Outer Join

Question

Sub Query VS Left Outer Join

Abrar Ahmad_

SSCarpal Tunnel

Points: 4239
More actions
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]

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

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

calvo SSChampion Points: 12930 More actions · Answer 1

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.

Abrar Ahmad_ SSCarpal Tunnel Points: 4239 More actions · Answer 2

[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]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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 SSCrazy Eights Points: 8434 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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 SSCrazy Eights Points: 8434 More actions · Answer 8

only4mithunc

SSCrazy Eights

Points: 8434

June 8, 2010 at 10:15 am

#1177553

Yes yes.. 🙂 thanks a lot.

Thanks & Regards,
MC