五種提高 SQL 性能的方法

本文節選自MSDN的文章《五種提高 SQL 性能的方法》，提出如何提高基于SQL Server應用程式的運作效率，非常值得推薦。對一些Traffic很高的應用系統而言，如何提高和改進SQL指令，是非常重要的，也是一個很好的突破點。

*文章主要包括如下一些内容（如感興趣，請直接通路下面的URL閱讀完整的中英文文檔）：

1, 從 INSERT 傳回 IDENTITY 

SELECT @@IDENTITY

2, 内嵌視圖與臨時表 

臨時表 － 在 tempdb 中的臨時表會導緻查詢進行大量 I/O 操作和磁盤通路，臨時表會消耗大量資源。

内嵌視圖 －使用内嵌視圖取代臨時表。内嵌視圖隻是一個可以聯接到 FROM 子句中的查詢。如果隻需要将資料聯接到其他查詢，則可以試試使用内嵌視圖，以節省資源。

3, 避免 LEFT JOIN 和 NULL 

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

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

DECLARE @tblMonths TABLE (sMonth VARCHAR(7))

4, 靈活使用笛卡爾乘積 

對于此技巧，我将進行非常詳細的介紹，并提倡在某些情況下使用笛卡爾乘積。出于某些原因，笛卡爾乘積 (CROSS JOIN) 遭到了很多譴責，開發人員通常會被警告根本就不要使用它們。在許多情況下，它們消耗的資源太多，進而無法高效使用。但是像 SQL 中的任何工具一樣，如果正确使用，它們也會很有價值。

其中一段示例代碼，值得效仿：

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

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)

DECLARE @dtStartDate DATETIME,

    @dtEndDate DATETIME,

    @dtDate DATETIME,

    @i INTEGER

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)

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

                CASE

                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

        c.CustomerID,

        c.CompanyName,

        c.ContactName

    FROM Customers c

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

    WHERE    o.OrderDate BETWEEN @dtStartDate AND @dtEndDate

INSERT INTO @tblFinal

SELECT    m.sMonth,

    c.CustomerID,

    c.CompanyName,

    c.ContactName,

FROM @tblMonths m CROSS JOIN @tblCustomers c

UPDATE @tblFinal  SET

    mSales = mydata.mSales

FROM @tblFinal f INNER JOIN

    (

    SELECT    c.CustomerID,

        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,

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

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

    GROUP BY

        END

    ) 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

ORDER BY

    f.sMonth

5, 拾遺補零 

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

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

－－－－－－－－－－－－－－－－－－－－－

中文URL：

http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx

英文URL：

http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/