Five Ways to Rev up Your SQL Performance

本文節(jié)選自MSDN的文章《五種提高 SQL 性能的方法》，提出如何提高基于SQL Server應(yīng)用程序的運(yùn)行效率，非常值得推薦。對(duì)一些Traffic很高的應(yīng)用系統(tǒng)而言，如何提高和改進(jìn)SQL指令，是非常重要的，也是一個(gè)很好的突破點(diǎn)。
*文章主要包括如下一些內(nèi)容（如感興趣，請(qǐng)直接訪問(wèn)下面的URL閱讀完整的中英文文檔）：

1, 從 INSERT 返回 IDENTITY 
SELECT @@IDENTITY

2, 內(nèi)嵌視圖與臨時(shí)表 
臨時(shí)表 － 在 tempdb 中的臨時(shí)表會(huì)導(dǎo)致查詢(xún)進(jìn)行大量 I/O 操作和磁盤(pán)訪問(wèn)，臨時(shí)表會(huì)消耗大量資源。
內(nèi)嵌視圖 －使用內(nèi)嵌視圖取代臨時(shí)表。內(nèi)嵌視圖只是一個(gè)可以聯(lián)接到 FROM 子句中的查詢(xún)。如果只需要將數(shù)據(jù)聯(lián)接到其他查詢(xún)，則可以試試使用內(nèi)嵌視圖，以節(jié)省資源。

3, 避免 LEFT JOIN 和 NULL 
LEFT JOIN 消耗的資源非常之多，因?yàn)樗鼈儼c NULL（不存在）數(shù)據(jù)匹配的數(shù)據(jù)。在某些情況下，這是不可避免的，但是代價(jià)可能非常高。LEFT JOIN 比 INNER JOIN 消耗資源更多，所以如果您可以重新編寫(xiě)查詢(xún)以使得該查詢(xún)不使用任何 LEFT JOIN，則會(huì)得到非常可觀的回報(bào)。

加快使用 LEFT JOIN 的查詢(xún)速度的一項(xiàng)技術(shù)涉及創(chuàng)建一個(gè) TABLE 數(shù)據(jù)類(lèi)型，插入第一個(gè)表（LEFT JOIN 左側(cè)的表）中的所有行，然后使用第二個(gè)表中的值更新 TABLE 數(shù)據(jù)類(lèi)型。此技術(shù)是一個(gè)兩步的過(guò)程，但與標(biāo)準(zhǔn)的 LEFT JOIN 相比，可以節(jié)省大量時(shí)間。一個(gè)很好的規(guī)則是嘗試各種不同的技術(shù)并記錄每種技術(shù)所需的時(shí)間，直到獲得用于您的應(yīng)用程序的執(zhí)行性能最佳的查詢(xún)。
DECLARE @tblMonths TABLE (sMonth VARCHAR(7))

4, 靈活使用笛卡爾乘積 
對(duì)于此技巧，我將進(jìn)行非常詳細(xì)的介紹，并提倡在某些情況下使用笛卡爾乘積。出于某些原因，笛卡爾乘積 (CROSS JOIN) 遭到了很多譴責(zé)，開(kāi)發(fā)人員通常會(huì)被警告根本就不要使用它們。在許多情況下，它們消耗的資源太多，從而無(wú)法高效使用。但是像 SQL 中的任何工具一樣，如果正確使用，它們也會(huì)很有價(jià)值。

其中一段示例代碼，值得效仿：
-- 笛卡爾乘積則可以返回所有月份的所有客戶(hù)。笛卡爾乘積基本上是將第一個(gè)表與第二個(gè)表相乘，生成一個(gè)行集合，其中包含第一個(gè)表中的行數(shù)與第二個(gè)表中的行數(shù)相乘的結(jié)果。因此，笛卡爾乘積會(huì)向表 @tblFinal 返回 12（所有月份）*81（所有客戶(hù)）=972 行。最后的步驟是使用此日期范圍內(nèi)每個(gè)客戶(hù)的月銷(xiāo)售額總計(jì)更新 @tblFinal 表，以及選擇最終的行集。

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),
            CompanyName VARCHAR(50),
            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,
    0
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
    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
    GROUP BY
        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
    ) 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.CompanyName,
    f.sMonth

5, 拾遺補(bǔ)零 
這里介紹其他一些可幫助提高 SQL 查詢(xún)效率的常用技術(shù)。假設(shè)您將按區(qū)域?qū)λ袖N(xiāo)售人員進(jìn)行分組并將他們的銷(xiāo)售額進(jìn)行小計(jì)，但是您只想要那些數(shù)據(jù)庫(kù)中標(biāo)記為處于活動(dòng)狀態(tài)的銷(xiāo)售人員。您可以按區(qū)域?qū)︿N(xiāo)售人員分組，并使用 HAVING 子句消除那些未處于活動(dòng)狀態(tài)的銷(xiāo)售人員，也可以在 WHERE 子句中執(zhí)行此操作。在 WHERE 子句中執(zhí)行此操作會(huì)減少需要分組的行數(shù)，所以比在 HAVING 子句中執(zhí)行此操作效率更高。HAVING 子句中基于行的條件的篩選會(huì)強(qiáng)制查詢(xún)對(duì)那些在 WHERE 子句中會(huì)被去除的數(shù)據(jù)進(jìn)行分組。

另一個(gè)提高效率的技巧是使用 DISTINCT 關(guān)鍵字查找數(shù)據(jù)行的單獨(dú)報(bào)表，來(lái)代替使用 GROUP BY 子句。在這種情況下，使用 DISTINCT 關(guān)鍵字的 SQL 效率更高。請(qǐng)?jiān)谛枰?jì)算聚合函數(shù)（SUM、COUNT、MAX 等）的情況下再使用 GROUP BY。另外，如果您的查詢(xún)總是自己返回一個(gè)唯一的行，則不要使用 DISTINCT 關(guān)鍵字。在這種情況下，DISTINCT 關(guān)鍵字只會(huì)增加系統(tǒng)開(kāi)銷(xiāo)。
－－－－－－－－－－－－－－－－－－－－－
中文URL：
http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx
英文URL：
http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/