SubQuery or Join?

很多開發都喜歡用Subquery而不喜歡用Join,對於他們來講Subquery更容易實現。但是很多情況下用Join效能要比用Subquery好。

 

首先我們看一下Subquery: 子查詢也稱為內部查詢或內部選擇，而包含子查詢的語句也稱為外部查詢或外部選擇。

許多包含子查詢的Transact-SQL
語句都可以改用聯結表示。其他問題只能通過子查詢提出。在 Transact-SQL 中，包含子查詢的語句和語義上等效的不包含子查詢的語句在效能上通常沒有差別。但是，在一些必須檢查存在性的情況中，使用聯結會產生更好的效能。否則，為確保消除重複值，必須為外部查詢的每個結果都處理巢狀查詢。所以在這些情況下，聯結方式會產生更好的效果。

 

下面是改寫Subquery的一個例子：

 

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

 

查詢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.

 

 

改寫後：

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

 

查詢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.

 

 

可以看到改寫後的查詢不管是在CPU花費時間和IO上面都有很大的提高。 當然Subquery在某些情況下還是有優勢的，比如不相關的Subquery使用Exist/not exist或者Subquery中做加總等，另外還要考慮外圍查詢結果的資料量。

 

總之開發人員在寫程式的時候不光要考慮實現還需要兼顧效能。功能實現以後自己做測試看看是否有該井空間。