Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Which is faster, Sub Query or Join? and Why ?


Which is faster, Sub Query or Join? and Why ?

Author
Message
vignesh.ms
vignesh.ms
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 516
Hi Friends,

I'm new to SQL.

And I wand to know Which is faster, Sub Query or Join? and Why ?

Thanks in Advance.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47415 Visits: 44402
No way to answer that question in general. Depends on what exactly you're doing.

If you are talking about a subquery in the from clause (derived table) and a join, then assuming the queries are logically equivalent the performance will likely be identical.
If you're talking about some other subquery, then it depends on exactly what you're doing.


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


Golfer22
Golfer22
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 96
A subquery could run in O(k) time which is very fast. In fact, it could be merely a mathematical operation (e.g., 1+1). But it could run in O(n!) which is very slow. A subquery could be a join.

Joins can be one of three types: nested loops, merge joins and hash joins. Nested loops are appropriate for small joins. Merge joins will be chosen by the database optimizer when the tables are almost in the correct order for the join. Merge join is faster than hash joins and takes less space in tempdb. Hash joins are chosen when the tables are essentially randomized compared to their intended result. Hash joins tend to be the slowest and take up the most space in tempdb. The underlying type of join is vieweable in the execution plan. A join operation is a supported operation of SQL Server. The operation itself, I don't think, could include a subquery. A transaction could encompass a join and a subquery.
patrickmcginnis59 10839
patrickmcginnis59 10839
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 5111
Golfer22 (5/26/2013)
A subquery could run in O(k) time which is very fast. In fact, it could be merely a mathematical operation (e.g., 1+1). But it could run in O(n!) which is very slow. A subquery could be a join.

Joins can be one of three types: nested loops, merge joins and hash joins. Nested loops are appropriate for small joins. Merge joins will be chosen by the database optimizer when the tables are almost in the correct order for the join.

Is there a statistic to indicate the degree or percentage of rows that are sorted in the table? Ie., the "sorted-ness" of the table? I was under the impression that the table was either sorted (ordered by a clustered key) or not, so this is a new one for me!

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47415 Visits: 44402
patrickmcginnis59 10839 (5/27/2013)
I was under the impression that the table was either sorted (ordered by a clustered key) or not, so this is a new one for me!


Technically tables are unsorted sets regardless of indexes, however that's besides the point here.

When the optimiser comes up with a plan, it will know if an intermediate resultset is ordered and if so by what, however that's an all or nothing, it's either sorted or it's not sorted. Sorted resultsets can be from doing an ordered (range) scan of an index (clustered or nonclustered) or from a sort operation, other operators in the query are flagged as order preserving (things like nested loop joins are) or non-order preserving (hash joins). A sorted resultset allows for merge joins or stream aggregate operators to be used without needing a sort operation before them and possibly allows for a sorted output (per the order by) without needing a sort operator.


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


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