久久国产成人av_抖音国产毛片_a片网站免费观看_A片无码播放手机在线观看,色五月在线观看,亚洲精品m在线观看,女人自慰的免费网址,悠悠在线观看精品视频,一级日本片免费的,亚洲精品久,国产精品成人久久久久久久

分享

CSDN技術(shù)中心 五種提高 SQL 性能的方法

 amber112 2005-09-21
五種提高 SQL 性能的方法
author:Johnny Papa

Data Points Archive

有時(shí),, 為了讓應(yīng)用程序運(yùn)行得更快,所做的全部工作就是在這里或那里做一些很小調(diào)整,。啊,,但關(guān)鍵在于確定如何進(jìn)行調(diào)整!遲早您會(huì)遇到這種情況:應(yīng)用程序中的 SQL 查詢不能按照您想要的方式進(jìn)行響應(yīng),。它要么不返回?cái)?shù)據(jù),,要么耗費(fèi)的時(shí)間長得出奇。如果它降低了報(bào)告或您的企業(yè)應(yīng)用程序的速度,,用戶必須等待的時(shí)間過長,,他們就會(huì)很不滿意。就像您的父母不想聽您解釋為什么在深更半夜才回來一樣,,用戶也不會(huì)聽你解釋為什么查詢耗費(fèi)這么長時(shí)間,。(“對(duì)不起,媽媽,,我使用了太多的 LEFT JOIN,。”)用戶希望應(yīng)用程序響應(yīng)迅速,他們的報(bào)告能夠在瞬間之內(nèi)返回分析數(shù)據(jù),。就我自己而言,,如果在 Web 上沖浪時(shí)某個(gè)頁面要耗費(fèi)十多秒才能加載(好吧,五秒更實(shí)際一些),,我也會(huì)很不耐煩,。

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



本頁內(nèi)容
從 INSERT 返回 IDENTITY  
內(nèi)嵌視圖與臨時(shí)表  
避免 LEFT JOIN 和 NULL  
靈活使用笛卡爾乘積  
拾遺補(bǔ)零  

從 INSERT 返回 IDENTITY
我決定從遇到許多問題的內(nèi)容入手:如何在執(zhí)行 SQL INSERT 后檢索 IDENTITY 值。通常,,問題不在于如何編寫檢索值的查詢,,而在于在哪里以及何時(shí)進(jìn)行檢索,。在 SQL Server 中,,下面的語句可用于檢索由最新在活動(dòng)數(shù)據(jù)庫連接上運(yùn)行的 SQL 語句所創(chuàng)建的 IDENTITY 值:

SELECT @@IDENTITY
這個(gè) SQL 語句并不復(fù)雜,但需要記住的一點(diǎn)是:如果這個(gè)最新的 SQL 語句不是 INSERT,,或者您針對(duì)非 INSERT SQL 的其他連接運(yùn)行了此 SQL,,則不會(huì)獲得期望的值。您必須運(yùn)行下列代碼才能檢索緊跟在 INSERT SQL 之后且位于同一連接上的 IDENTITY,,如下所示:

INSERT INTO Products (ProductName) VALUES (‘Chalk‘)
SELECT @@IDENTITY
在一個(gè)連接上針對(duì) Northwind 數(shù)據(jù)庫運(yùn)行這些查詢將返回一個(gè)名稱為 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 不要返回查詢的行計(jì)數(shù),,然后執(zhí)行 INSERT 語句,并返回剛剛為這個(gè)新行創(chuàng)建的 IDENTITY 值,。SET NOCOUNT ON 語句表示返回的記錄集有一行和一列,其中包含了這個(gè)新的 IDENTITY 值,。如果沒有此語句,,則會(huì)首先返回一個(gè)空的記錄集(因?yàn)?INSERT 語句不返回任何數(shù)據(jù)),然后會(huì)返回第二個(gè)記錄集,,第二個(gè)記錄集中包含 IDENTITY 值,。這可能有些令人困惑,尤其是因?yàn)槟鷱膩砭蜎]有希望過 INSERT 會(huì)返回記錄集,。之所以會(huì)發(fā)生此情況,是因?yàn)?SQL Server 看到了這個(gè)行計(jì)數(shù)(即一行受到影響)并將其解釋為表示一個(gè)記錄集,。因此,,真正的數(shù)據(jù)被推回到了第二個(gè)記錄集。當(dāng)然您可以使用 ADO 中的 NextRecordset 方法獲取此第二個(gè)記錄集,,但如果總能夠首先返回該記錄集且只返回該記錄集,,則會(huì)更方便,也更有效率,。

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

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

觸發(fā)器只在 Products 表上發(fā)生 INSERT 時(shí)啟動(dòng),,所以它總是會(huì)在成功 INSERT 之后返回一個(gè) IDENTITY,。使用此技術(shù),您可以始終以相同的方式在應(yīng)用程序中檢索 IDENTITY 值,。

返回頁首
內(nèi)嵌視圖與臨時(shí)表
某些時(shí)候,,查詢需要將數(shù)據(jù)與其他一些可能只能通過執(zhí)行 GROUP BY 然后執(zhí)行標(biāo)準(zhǔn)查詢才能收集的數(shù)據(jù)進(jìn)行聯(lián)接。例如,,如果要查詢最新五個(gè)定單的有關(guān)信息,,您首先需要知道是哪些定單。這可以使用返回定單 ID 的 SQL 查詢來檢索,。此數(shù)據(jù)就會(huì)存儲(chǔ)在臨時(shí)表(這是一個(gè)常用技術(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 語句會(huì)創(chuàng)建一個(gè)臨時(shí)表,將數(shù)據(jù)插入該表中,,將其他數(shù)據(jù)與該表進(jìn)行聯(lián)接,,然后除去該臨時(shí)表。這會(huì)導(dǎo)致此查詢進(jìn)行大量 I/O 操作,,因此,,可以重新編寫查詢,使用內(nèi)嵌視圖取代臨時(shí)表,。內(nèi)嵌視圖只是一個(gè)可以聯(lián)接到 FROM 子句中的查詢,。所以,您不用在 tempdb 中的臨時(shí)表上耗費(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í)表會(huì)消耗大量資源,。如果只需要將數(shù)據(jù)聯(lián)接到其他查詢,,則可以試試使用內(nèi)嵌視圖,,以節(jié)省資源。

返回頁首
避免 LEFT JOIN 和 NULL
當(dāng)然,,有很多時(shí)候您需要執(zhí)行 LEFT JOIN 和使用 NULL 值,。但是,它們并不適用于所有情況,。改變 SQL 查詢的構(gòu)建方式可能會(huì)產(chǎn)生將一個(gè)花幾分鐘運(yùn)行的報(bào)告縮短到只花幾秒鐘這樣的天壤之別的效果。有時(shí),必須在查詢中調(diào)整數(shù)據(jù)的形態(tài),,使之適應(yīng)應(yīng)用程序所要求的顯示方式。雖然 TABLE 數(shù)據(jù)類型會(huì)減少大量占用資源的情況,,但在查詢中還有許多區(qū)域可以進(jìn)行優(yōu)化,。SQL 的一個(gè)有價(jià)值的常用功能是 LEFT JOIN。它可以用于檢索第一個(gè)表中的所有行,、第二個(gè)表中所有匹配的行,、以及第二個(gè)表中與第一個(gè)表不匹配的所有行。例如,,如果希望返回每個(gè)客戶及其定單,,使用 LEFT JOIN 則可以顯示有定單和沒有定單的客戶。

此工具可能會(huì)被過度使用,。LEFT JOIN 消耗的資源非常之多,,因?yàn)樗鼈儼c NULL(不存在)數(shù)據(jù)匹配的數(shù)據(jù)。在某些情況下,,這是不可避免的,,但是代價(jià)可能非常高。LEFT JOIN 比 INNER JOIN 消耗資源更多,所以如果您可以重新編寫查詢以使得該查詢不使用任何 LEFT JOIN,,則會(huì)得到非??捎^的回報(bào)(請(qǐng)參閱圖 1 中的圖)。

圖 1 查詢

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

測(cè)試查詢的速度時(shí),有必要多次運(yùn)行此查詢,,然后取一個(gè)平均值,。因?yàn)椴樵儯ɑ虼鎯?chǔ)過程)可能會(huì)存儲(chǔ)在 SQL Server 內(nèi)存中的過程緩存中,因此第一次嘗試耗費(fèi)的時(shí)間好像稍長一些,,而所有后續(xù)嘗試耗費(fèi)的時(shí)間都較短,。另外,運(yùn)行您的查詢時(shí),,可能正在針對(duì)相同的表運(yùn)行其他查詢,。當(dāng)其他查詢鎖定和解鎖這些表時(shí),可能會(huì)導(dǎo)致您的查詢要排隊(duì)等待,。例如,,如果您進(jìn)行查詢時(shí)某人正在更新此表中的數(shù)據(jù),則在更新提交時(shí)您的查詢可能需要耗費(fèi)更長時(shí)間來執(zhí)行,。

避免使用 LEFT JOIN 時(shí)速度降低的最簡單方法是盡可能多地圍繞它們?cè)O(shè)計(jì)數(shù)據(jù)庫,。例如,假設(shè)某一產(chǎn)品可能具有類別也可能沒有類別,。如果 Products 表存儲(chǔ)了其類別的 ID,,而沒有用于某個(gè)特定產(chǎn)品的類別,則您可以在字段中存儲(chǔ) NULL 值,。然后您必須執(zhí)行 LEFT JOIN 來獲取所有產(chǎn)品及其類別,。您可以創(chuàng)建一個(gè)值為“No Category”的類別,從而指定外鍵關(guān)系不允許 NULL 值,。通過執(zhí)行上述操作,,現(xiàn)在您就可以使用 INNER JOIN 檢索所有產(chǎn)品及其類別了。雖然這看起來好像是一個(gè)帶有多余數(shù)據(jù)的變通方法,,但可能是一個(gè)很有價(jià)值的技術(shù),,因?yàn)樗梢韵?SQL 批處理語句中消耗資源較多的 LEFT JOIN,。在數(shù)據(jù)庫中全部使用此概念可以為您節(jié)省大量的處理時(shí)間。請(qǐng)記住,,對(duì)于您的用戶而言,,即使幾秒鐘的時(shí)間也非常重要,因?yàn)楫?dāng)您有許多用戶正在訪問同一個(gè)聯(lián)機(jī)數(shù)據(jù)庫應(yīng)用程序時(shí),,這幾秒鐘實(shí)際上的意義會(huì)非常重大,。

返回頁首
靈活使用笛卡爾乘積
對(duì)于此技巧,我將進(jìn)行非常詳細(xì)的介紹,,并提倡在某些情況下使用笛卡爾乘積,。出于某些原因,笛卡爾乘積 (CROSS JOIN) 遭到了很多譴責(zé),,開發(fā)人員通常會(huì)被警告根本就不要使用它們,。在許多情況下,它們消耗的資源太多,,從而無法高效使用,。但是像 SQL 中的任何工具一樣,如果正確使用,,它們也會(huì)很有價(jià)值,。例如,如果您想運(yùn)行一個(gè)返回每月數(shù)據(jù)(即使某一特定月份客戶沒有定單也要返回)的查詢,,您就可以很方便地使用笛卡爾乘積,。 圖 2 中的 SQL 就執(zhí)行了上述操作。

雖然這看起來好像沒什么神奇的,,但是請(qǐng)考慮一下,,如果您從客戶到定單(這些定單按月份進(jìn)行分組并對(duì)銷售額進(jìn)行小計(jì))進(jìn)行了標(biāo)準(zhǔn)的 INNER JOIN,則只會(huì)獲得客戶有定單的月份,。因此,對(duì)于客戶未訂購任何產(chǎn)品的月份,,您不會(huì)獲得 0 值,。如果您想為每個(gè)客戶都繪制一個(gè)圖,以顯示每個(gè)月和該月銷售額,,則可能希望此圖包括月銷售額為 0 的月份,,以便直觀標(biāo)識(shí)出這些月份。如果使用 圖 2 中的 SQL,,數(shù)據(jù)則會(huì)跳過銷售額為 0 美元的月份,,因?yàn)樵诙▎伪碇袑?duì)于零銷售額不會(huì)包含任何行(假設(shè)您只存儲(chǔ)發(fā)生的事件)。

圖 3 中的代碼雖然較長,,但是可以達(dá)到獲取所有銷售數(shù)據(jù)(甚至包括沒有銷售額的月份)的目標(biāo),。首先,它會(huì)提取去年所有月份的列表,然后將它們放入第一個(gè) TABLE 數(shù)據(jù)類型表 (@tblMonths) 中,。下一步,,此代碼會(huì)獲取在該時(shí)間段內(nèi)有銷售額的所有客戶公司的名稱列表,然后將它們放入另一個(gè) TABLE 數(shù)據(jù)類型表 (@tblCus-tomers) 中,。這兩個(gè)表存儲(chǔ)了創(chuàng)建結(jié)果集所必需的所有基本數(shù)據(jù),,但實(shí)際銷售數(shù)量除外。 第一個(gè)表中列出了所有月份(12 行),,第二個(gè)表中列出了這個(gè)時(shí)間段內(nèi)有銷售額的所有客戶(對(duì)于我是 81 個(gè)),。并非每個(gè)客戶在過去 12 個(gè)月中的每個(gè)月都購買了產(chǎn)品,所以,,執(zhí)行 INNER JOIN 或 LEFT JOIN 不會(huì)返回每個(gè)月的每個(gè)客戶,。這些操作只會(huì)返回購買產(chǎn)品的客戶和月份。

笛卡爾乘積則可以返回所有月份的所有客戶,。笛卡爾乘積基本上是將第一個(gè)表與第二個(gè)表相乘,,生成一個(gè)行集合,其中包含第一個(gè)表中的行數(shù)與第二個(gè)表中的行數(shù)相乘的結(jié)果,。因此,,笛卡爾乘積會(huì)向表 @tblFinal 返回 972 行。最后的步驟是使用此日期范圍內(nèi)每個(gè)客戶的月銷售額總計(jì)更新 @tblFinal 表,,以及選擇最終的行集,。

如果由于笛卡爾乘積占用的資源可能會(huì)很多,而不需要真正的笛卡爾乘積,,則可以謹(jǐn)慎地使用 CROSS JOIN,。例如,如果對(duì)產(chǎn)品和類別執(zhí)行了 CROSS JOIN,,然后使用 WHERE 子句,、DISTINCT 或 GROUP BY 來篩選出大多數(shù)行,那么使用 INNER JOIN 會(huì)獲得同樣的結(jié)果,,而且效率高得多,。如果需要為所有的可能性都返回?cái)?shù)據(jù)(例如在您希望使用每月銷售日期填充一個(gè)圖表時(shí)),則笛卡爾乘積可能會(huì)非常有幫助,。但是,,您不應(yīng)該將它們用于其他用途,因?yàn)樵诖蠖鄶?shù)方案中 INNER JOIN 的效率要高得多,。

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

另一個(gè)提高效率的技巧是使用 DISTINCT 關(guān)鍵字查找數(shù)據(jù)行的單獨(dú)報(bào)表,,來代替使用 GROUP BY 子句,。在這種情況下,使用 DISTINCT 關(guān)鍵字的 SQL 效率更高,。請(qǐng)?jiān)谛枰?jì)算聚合函數(shù)(SUM,、COUNT、MAX 等)的情況下再使用 GROUP BY,。另外,,如果您的查詢總是自己返回一個(gè)唯一的行,則不要使用 DISTINCT 關(guān)鍵字,。在這種情況下,,DISTINCT 關(guān)鍵字只會(huì)增加系統(tǒng)開銷。

您已經(jīng)看到了,,有大量技術(shù)都可用于優(yōu)化查詢和實(shí)現(xiàn)特定的業(yè)務(wù)規(guī)則,,技巧就是進(jìn)行一些嘗試,然后比較它們的性能,。最重要的是要測(cè)試,、測(cè)試、再測(cè)試,。在此專欄的將來各期內(nèi)容中,,我將繼續(xù)深入講述 SQL Server 概念,包括數(shù)據(jù)庫設(shè)計(jì),、好的索引實(shí)踐以及 SQL Server 安全范例,。

    本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間,所有內(nèi)容均由用戶發(fā)布,,不代表本站觀點(diǎn),。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息,,謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容,,請(qǐng)點(diǎn)擊一鍵舉報(bào),。
    轉(zhuǎn)藏 分享 獻(xiàn)花(0

    0條評(píng)論

    發(fā)表

    請(qǐng)遵守用戶 評(píng)論公約

    類似文章 更多