May 26, 2013 at 6:33 am
Hi Friends,
I'm new to SQL.
And I wand to know Which is faster, Sub Query or Join? and Why ?
Thanks in Advance.
May 26, 2013 at 7:39 am
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
May 26, 2013 at 8:23 pm
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.
May 27, 2013 at 8:21 am
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!
May 27, 2013 at 8:38 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply