 |
從 INSERT 返回 IDENTITY |
|
內(nèi)嵌視圖與臨時表 |
|
避免 LEFT JOIN 和 NULL |
|
靈活使用笛卡爾乘積 |
|
拾遺補(bǔ)零 |
我決定從遇到許多問題的內(nèi)容入手：如何在執(zhí)行 SQL INSERT 后檢索 IDENTITY 值。通常，問題不在于如何編寫檢索值的查詢，而在于在哪里以及何時進(jìn)行檢索。在 SQL Server 中，下面的語句可用于檢索由最新在活動數(shù)據(jù)庫連接上運(yùn)行的 SQL 語句所創(chuàng)建的 IDENTITY 值：
SELECT @@IDENTITY
這個 SQL 語句并不復(fù)雜，但需要記住的一點(diǎn)是：如果這個最新的 SQL 語句不是 INSERT，或者您針對非 INSERT SQL 的其他連接運(yùn)行了此 SQL，則不會獲得期望的值。您必須運(yùn)行下列代碼才能檢索緊跟在 INSERT SQL 之后且位于同一連接上的 IDENTITY，如下所示：
INSERT INTO Products (ProductName) VALUES ('Chalk')
SELECT @@IDENTITY
在一個連接上針對 Northwind 數(shù)據(jù)庫運(yùn)行這些查詢將返回一個名稱為 Chalk 的新產(chǎn)品的 IDENTITY 值。所以，在使用 ADO 的 Visual Basic? 應(yīng)用程序中，可以運(yùn)行以下語句：
Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _
(ProductName) VALUES ('Chalk');SELECT @@IDENTITY")
lProductID = oRs(0)
此代碼告訴 SQL Server 不要返回查詢的行計數(shù)，然后執(zhí)行 INSERT 語句，并返回剛剛為這個新行創(chuàng)建的 IDENTITY 值。SET NOCOUNT ON 語句表示返回的記錄集有一行和一列，其中包含了這個新的 IDENTITY 值。如果沒有此語句，則會首先返回一個空的記錄集（因?yàn)?INSERT 語句不返回任何數(shù)據(jù)），然后會返回第二個記錄集，第二個記錄集中包含 IDENTITY 值。這可能有些令人困惑，尤其是因?yàn)槟鷱膩砭蜎]有希望過 INSERT 會返回記錄集。之所以會發(fā)生此情況，是因?yàn)?SQL Server 看到了這個行計數(shù)（即一行受到影響）并將其解釋為表示一個記錄集。因此，真正的數(shù)據(jù)被推回到了第二個記錄集。當(dāng)然您可以使用 ADO 中的 NextRecordset 方法獲取此第二個記錄集，但如果總能夠首先返回該記錄集且只返回該記錄集，則會更方便，也更有效率。
此方法雖然有效，但需要在 SQL 語句中額外添加一些代碼。獲得相同結(jié)果的另一方法是在 INSERT 之前使用 SET NOCOUNT ON 語句，并將 SELECT @@IDENTITY 語句放在表中的 FOR INSERT 觸發(fā)器中，如下面的代碼片段所示。這樣，任何進(jìn)入該表的 INSERT 語句都將自動返回 IDENTITY 值。
CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS SELECT @@IDENTITY GO
觸發(fā)器只在 Products 表上發(fā)生 INSERT 時啟動，所以它總是會在成功 INSERT 之后返回一個 IDENTITY。使用此技術(shù)，您可以始終以相同的方式在應(yīng)用程序中檢索 IDENTITY 值。
返回頁首某些時候，查詢需要將數(shù)據(jù)與其他一些可能只能通過執(zhí)行 GROUP BY 然后執(zhí)行標(biāo)準(zhǔn)查詢才能收集的數(shù)據(jù)進(jìn)行聯(lián)接。例如，如果要查詢最新五個定單的有關(guān)信息，您首先需要知道是哪些定單。這可以使用返回定單 ID 的 SQL 查詢來檢索。此數(shù)據(jù)就會存儲在臨時表（這是一個常用技術(shù)）中，然后與 Products 表進(jìn)行聯(lián)接，以返回這些定單售出的產(chǎn)品數(shù)量：
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 語句會創(chuàng)建一個臨時表，將數(shù)據(jù)插入該表中，將其他數(shù)據(jù)與該表進(jìn)行聯(lián)接，然后除去該臨時表。這會導(dǎo)致此查詢進(jìn)行大量 I/O 操作，因此，可以重新編寫查詢，使用內(nèi)嵌視圖取代臨時表。內(nèi)嵌視圖只是一個可以聯(lián)接到 FROM 子句中的查詢。所以，您不用在 tempdb 中的臨時表上耗費(fèi)大量 I/O 和磁盤訪問，而可以使用內(nèi)嵌視圖得到同樣的結(jié)果：
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
此查詢不僅比前面的查詢效率更高，而且長度更短。臨時表會消耗大量資源。如果只需要將數(shù)據(jù)聯(lián)接到其他查詢，則可以試試使用內(nèi)嵌視圖，以節(jié)省資源。
返回頁首當(dāng)然，有很多時候您需要執(zhí)行 LEFT JOIN 和使用 NULL 值。但是，它們并不適用于所有情況。改變 SQL 查詢的構(gòu)建方式可能會產(chǎn)生將一個花幾分鐘運(yùn)行的報告縮短到只花幾秒鐘這樣的天壤之別的效果。有時，必須在查詢中調(diào)整數(shù)據(jù)的形態(tài)，使之適應(yīng)應(yīng)用程序所要求的顯示方式。雖然 TABLE 數(shù)據(jù)類型會減少大量占用資源的情況，但在查詢中還有許多區(qū)域可以進(jìn)行優(yōu)化。SQL 的一個有價值的常用功能是 LEFT JOIN。它可以用于檢索第一個表中的所有行、第二個表中所有匹配的行、以及第二個表中與第一個表不匹配的所有行。例如，如果希望返回每個客戶及其定單，使用 LEFT JOIN 則可以顯示有定單和沒有定單的客戶。
此工具可能會被過度使用。LEFT JOIN 消耗的資源非常之多，因?yàn)樗鼈儼c NULL（不存在）數(shù)據(jù)匹配的數(shù)據(jù)。在某些情況下，這是不可避免的，但是代價可能非常高。LEFT JOIN 比 INNER JOIN 消耗資源更多，所以如果您可以重新編寫查詢以使得該查詢不使用任何 LEFT JOIN，則會得到非?？捎^的回報（請參閱圖 1 中的圖）。
圖 1：查詢
加快使用 LEFT JOIN 的查詢速度的一項(xiàng)技術(shù)涉及創(chuàng)建一個 TABLE 數(shù)據(jù)類型，插入第一個表（LEFT JOIN 左側(cè)的表）中的所有行，然后使用第二個表中的值更新 TABLE 數(shù)據(jù)類型。此技術(shù)是一個兩步的過程，但與標(biāo)準(zhǔn)的 LEFT JOIN 相比，可以節(jié)省大量時間。一個很好的規(guī)則是嘗試各種不同的技術(shù)并記錄每種技術(shù)所需的時間，直到獲得用于您的應(yīng)用程序的執(zhí)行性能最佳的查詢。
測試查詢的速度時，有必要多次運(yùn)行此查詢，然后取一個平均值。因?yàn)椴樵儯ɑ虼鎯^程）可能會存儲在 SQL Server 內(nèi)存中的過程緩存中，因此第一次嘗試耗費(fèi)的時間好像稍長一些，而所有后續(xù)嘗試耗費(fèi)的時間都較短。另外，運(yùn)行您的查詢時，可能正在針對相同的表運(yùn)行其他查詢。當(dāng)其他查詢鎖定和解鎖這些表時，可能會導(dǎo)致您的查詢要排隊(duì)等待。例如，如果您進(jìn)行查詢時某人正在更新此表中的數(shù)據(jù)，則在更新提交時您的查詢可能需要耗費(fèi)更長時間來執(zhí)行。
避免使用 LEFT JOIN 時速度降低的最簡單方法是盡可能多地圍繞它們設(shè)計數(shù)據(jù)庫。例如，假設(shè)某一產(chǎn)品可能具有類別也可能沒有類別。如果 Products 表存儲了其類別的 ID，而沒有用于某個特定產(chǎn)品的類別，則您可以在字段中存儲 NULL 值。然后您必須執(zhí)行 LEFT JOIN 來獲取所有產(chǎn)品及其類別。您可以創(chuàng)建一個值為“No Category”的類別，從而指定外鍵關(guān)系不允許 NULL 值。通過執(zhí)行上述操作，現(xiàn)在您就可以使用 INNER JOIN 檢索所有產(chǎn)品及其類別了。雖然這看起來好像是一個帶有多余數(shù)據(jù)的變通方法，但可能是一個很有價值的技術(shù)，因?yàn)樗梢韵?SQL 批處理語句中消耗資源較多的 LEFT JOIN。在數(shù)據(jù)庫中全部使用此概念可以為您節(jié)省大量的處理時間。請記住，對于您的用戶而言，即使幾秒鐘的時間也非常重要，因?yàn)楫?dāng)您有許多用戶正在訪問同一個聯(lián)機(jī)數(shù)據(jù)庫應(yīng)用程序時，這幾秒鐘實(shí)際上的意義會非常重大。
返回頁首對于此技巧，我將進(jìn)行非常詳細(xì)的介紹，并提倡在某些情況下使用笛卡爾乘積。出于某些原因，笛卡爾乘積 (CROSS JOIN) 遭到了很多譴責(zé)，開發(fā)人員通常會被警告根本就不要使用它們。在許多情況下，它們消耗的資源太多，從而無法高效使用。但是像 SQL 中的任何工具一樣，如果正確使用，它們也會很有價值。例如，如果您想運(yùn)行一個返回每月數(shù)據(jù)（即使某一特定月份客戶沒有定單也要返回）的查詢，您就可以很方便地使用笛卡爾乘積。 圖 2 中的 SQL 就執(zhí)行了上述操作。
雖然這看起來好像沒什么神奇的，但是請考慮一下，如果您從客戶到定單（這些定單按月份進(jìn)行分組并對銷售額進(jìn)行小計）進(jìn)行了標(biāo)準(zhǔn)的 INNER JOIN，則只會獲得客戶有定單的月份。因此，對于客戶未訂購任何產(chǎn)品的月份，您不會獲得 0 值。如果您想為每個客戶都繪制一個圖，以顯示每個月和該月銷售額，則可能希望此圖包括月銷售額為 0 的月份，以便直觀標(biāo)識出這些月份。如果使用 圖 2 中的 SQL，數(shù)據(jù)則會跳過銷售額為 0 美元的月份，因?yàn)樵诙▎伪碇袑τ诹沅N售額不會包含任何行（假設(shè)您只存儲發(fā)生的事件）。
圖 3 中的代碼雖然較長，但是可以達(dá)到獲取所有銷售數(shù)據(jù)（甚至包括沒有銷售額的月份）的目標(biāo)。首先，它會提取去年所有月份的列表，然后將它們放入第一個 TABLE 數(shù)據(jù)類型表 (@tblMonths) 中。下一步，此代碼會獲取在該時間段內(nèi)有銷售額的所有客戶公司的名稱列表，然后將它們放入另一個 TABLE 數(shù)據(jù)類型表 (@tblCus-tomers) 中。這兩個表存儲了創(chuàng)建結(jié)果集所必需的所有基本數(shù)據(jù)，但實(shí)際銷售數(shù)量除外。 第一個表中列出了所有月份（12 行），第二個表中列出了這個時間段內(nèi)有銷售額的所有客戶（對于我是 81 個）。并非每個客戶在過去 12 個月中的每個月都購買了產(chǎn)品，所以，執(zhí)行 INNER JOIN 或 LEFT JOIN 不會返回每個月的每個客戶。這些操作只會返回購買產(chǎn)品的客戶和月份。
笛卡爾乘積則可以返回所有月份的所有客戶。笛卡爾乘積基本上是將第一個表與第二個表相乘，生成一個行集合，其中包含第一個表中的行數(shù)與第二個表中的行數(shù)相乘的結(jié)果。因此，笛卡爾乘積會向表 @tblFinal 返回 972 行。最后的步驟是使用此日期范圍內(nèi)每個客戶的月銷售額總計更新 @tblFinal 表，以及選擇最終的行集。
如果由于笛卡爾乘積占用的資源可能會很多，而不需要真正的笛卡爾乘積，則可以謹(jǐn)慎地使用 CROSS JOIN。例如，如果對產(chǎn)品和類別執(zhí)行了 CROSS JOIN，然后使用 WHERE 子句、DISTINCT 或 GROUP BY 來篩選出大多數(shù)行，那么使用 INNER JOIN 會獲得同樣的結(jié)果，而且效率高得多。如果需要為所有的可能性都返回數(shù)據(jù)（例如在您希望使用每月銷售日期填充一個圖表時），則笛卡爾乘積可能會非常有幫助。但是，您不應(yīng)該將它們用于其他用途，因?yàn)樵诖蠖鄶?shù)方案中 INNER JOIN 的效率要高得多。
返回頁首這里介紹其他一些可幫助提高 SQL 查詢效率的常用技術(shù)。假設(shè)您將按區(qū)域?qū)λ袖N售人員進(jìn)行分組并將他們的銷售額進(jìn)行小計，但是您只想要那些數(shù)據(jù)庫中標(biāo)記為處于活動狀態(tài)的銷售人員。您可以按區(qū)域?qū)︿N售人員分組，并使用 HAVING 子句消除那些未處于活動狀態(tài)的銷售人員，也可以在 WHERE 子句中執(zhí)行此操作。在 WHERE 子句中執(zhí)行此操作會減少需要分組的行數(shù)，所以比在 HAVING 子句中執(zhí)行此操作效率更高。HAVING 子句中基于行的條件的篩選會強(qiáng)制查詢對那些在 WHERE 子句中會被去除的數(shù)據(jù)進(jìn)行分組。
另一個提高效率的技巧是使用 DISTINCT 關(guān)鍵字查找數(shù)據(jù)行的單獨(dú)報表，來代替使用 GROUP BY 子句。在這種情況下，使用 DISTINCT 關(guān)鍵字的 SQL 效率更高。請在需要計算聚合函數(shù)（SUM、COUNT、MAX 等）的情況下再使用 GROUP BY。另外，如果您的查詢總是自己返回一個唯一的行，則不要使用 DISTINCT 關(guān)鍵字。在這種情況下，DISTINCT 關(guān)鍵字只會增加系統(tǒng)開銷。
您已經(jīng)看到了，有大量技術(shù)都可用于優(yōu)化查詢和實(shí)現(xiàn)特定的業(yè)務(wù)規(guī)則，技巧就是進(jìn)行一些嘗試，然后比較它們的性能。最重要的是要測試、測試、再測試。在此專欄的將來各期內(nèi)容中，我將繼續(xù)深入講述 SQL Server 概念，包括數(shù)據(jù)庫設(shè)計、好的索引實(shí)踐以及 SQL Server 安全范例。
如有向 Johnny 提出的問題和建議，請發(fā)送電子郵件到 mmdata@microsoft.com
Johnny Papa 是北卡羅來納州羅利市的 MJM 研究公司的信息技術(shù)副總裁，他著有?Professional ADO 25 RDS Programming with ASP 30?? (Wrox, 2000)，并經(jīng)常在行業(yè)會議中做演講。要與他聯(lián)系，請發(fā)送電子郵件到 datapoints@lancelotweb.com
摘自 MSDN Magazine 2002 年 7 月 刊?？梢栽谀?dāng)?shù)氐膱髷偵腺徺I此雜志
標(biāo)簽：寶雞 無錫 邯鄲 來賓 七臺河 西寧 汕尾 營口
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《五種提高 SQL 性能的方法》，本文關(guān)鍵詞 五種,提高,SQL,性能,的,方法,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。