SQL語法提示工具SQL Prompt教程：使用EXISTS或COUNT查找相關行（下）

SQL Prompt根據(jù)數(shù)據(jù)庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設置使代碼簡單易讀--當開發(fā)者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據(jù)需要進行自定義，使之以預想的方式工作。

點擊下載SQL Prompt免費版

本文是教程使用EXISTS或COUNT查找相關行的下半部分，內(nèi)容緊接前文（點此查看），本文描述了“EXISTS或COUNT（或其他）哪個更好？”和“代碼分析規(guī)則的注意事項和誤報”。

查詢執(zhí)行統(tǒng)計數(shù)據(jù)

查看單個查詢的計時和其他執(zhí)行統(tǒng)計信息的最簡單方法是使用STATISTICS IO/ TIME，如下所示（盡管STATISTICS IO在某些情況下會帶來大量開銷，您可能更喜歡使用擴展事件）。

SET STATISTICS IO, TIME ON; --turn on io and time stats
--clear the procedure cache for the WideWorldImporters DB
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
-- Query with EXISTS
-- Query with COUNT(*)
-- Query with DISTINCT

將每個查詢執(zhí)行幾次，以獲取僅與執(zhí)行計劃有關的統(tǒng)計信息（而不是編譯計劃或將數(shù)據(jù)緩存到內(nèi)存）。我還建議單獨運行每個語句，否則時間統(tǒng)計信息可能會增加或丟失查詢執(zhí)行過程中的一些開銷。

我不會在這里列出統(tǒng)計數(shù)據(jù)，但是我沒有看到任何查詢變化在運行時間上的顯著差異，對于COUNT和EXISTS查詢，包括邏輯讀?。▉碜詢?nèi)存）和物理讀?。▉碜源疟P）內(nèi)部的執(zhí)行統(tǒng)計數(shù)據(jù)是相同的。

在更有趣的情況下，您可能會發(fā)現(xiàn)可讀性較差的解決方案使用更少的CPU，更少的I / O（內(nèi)存和磁盤）并且花費更少的時間。那么問題就變成了：是否花了更少的時間來實施一個更神秘的方式來回答這個問題呢？這個決定并不總是那么簡單。如果查詢每天運行一百萬次，那么節(jié)省幾毫秒是值得的。如果它每天運行一次，那么節(jié)省10秒幾乎是絕對不值得的，尤其是如果它意味著沒有人可以理解代碼的工作原理時。

在這種情況下，值得簡要考慮一下為什么COUNT和EXISTS之間沒有性能差異。 這似乎令人驚訝，因為從邏輯上講，很容易解釋為什么EXISTS解決方案可能會更快，因為它一找到第一個客戶就停止尋找客戶的匹配訂單。 如前所述，COUNT解決方案以及其他書面解決方案會為購買的每個人處理所有訂單行，然后拒絕那些不符合“大于沒有訂單”標準的購買500件以上的產(chǎn)品。

執(zhí)行計劃揭示了答案。

執(zhí)行計劃

請注意，WideWorldImporters其中包括我暫時禁用的FilterCustomersBySalesTerritoryRole 安全策略，以免使計劃復雜化。只有在開發(fā)中才能做到這一點！

ALTER SECURITY POLICY Application.FilterCustomersBySalesTerritoryRole WITH (STATE = OFF);

在我的測試中，COUNT和EXISTS查詢的執(zhí)行計劃相同，如圖2所示。

圖2

在幾乎每個查詢示例中，您都可能會發(fā)現(xiàn)EXISTS和COUNT查詢具有相同的計劃。盡管從邏輯上講前者效率更高，但實際上查詢優(yōu)化器通?？梢詫⒉樵冎貙憺樾阅芨玫臄?shù)學等效版本，而且實際上，只要可能，它都會以相同的方式對待這兩個變體，因此計劃和性能是相同的。

然而，隨著查詢復雜度的增加，優(yōu)化器可能無法始終發(fā)揮其魔力，因此您可能仍會看到某些情況下，該COUNT變體的確變慢了，并且可讀性較差。就是說，我在最大為數(shù)百GB的表上測試了這些查詢的更復雜版本（盡管仍然具有相同的謂詞條件），但仍然沒有發(fā)現(xiàn)任何差異。

但是，如果將謂詞條件更改為“大于或等于零”，我確實發(fā)現(xiàn)了微小的差異。例如，對于COUNT(*)查詢：

........AND  OrderLines.UnitPrice > 500) >= 0;

從數(shù)學上講，此查詢必須返回數(shù)據(jù)。但是，該COUNT查詢計劃包括一些額外的運算符。一個哈希匹配（聚合）操作，來計算COUNT(*)值，計算標量，并且一個過濾器以過濾掉的行，其中COUNT(*) = 0。總體而言，它們只占該查詢工作量的不到2％。

圖3

最后，我不會在這里顯示它，但是DISTINCT查詢計劃表明它是成本較高的實現(xiàn)，占預期成本的34％，其他兩個成本分別為33％。DISTINCT查詢版本中的額外費用主要是需要進行排序以實現(xiàn)不同的運算符才能刪除重復的值。

先前的查詢使用Customers和之間Orders的半聯(lián)接（半聯(lián)接實現(xiàn)為相關子查詢，本質(zhì)上是聯(lián)接到表，但Orders在這種情況下不從一個輸入（表）返回任何行），在這里我們得到一個JOIN將在處理過程中將數(shù)據(jù)添加到集合中的數(shù)據(jù)，導致使用的內(nèi)存稍大。所產(chǎn)生的性能差異仍然可以忽略不計，但是這種方法可能無法很好地擴展到大數(shù)據(jù)量。

點擊下載SQL Prompt免費版

代碼分析規(guī)則的注意事項和誤報

代碼分析中有趣的事情之一是，如果我按如下方式編寫查詢，使用一個變量作為COUNT過濾器，結果將是正確的，我沒有看到PE013警告，但我確實得到了較差的計劃（在圖3中）。

DECLARE @countvalue int = 0;
SELECT People.FullName, People.EmailAddress
FROM   Sales.Customers
        JOIN Application.People
         ON People.PersonID = Customers.PrimaryContactPersonID
WHERE  People.EmailAddress IS NOT NULL 
  AND  (SELECT COUNT(1)
        FROM   Sales.Orders
                JOIN Sales.OrderLines
                     ON OrderLines.OrderID = Orders.OrderID
        WHERE  Orders.CustomerID = Customers.CustomerID
          AND  OrderLines.UnitPrice >= 500) > @countvalue;

顯然，如果的值@countvalue始終設置為文字值，則這不是編寫查詢的理想方法，因為它向優(yōu)化器引入了看起來像變量值的變量，從而使該優(yōu)化器選擇了允許@countvalue（尤其是如果您只是為了避免代碼分析警告而使您的團隊代碼審查變得更容易的話）。

如果@countvalue的值是一個允許查詢執(zhí)行的操作而不是簡單的操作或存在一行，那么此技術是提供答案的最佳方法，例如“給我所有客戶的郵件已經(jīng)命令2個或更多的項目單價大于500”通過設置@countvalue變量2。然后要求5或更多是一個簡單的參數(shù)改變。

如果您想知道是否使用COUNT（1）而不是COUNT（*）會對性能產(chǎn)生任何影響：標量表達式的含義沒有什么不同，除非它在表達式中包含可能導致其評估為NULL的列。沒有列引用的標量值不會被評估，即使它像1/0一樣是無用的：

SELECT COUNT(1/0) FROM Sales.Orders;

這將返回73595，而不是您期望的除以零。任何標量表達式都會被忽略并計數(shù)。

最后，請注意，靜態(tài)代碼分析將規(guī)則基于幅度布爾表達式中的0。使用以下任何命令結束COUNT（*）查詢都將導致相同的警報，即使它們具有非常不同的含義，因此也是該規(guī)則的誤報，盡管以下示例均不是解決問題的理想方法。

--First two are equivalent to NOT EXISTS
  AND  OrderLines.UnitPrice >= 500) = 0;
  AND  OrderLines.UnitPrice >= 500) <= 0;
--Nonsense, COUNT cannot be < 0
  AND  OrderLines.UnitPrice >= 500) < 0;
--Equivalent to EXISTS
  AND  OrderLines.UnitPrice >= 500) > 0;
--Will always be true
  AND  OrderLines.UnitPrice >= 500) >= 0;

結論

為了根據(jù)至少必須存在一行的條件查找相關行，很明顯，使用EXISTS是最合適的解決方案。它可讀性強，可以直接，簡單地回答問題，并且在任何替代解決方案中的執(zhí)行效果至少相同。盡管我無法檢測到使用EXISTS而不是COUNT所帶來的性能優(yōu)勢，但是可讀性足以確保認真對待代碼分析規(guī)則PE013（為了您自己和其他程序員）。

本教程內(nèi)容到這里就結束啦，您可以點擊下方相關鏈接查看該教程上半部分的內(nèi)容哦~您也可以下載SQL Prompt試用版評估一下~

相關內(nèi)容推薦：

SQL語法提示工具SQL Prompt教程：使用EXISTS或COUNT查找相關行（上）

SQL Prompt使用教程>>>

想要購買SQL Prompt正版授權，或了解更多產(chǎn)品信息請點擊“咨詢在線客服”