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

Which is faster, Sub Query or Join? and Why ? Expand / Collapse
Author
Message
Posted Sunday, May 26, 2013 6:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 3:33 AM
Points: 165, Visits: 447
Hi Friends,

I'm new to SQL.

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

Thanks in Advance.
Post #1456859
Posted Sunday, May 26, 2013 7:39 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 @ 2:57 AM
Points: 40,618, Visits: 37,085
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 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 #1456864
Posted Sunday, May 26, 2013 8:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, December 28, 2013 5:45 PM
Points: 94, 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.
Post #1456947
Posted Monday, May 27, 2013 8:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:13 PM
Points: 393, Visits: 2,684
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!
Post #1457112
Posted Monday, May 27, 2013 8:38 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 @ 2:57 AM
Points: 40,618, Visits: 37,085
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 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 #1457115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse