五種提高 SQL 性能的方法

有時， 為了讓應用程序運行得更快，所做的全部工作就是在這里或那里做一些很小調整。啊，但關鍵在于確定如何進行調整！遲早您會遇到這種情況：應用程序中的 SQL 查詢不能按照您想要的方式進行響應。它要么不返回數據，要么耗費的時間長得出奇。如果它降低了報告或您的企業應用程序的速度，用戶必須等待的時間過長，他們就會很不滿意。就像您的父母不想聽您解釋為什么在深更半夜才回來一樣，用戶也不會聽你解釋為什么查詢耗費這么長時間。（“對不起，媽媽，我使用了太多的 LEFT JOIN?！保┯脩粝Ｍ麘贸绦蝽憫杆?，他們的報告能夠在瞬間之內返回分析數據。就我自己而言，如果在 Web 上沖浪時某個頁面要耗費十多秒才能加載（好吧，五秒更實際一些），我也會很不耐煩。

為了解決這些問題，重要的是找到問題的根源。那么，從哪里開始呢？根本原因通常在于數據庫設計和訪問它的查詢。在本月的專欄中，我將講述四項技術，這些技術可用于提高基于 SQL Server? 的應用程序的性能或改善其可伸縮性。我將仔細說明 LEFT JOIN、CROSS JOIN 的使用以及 IDENTITY 值的檢索。請記住，根本沒有神奇的解決方案。調整您的數據庫及其查詢需要占用時間、進行分析，還需要大量的測試。這些技術都已被證明行之有效，但對您的應用程序而言，可能其中一些技術比另一些技術更適用。

本頁內容 從 INSERT 返回 IDENTITY 內嵌視圖與臨時表 避免 LEFT JOIN 和 NULL 靈活使用笛卡爾乘積 拾遺補零 從 INSERT 返回 IDENTITY

我決定從遇到許多問題的內容入手：如何在執行 SQL INSERT 后檢索 IDENTITY 值。通常，問題不在于如何編寫檢索值的查詢，而在于在哪里以及何時進行檢索。在 SQL Server 中，下面的語句可用于檢索由最新在活動數據庫連接上運行的 SQL 語句所創建的 IDENTITY 值：

SELECT @@IDENTITY

這個 SQL 語句并不復雜，但需要記住的一點是：如果這個最新的 SQL 語句不是 INSERT，或者您針對非 INSERT SQL 的其他連接運行了此 SQL，則不會獲得期望的值。您必須運行下列代碼才能檢索緊跟在 INSERT SQL 之后且位于同一連接上的 IDENTITY，如下所示：

INSERT INTO Products (ProductName) VALUES ('Chalk') SELECT @@IDENTITY

在一個連接上針對 Northwind 數據庫運行這些查詢將返回一個名稱為 Chalk 的新產品的 IDENTITY 值。所以，在使用 ADO 的 Visual Basic? 應用程序中，可以運行以下語句：

Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _ (ProductName) VALUES ('Chalk');SELECT @@IDENTITY") lProductID = oRs(0)

此代碼告訴 SQL Server 不要返回查詢的行計數，然后執行 INSERT 語句，并返回剛剛為這個新行創建的 IDENTITY 值。SET NOCOUNT ON 語句表示返回的記錄集有一行和一列，其中包含了這個新的 IDENTITY 值。如果沒有此語句，則會首先返回一個空的記錄集（因為 INSERT 語句不返回任何數據），然后會返回第二個記錄集，第二個記錄集中包含 IDENTITY 值。這可能有些令人困惑，尤其是因為您從來就沒有希望過 INSERT 會返回記錄集。之所以會發生此情況，是因為 SQL Server 看到了這個行計數（即一行受到影響）并將其解釋為表示一個記錄集。因此，真正的數據被推回到了第二個記錄集。當然您可以使用 ADO 中的 NextRecordset 方法獲取此第二個記錄集，但如果總能夠首先返回該記錄集且只返回該記錄集，則會更方便，也更有效率。

此方法雖然有效，但需要在 SQL 語句中額外添加一些代碼。獲得相同結果的另一方法是在 INSERT 之前使用 SET NOCOUNT ON 語句，并將 SELECT @@IDENTITY 語句放在表中的 FOR INSERT 觸發器中，如下面的代碼片段所示。這樣，任何進入該表的 INSERT 語句都將自動返回 IDENTITY 值。

CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS SELECT @@IDENTITY GO

觸發器只在 Products 表上發生 INSERT 時啟動，所以它總是會在成功 INSERT 之后返回一個 IDENTITY。使用此技術，您可以始終以相同的方式在應用程序中檢索 IDENTITY 值。

返回頁首內嵌視圖與臨時表

某些時候，查詢需要將數據與其他一些可能只能通過執行 GROUP BY 然后執行標準查詢才能收集的數據進行聯接。例如，如果要查詢最新五個定單的有關信息，您首先需要知道是哪些定單。這可以使用返回定單 ID 的 SQL 查詢來檢索。此數據就會存儲在臨時表（這是一個常用技術）中，然后與 Products 表進行聯接，以返回這些定單售出的產品數量：

CREATE TABLE #Temp1 (OrderID INT NOT NULL, _ OrderDate DATETIME NOT NULL) INSERT INTO #Temp1 (OrderID, OrderDate) SELECT TOP 5 o.OrderID, o.OrderDate FROM Orders o ORDER BY o.OrderDate DESC SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity FROM #Temp1 t INNER JOIN [Order Details] od ON t.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GROUP BY p.ProductName ORDER BY p.ProductName DROP TABLE #Temp1

這些 SQL 語句會創建一個臨時表，將數據插入該表中，將其他數據與該表進行聯接，然后除去該臨時表。這會導致此查詢進行大量 I/O 操作，因此，可以重新編寫查詢，使用內嵌視圖取代臨時表。內嵌視圖只是一個可以聯接到 FROM 子句中的查詢。所以，您不用在 tempdb 中的臨時表上耗費大量 I/O 和磁盤訪問，而可以使用內嵌視圖得到同樣的結果：

SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity FROM ( SELECT TOP 5 o.OrderID, o.OrderDate FROM Orders o ORDER BY o.OrderDate DESC ) t INNER JOIN [Order Details] od ON t.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GROUP BY p.ProductName ORDER BY p.ProductName

此查詢不僅比前面的查詢效率更高，而且長度更短。臨時表會消耗大量資源。如果只需要將數據聯接到其他查詢，則可以試試使用內嵌視圖，以節省資源。

原文轉自：http://www.anti-gravitydesign.com