四項技術提高SQL Server的性能

　　本文講解如何使用LEFT JOIN、CROSS JOIN以及IDENTITY值的檢索，這些技術來提高基于SQL Server的應用程式的性能或改善其可伸縮性。

　　有時，為了讓應用程式運作得更快，所做的全部工作就是在這裡或那裡做一些很小調整。但關鍵在于确定如何進行調整！遲早您會遇到這種情況：應用程式中的SQL 查詢不能按照您想要的方式進行響應。它要麼不傳回資料，要麼耗費的時間長得出奇。如果它降低了企業應用程式的速度，使用者必須等待很長時間。使用者希望應用程式響應迅速，他們的報告能夠在瞬間之内傳回分析資料。就我自己而言，如果在Web上沖浪時某個頁面要耗費十多秒才能加載，我也會很不耐煩。

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

　　從 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')

　　在一個連接配接上針對 Northwind 資料庫運作這些查詢将傳回一個名稱為 Chalk 的新産品的 IDENTITY 值。是以，在使用ADOVisual 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

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 (

FROM Orders o

ORDER BY o.OrderDate DESC

) t

GROUP BY

p.ProductName

ORDER BY

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

　　避免 LEFT JOIN 和 NULL

　　當然，有很多時候您需要執行 LEFT JOIN 和使用 NULL 值。但是，它們并不适用于所有情況。改變 SQL 查詢的建構方式可能會産生将一個花幾分鐘運作的報告縮短到隻花幾秒鐘這樣的天壤之别的效果。有時，必須在查詢中調整資料的形态，使之适應應用程式所要求的顯示方式。雖然 TABLE 資料類型會減少大量占用資源的情況，但在查詢中還有許多區域可以進行優化。SQL 的一個有價值的常用功能是 LEFT JOIN。它可以用于檢索第一個表中的所有行、第二個表中所有比對的行、以及第二個表中與第一個表不比對的所有行。例如，如果希望傳回每個客戶及其定單，使用 LEFT JOIN 則可以顯示有定單和沒有定單的客戶。

　　此工具可能會被過度使用。LEFT JOIN 消耗的資源非常之多，因為它們包含與 NULL（不存在）資料比對的資料。在某些情況下，這是不可避免的，但是代價可能非常高。LEFT JOIN 比 INNER JOIN 消耗資源更多，是以如果您可以重新編寫查詢以使得該查詢不使用任何 LEFT JOIN，則會得到非常可觀的回報。

　　加快使用 LEFT JOIN 的查詢速度的一項技術涉及建立一個 TABLE 資料類型，插入第一個表（LEFT JOIN 左側的表）中的所有行，然後使用第二個表中的值更新 TABLE 資料類型。此技術是一個兩步的過程，但與标準的 LEFT JOIN 相比，可以節省大量時間。一個很好的規則是嘗試各種不同的技術并記錄每種技術所需的時間，直到獲得用于您的應用程式的執行性能最佳的查詢。

　　測試查詢的速度時，有必要多次運作此查詢，然後取一個平均值。因為查詢（或存儲過程）可能會存儲在 SQL Server 記憶體中的過程緩存中，是以第一次嘗試耗費的時間好像稍長一些，而所有後續嘗試耗費的時間都較短。另外，運作您的查詢時，可能正在針對相同的表運作其他查詢。當其他查詢鎖定和解鎖這些表時，可能會導緻您的查詢要排隊等待。例如，如果您進行查詢時某人正在更新 此表中的資料，則在更新送出時您的查詢可能需要耗費更長時間來執行。

　　避免使用 LEFT JOIN 時速度降低的最簡單方法是盡可能多地圍繞它們設計資料庫。例如，假設某一産品可能具有類别也可能沒有類别。如果 Products 表存儲了其類别的 ID，而沒有用于某個特定産品的類别，則您可以在字段中存儲 NULL 值。然後您必須執行 LEFT JOIN 來擷取所有産品及其類别。您可以建立一個值為“No Category”的類别，進而指定外鍵關系不允許 NULL 值。通過執行上述操作，現在您就可以使用 INNER JOIN 檢索所有産品及其類别了。雖然這看起來好像是一個帶有多餘資料的變通方法，但可能是一個很有價值的技術，因為它可以消除 SQL 批處理語句中消耗資源較多的 LEFT JOIN。在資料庫中全部使用此概念可以為您節省大量的處理時間。請記住，對于您的使用者而言，即使幾秒鐘的時間也非常重要，因為當您有許多使用者正在通路同一個聯機資料庫應用程式時，這幾秒鐘實際上的意義會非常重大。

　　靈活使用笛卡爾乘積

　　對于此技巧，我将進行非常詳細的介紹，并提倡在某些情況下使用笛卡爾乘積。出于某些原因，笛卡爾乘積 (CROSS JOIN) 遭到了很多譴責，開發人員通常會被警告根本就不要使用它們。在許多情況下，它們消耗的資源太多，進而無法高效使用。但是像 SQL 中的任何工具一樣，如果正确使用，它們也會很有價值。例如，如果您想運作一個傳回每月資料（即使某一特定月份客戶沒有定單也要傳回）的查詢，您就可以很友善地使用笛卡爾乘積。

　　雖然這看起來好像沒什麼神奇的，但是請考慮一下，如果您從客戶到定單（這些定單按月份進行分組并對銷售額進行小計）進行了标準的 INNER JOIN，則隻會獲得客戶有定單的月份。是以，對于客戶未訂購任何産品的月份，您不會獲得 0 值。如果您想為每個客戶都繪制一個圖，以顯示每個月和該月銷售額，則可能希望此圖包括月銷售額為 0 的月份，以便直覺辨別出這些月份。如果使用 Figure 2（最後一頁） 中的 SQL，資料則會跳過銷售額為 0 美元的月份，因為在定單表中對于零銷售額不會包含任何行（假設您隻存儲發生的事件）。

　　Figure 3（最後一頁）中的代碼雖然較長，但是可以達到擷取所有銷售資料（甚至包括沒有銷售額的月份）的目标。首先，它會提取去年所有月份的清單，然後将它們放入第一個 TABLE 資料類型表 (@tblMonths) 中。下一步，此代碼會擷取在該時間段内有銷售額的所有客戶公司的名稱清單，然後将它們放入另一個 TABLE 資料類型表 (@tblCus-tomers) 中。這兩個表存儲了建立結果集所必需的所有基本資料，但實際銷售數量除外。 第一個表中列出了所有月份（12 行），第二個表中列出了這個時間段内有銷售額的所有客戶（對于我是 81 個）。并非每個客戶在過去 12 個月中的每個月都購買了産品，是以，執行 INNER JOIN 或 LEFT JOIN 不會傳回每個月的每個客戶。這些操作隻會傳回購買産品的客戶和月份。

　　笛卡爾乘積則可以傳回所有月份的所有客戶。笛卡爾乘積基本上是将第一個表與第二個表相乘，生成一個行集合，其中包含第一個表中的行數與第二個表中的行數相乘的結果。是以，笛卡爾乘積會向表@tblFinal 傳回 972 行。最後的步驟是使用此日期範圍内每個客戶的月銷售額總計更新 @tblFinal 表，以及選擇最終的行集。

　　如果由于笛卡爾乘積占用的資源可能會很多，而不需要真正的笛卡爾乘積，則可以謹慎地使用 CROSS JOIN。例如，如果對産品和類别執行了 CROSS JOIN，然後使用 WHERE 子句、DISTINCT 或 GROUP BY 來篩選出大多數行，那麼使用 INNER JOIN 會獲得同樣的結果，而且效率高得多。如果需要為所有的可能性都傳回資料（例如在您希望使用每月銷售日期填充一個圖表時），則笛卡爾乘積可能會非常有幫助。但是，您不應該将它們用于其他用途，因為在大多數方案中 INNER JOIN 的效率要高得多。

　　拾遺補零

　　這裡介紹其他一些可幫助提高 SQL 查詢效率的常用技術。假設您将按區域對所有銷售人員進行分組并将他們的銷售額進行小計，但是您隻想要那些資料庫中标記為處于活動狀态的銷售人員。您可以按區域對銷售人員分組，并使用 HAVING 子句消除那些未處于活動狀态的銷售人員，也可以在 WHERE 子句中執行此操作。在 WHERE 子句中執行此操作會減少需要分組的行數，是以比在 HAVING 子句中執行此操作效率更高。HAVING 子句中基于行的條件的篩選會強制查詢對那些在 WHERE 子句中會被去除的資料進行分組。

　　另一個提高效率的技巧是使用 DISTINCT 關鍵字查找資料行的單獨報表，來代替使用 GROUP BY 子句。在這種情況下，使用 DISTINCT 關鍵字的 SQL 效率更高。請在需要計算聚合函數（SUM、COUNT、MAX 等）的情況下再使用 GROUP BY。另外，如果您的查詢總是自己傳回一個唯一的行，則不要使用 DISTINCT 關鍵字。在這種情況下，DISTINCT 關鍵字隻會增加系統開銷。

　　您已經看到了，有大量技術都可用于優化查詢和實作特定的業務規則，技巧就是進行一些嘗試，然後比較它們的性能。最重要的是要測試、測試、再測試。

Figure 2 Returning All Customers and Their Sales

set nocount on

DECLARE @dtStartDate DATETIME,

@dtEndDate DATETIME,

@dtDate DATETIME

SET @dtEndDate = '5/5/1997'

SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1) AS VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + '

23:59:59' AS DATETIME))

SET @dtStartDate = DATEADD(MM, -1 * 12, @dtEndDate)

SELECT CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +

CASE

WHEN MONTH(o.OrderDate) < 10

THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))

ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))

END AS sMonth,

c.CustomerID,

c.CompanyName,

c.ContactName,

SUM(od.Quantity * od.UnitPrice) AS mSales

FROM Customers c

INNER JOIN Orders o ON c.CustomerID = o.CustomerID

INNER JOIN [Order Details] od ON o.OrderID = od.OrderID

WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate

CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +

END,

c.ContactName

sMonth

Figure 3 Cartesian Product at Work

DECLARE @tblMonths TABLE (sMonth VARCHAR(7))

DECLARE @tblCustomers TABLE ( CustomerID CHAR(10),

CompanyName VARCHAR(50),

ContactName VARCHAR(50))

DECLARE @tblFinal TABLE ( sMonth VARCHAR(7),

CustomerID CHAR(10),

ContactName VARCHAR(50),

mSales MONEY)

@dtDate DATETIME,

@i INTEGER

SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1) AS

VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + '

— Get all months into the first table

SET @i = 0

WHILE (@i < 12)

BEGIN

SET @dtDate = DATEADD(mm, -1 * @i, @dtEndDate)

INSERT INTO @tblMonths SELECT CAST(YEAR(@dtDate) AS VARCHAR(4)) + '-' +

WHEN MONTH(@dtDate) < 10

THEN '0' + CAST(MONTH(@dtDate) AS VARCHAR(2))

ELSE CAST(MONTH(@dtDate) AS VARCHAR(2))

END AS sMonth

SET @i = @i + 1

END

— Get all clients who had sales during that period into the "y" table

INSERT INTO @tblCustomers

SELECT DISTINCT

INSERT INTO @tblFinal

SELECT m.sMonth,

FROM @tblMonths m CROSS JOIN @tblCustomers c

UPDATE @tblFinal SET

mSales = mydata.mSales

FROM @tblFinal f INNER JOIN

(

SELECT c.CustomerID,

CASE WHEN MONTH(o.OrderDate) < 10

) mydata on f.CustomerID = mydata.CustomerID AND f.sMonth =

mydata.sMonth

SELECT f.sMonth,

f.CustomerID,

f.CompanyName,

f.ContactName,

f.mSales

FROM @tblFinal f

f.sMonth