Subquery or join?

Many developers prefer subquery instead of join. For them, subquery is easier to implement. However, in many cases, join is better than subquery.

 

First, let's take a look at subquery: subqueries are also called internal queries or internal choices, while statements containing subqueries are also called external queries or external choices.

Many Transact-SQL statements containing subqueries
You can use the join statement instead. Other problems can only be raised through subqueries. In Transact-SQL, statements that contain subqueries and statements that are semantically equivalent to statements that do not contain subqueries have no performance difference. However, in some cases where you must check the existence, using a join produces better performance. Otherwise, to ensure that duplicate values are eliminated, nested queries must be processed for each result of an external query. In these cases, the connection method will produce better results.

 

The following is an example of subquery Rewriting:

 

Select C. accountnumber,

(Select
Count (*)

From sales. salesorderheader o

Where C. customerid
= O. customerid
And year (orderdate)
= 2001)
As orders_2001,

(Select
Count (*)

From sales. salesorderheader o

Where C. customerid
= O. customerid
And year (orderdate)
= 2002)
As orders_2002,

(Select
Count (*)

From sales. salesorderheader o

Where C. customerid
= O. customerid
And year (orderdate)
= 2003)
As orders_2003,

(Select
Count (*)

From sales. salesorderheader o

Where C. customerid
= O. customerid
And year (orderdate)
= 2004)
As orders_2004

From sales. Customer C

Order by 1

 

Result returned by query 1:

SQL Server parse and compile time:

CPU time = 0 MS, elapsed time = 0 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 0 ms.

SQL Server parse and compiletime:

CPU time = 65 MS, elapsed time = 65 ms.

 

(19185 row (s) affected)

Table 'worktable'. Scan count0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'customer'. Scan count1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads0, lob read-ahead reads 0.

Table 'salesorderheader'. scancount 4, logical reads2824, physical reads 0, read-ahead reads 0, lob logical reads 0, lobphysical reads 0, lob read-ahead reads 0.

 

SQL Server execution times:

CPU time = 375 MS, elapsed time = 772 Ms.

 

 

After Rewriting:

Select C. accountnumber,

Sum (Case
When year (O. orderdate)
= 2001 then 1
Else 0 end)
As orders_2001,

Sum (Case
When year (O. orderdate)
= 2002 then 1
Else 0 end)
As orders_2002,

Sum (Case
When year (O. orderdate)
= 2003 then 1
Else 0 end)
As orders_2003,

Sum (Case
When year (O. orderdate)
= 2004 then 1
Else 0 end)
As orders_2004

From sales. Customer C

Left join sales. salesorderheadero
On O. customerid = C. customerid

Group by C. accountnumber

Order by 1

 

Result returned by query 2:

SQL Server parse and compiletime:

CPU time = 0 MS, elapsed time = 0 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 0 ms.

SQL Server parse and compiletime:

CPU time = 16 MS, elapsed time = 21 Ms.

 

(19185 row (s) affected)

Table 'worktable'. Scan count0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'salesorderheader'. Scan count 1, logicalreads 706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'customer'. Scan count1, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads0, lob read-ahead reads 0.

 

SQL Server execution times:

CPU time = 249 MS, elapsed time = 547 Ms.

SQL Server parse and compiletime:

CPU time = 0 MS, elapsed time = 0 ms.

 

 

We can see that the modified query greatly improves both the CPU time and IO consumption. Of course, subquery is advantageous in some situations. For example, unrelated subquery uses exist/not exist or subquery to add a total. In addition, the data volume of the peripheral query results must be considered.

 

In short, developers should consider both implementation and performance when writing programs. After the function is implemented, perform a test to check whether the well space exists.