技術文章 > 提高ADO性能的優秀經驗

提高ADO性能的優秀經驗

2020-02-27 11:43

文檔管理軟件,文檔管理系統,知識管理系統,檔案管理系統的技術資料:
一、概述

“性能”這一術語有著幾種不同的、差異微妙的含義。當人們談到某個東西性能多少好時,他們想要表達的意思可能就是在一定的時間之內它完成了多少工作。例如,一個性能好的發動機運行起來更穩定,產生的動力更強大。對于開發小組,你同樣也可能應用這個判斷標準:一個性能好的開發小組工作時比較安靜,而且能夠生產出大量高質量的代碼。對我來說,性能至少意味著兩件事情——我的代碼運行起來有多好,我的開發小組和我本人工作效率怎么樣。無論哪一方面,本文介紹的技巧都將起到一定的幫助作用:幫助你更快地編寫代碼,幫助你編寫更快的代碼——安靜地完成這一切,減少這樣那樣的錯誤。本文介紹的技巧主要面向ADO,特別是如何通過ADO訪問SQL Server。但與此同時,我還將涉及一些適用范圍更廣的COM技巧,它們適用于你所編寫的所有Visual Basic代碼。

為了了解從哪些SQL Server數據訪問代碼編寫技術、哪些體系、哪些開發習慣可以得到最好的性能,我已經花了不少時間。一些情況下,對于應用的整體性能來說,單一的技術意義很小,除非我們通過循環將性能的改善程度成倍放大。例如,在一個客戶機/服務器應用中,當我們不是通過指定ODBC數據源(DSN)的方式連接數據庫時,大約能夠節省一到二秒的時間。對于應用整體的適用性或性能來說,這部分節省的時間所產生的影響很小。但是,如果我們在一個中間層組件上應用這種技術,這個組件每分鐘(或每小時,每天)都要建立和關閉數據庫連接數百(甚至數千)次,那么,這種技術將顯著地影響系統的性能表現。因此,對于我在這里討論的每一種技術,請務必考慮這個倍數因子——即,在一定的時間周期內,你的系統將執行同一段代碼多少次。

當你開始尋求改進性能的方案時,請考慮一下你的應用(組件,或者是ASP代碼)大部份的等待和處理時間花在什么地方。如果你發現應用程序把大量的時間花在等待Open或Execute方法執行完成,那么,你應該認真地檢查一下服務器端的查詢策略。包括ADO在內,所有的數據訪問接口等待查詢結果的時間都相同。例如,如果你有一個查詢,SQL Server需要20秒才能完成它,不論用來執行該查詢的是什么接口,沒有一種接口能夠比其他接口以更快的速度返回結果。雖然有些接口打開連接的速度比較快,有些接口處理結果集的速度比較快,但沒有一種接口能夠影響數據庫引擎編譯和執行查詢的速度。因此,如果你的查詢具有太高的“挑戰性”——例如你沒有對索引進行優化,你沒有使用存儲過程,服務器負載過重,或者你要求返回的記錄數量太多——那么,世界上沒有一種ADO技術能夠幫助你提高性能。除非你解決了這些基本的查詢問題,否則沒有一種性能調整技術能夠顯著地改善整體性能。SQL Server的Query Analyzer是一個分析查詢性能的優秀工具。它能夠用圖形的方式顯示查詢的執行過程,并對改進性能的方法提出建議。

如果你能夠確信查詢具有較高的效率,那么,你可以使用本文介紹的技術進一步調整ADO代碼的性能。這里介紹的技巧將從各個方面幫助你簡化和改進ADO編程,包括:建立和維護連接,構造和提交執行速度更快的查詢,提高處理查詢結果的效率,等等。

二、建立連接

在一個客戶機/服務器應用中,我們可以用好幾種方法把建立和初始化數據庫連接所需要的時間隱藏起來,使得應用程序既能夠打開連接,又不需要用戶等待應用程序啟動。首先,我們可以嘗試異步連接。使用異步連接時,ADO啟動連接操作之后,不等待連接完成就把控制權返回給應用程序——這樣,應用程序就能夠接著執行大部份初始化操作,以更快的速度完成form_load事件處理。如果關閉并重新建立連接的時間小于連接池釋放連接的時間,那么這個連接實際上是即時的。但在許多情況下(特別是用戶數量不多時),讓連接保持打開狀態更具有現實意義。在中間層組件或ASP頁面內部,如果數據庫查詢多次重復出現,我建議你讓Connection對象保持打開狀態。

另外一個改進連接性能的辦法是,避免使用帶有DSN的ODBC。在Microsoft,ODBC已經轉入了Quick Fix Engineering(QFE,快速修理工程)狀態,它意味著:除非發現重大BUG,該公司將不再在ODBC或它的驅動程序上花時間。另外,考慮性能和部署問題時,ODBC DSN也是一個必須關注的問題。DSN必須安裝到客戶系統上,要求進行注冊表查找,與OLE DB連接相比,它建立連接所需要的時間更長——特別是當你用直接編碼的方式指定ConnectionString時,這一點尤其突出。從實際效果來看,避免使用DSN降低的系統開銷很有限:如果完全取消連接建立過程,對于每個連接,你也許能夠剩下二到五秒時間(假設數據庫連接池中已經沒有連接)。然而,如果你的應用程序需要頻繁地建立連接,節省的時間累計起來就很可觀了。

建立數據庫連接的時候,你要選擇一個數據提供者。Microsoft建議我們使用OLE DB提供者替代默認的ODBC提供者。對比最新的OLE DB本地提供者和功能類似但較早的ODBC提供者,我感到前者令人不愉快的意外之事較少。但無論是哪種情況,你都應該在決定使用某個新的提供者之前對應用進行完整地測試——代碼的性能、支持的功能、行為方式都有可能發生變化。

在中間層和ASP中,在保持連接打開的情況下,我們不能(從實踐來看)創建出可伸縮的組件——至少在多次調用之間是這樣的。一般地,當IIS引用和釋放組件、ASP頁面的實例時,組件和ASP頁面被頻繁地裝入、丟棄。由于基于ADO的代碼每次執行時都必須建立、使用、釋放數據庫連接,最小化連接復雜程度的策略對性能的提高程度達到了可明顯測量的程度。在這些情形下,對于我們連接數據庫的速度來說,連接/會話池有著重要的意義。如果你為Command對象的ConnectionString屬性指定合適的值(即,每次使用同樣的服務器、初始目錄、登錄ID和其他參數),那么,連接已經打開且處于可用狀態的機會很大。如果連接池中能夠找到匹配的連接,連接(或重新連接)的時間將接近0(通常小于250 ms)。

然而,如果ADO(或VB)代碼不釋放Connection對象,或者,我們在不同的實例之間改換了ConnectionString,OLE DB必須每次建立一個新的連接。如果出現了這種情況,我們將很快耗盡連接池內可用連接的數量。要確保連接被釋放,我們必須在關閉連接之后把Connection對象設置為Nothing。另外,不要在Recordset Open方法中使用ConnectionString,而是以獨立的方式打開Connection對象;這樣,當我們要關閉Connection對象以及要把它設置成Nothing的時候,引用它就很方便了。

三、構造和提交查詢

在構造查詢的時候,要搞清楚為什么必須這么做、為什么不能那么做是一個很復雜的問題。然而,一些基本的指導方針能夠讓構造高效查詢的過程更加流暢、輕松。一般地,你不應該讓查詢浪費服務器時間。下面幾個技巧能夠幫助你構造出更好、更高效的查詢。

不要強制SQL Server每次執行查詢的時候重新編譯和構造查詢執行計劃。避免這種重復操作的一種簡單方法是使用帶有參數的存儲過程。注意盡量不要使用ADO Command對象的Prepare屬性——有時它不能正確工作。如果使用存儲過程,你還可以通過消除不必要的“受影響行數”返回值進一步提高ADO性能——只需在存儲過程中加入SET NOCOUNT ON就可以了。

盡量減少與服務器的通信次數。如果你有幾個相關的操作要執行,請把它們合并為一個存儲過程,或者是一個可以在服務器上作為腳本執行的復合查詢。避免使用方法(比如Refresh)和不適當的Parameters集合引用,它們會強制ADO增加額外的服務器通信過程。

在客戶機/服務器應用中,只構造Command對象一次,而不是每次使用Command對象的時候重新構造。你可以重新設置Command的參數值,然后在需要時執行它。

當查詢返回的不是一個記錄集時,確保使用了adExecuteNoRecords選項,告訴ADO越過所有那些用來接收和構造記錄集(Recordset格式)的代碼。你可以把adExecuteNoRecords選項傳遞給Execute方法,或把它作為Command的選項。

執行返回簡單記錄集的存儲過程時,不要使用Command對象。所有的存儲過程(以及Command對象)可以作為Connection對象的COM方法出現。讓存儲過程作為Connection對象的方法出現有著顯著的性能優勢,同時它也簡化了代碼。盡管這種技術對于那些有Return Status值或Output參數的存儲過程沒有什么幫助,但對于動作查詢(INSERT、DELETE等)以及那些返回一個或多個記錄的查詢來說,這種技術很有用。把存儲過程作為Connection的方法之后,你可以用方法參數的形式傳入存儲過程的輸入參數;如果調用存儲過程返回了一個記錄集,你可以通過方法調用中最后一個參數引用該Recordset。例如,下面的ADO語句執行一個名為“Fred”的存儲過程,Fred存儲過程有兩個輸入參數,返回一個Recordset:
MyConnection.Fred "InputArg1", 2, myRecordset

編寫代碼的時候,不要寄希望于VB的自動完成功能會把存儲過程或Command對象名字視為合法的Connection對象的方法。在正式運行之前,COM不會解析這類名字。

除非絕對必要,否則不要返回記錄集。當正在執行的查詢返回記錄時,ADO就會構造一個Recordset對象。構造Recordset對象的開銷很大,因此你應該盡量避免使用Recordset對象。注意有時候執行查詢雖然返回結果,但不是返回記錄。例如,你可以通過Return Status參數返回整數值。另外,你可以返回Output參數來替代需要構造Recordset對象的記錄集,SQL Server允許返回的Output參數多達1000個。

只要有可能,請用動作查詢(INSERT,UPDATE,DELETE和執行這些操作的存儲過程)替代可更新的Recordset游標。此時,你應該使用Execute方法和它的adExecuteNoRecords選項,確保ADO能夠知道查詢不需要構造Recordset對象。

除非必要,否則不要請求服務器進行排序。大多數情況下,對于一個適度大小的Recordset對象,當它被發送到客戶端之后,排序速度將更快。另外,如果讓ADO客戶程序排序Recordset中的記錄,則客戶應用程序能夠按照用戶選擇的次序排序,從而提高了靈活性。

在編寫查詢之前了解索引的結構。創建合適的索引,調整查詢的語法以利用這些索引,你將能夠提高記錄提取的速度。Query Analyzer能夠幫助你決定是否有必要添加更多的索引。

不要一次性返回太多的記錄。很多時候,容量太大的記錄集會嚴重地影響應用程序的性能。只返回那些當前你需要的記錄,如果客戶程序需要更多的記錄,則以后隨時提取。通過帶有參數的WHERE子句,或者靈活地運用TOP N查詢,限制查詢的范圍。

不要返回太多的列。避免使用SELECT *。SELECT *語句告訴SQL Server返回所有的列,不管實際存在的列有多少。只選擇那些你需要的列,這樣,當有人為表增加了更多的列時,你不會得到大得出奇的結果集。

避免使用游標。如果你必須使用游標,那么不要使用那些所需資源數量超過必要的游標類型。如果沒有必要,不要要求游標提供滾動、更新和數據緩沖能力。

詳細地告訴ADO你想要它做些什么。打開Recordset或者構造Command對象時,不要忘了設置CommandType選項。它避免了ADO“猜測”你的意圖,你將能夠減少與服務器的通信,而且使得代碼更加穩定。

另外,學習使用診斷工具,測定運行在服務器上的代碼和應用程序的代碼占用了多少時間——以及這些時間花在哪里。在這方面,SQL Server Profiler是一個寶貴的工具。它能夠闡明你的代碼在要求服務器做些什么,能夠在草率構造的查詢中或對于錯誤選擇的命令屬性突出顯示。另外,Query Analyzer還能夠用圖示的方式顯示出SQL Server將如何執行查詢,提出改進查詢的建議,幫助你調整查詢。Query Analyzer甚至還能夠執行它提出的建議(例如,添加或者刪除索引),你只需點擊一下按鈕就可以完成。

四、處理查詢結果

查詢結果記錄發送到客戶端之后,客戶端應用程序可能需要相當可觀的時間去處理結果集。每一種體系(客戶機/服務器,多層體系中的中間層,以及ASP)都為優化這個階段的代碼提供了相應的技術。下面是幾個能夠顯著改善性能的技巧。

我在代碼中看到最多的錯誤之一是:在引用Recordset Field.Value的時候,使用延遲綁定(Late Binding)。由于代碼需要頻繁地引用Value屬性,而且通常要引用的Field對象有很多,本文前面提到的倍數因子將起到重要影響——因此,所有這里介紹的技巧能夠顯著地改善性能。一些開發者使用延遲綁定技術的原因在于,他們想要明確地標識出SELECT語句選擇了哪些行。為了這個目標,許多人使用了用引號包圍字符串的做法。例如,為了引用記錄集RS字段集合中的“Cows”字段,你可能使用:
RS("Cows")
或者:
RS.Fields("Cows").Value

后面這種方法顯式地引用了記錄集內Fields集合中指定成員的Value屬性。這種方法要稍微快一點,而且當你把這些代碼向Visual Basic.NET遷移時,它的向上兼容性也要好一些。上述方法的一種變化是使用感嘆號(!)操作符:
RS!Cows

與先行綁定(Early-Binding)相比,采用上述方法時COM進行解析的時間要長得多,這是因為它們強制COM在運行時(而不是在編譯時)解析對Value屬性的引用,每一次對該對象的引用都要求有一系列類似的、后臺進行的查找過程。

然而,使用延遲綁定時,不存在代碼引用的是哪一個列這類問題。如果你完全按照下列方式編寫代碼:
RS(0) “ 指向第一個列(Fields集合的成員)

這時,COM能夠在編譯時解析Value屬性地址,代碼的運行速度將加快。但是,只有那些了解查詢所返回的列以及返回次序的人能夠理解這行代碼到底指向了哪一個列。如果開發者不具備控制查詢數據源的權限(這是很常見的情況),這種方法可能帶來問題。為了確定RS(0)引用了哪一個SELECT列,你必須找出生成該Recordset的是哪一個SELECT語句、搞清楚SELECT語句所返回的各個列。

然而,有幾種技術允許你既能夠實現快速地運行時引用,同時保證代碼的可讀性。其中一種方法的要求如下:開發者必須創建一個查詢所返回列的枚舉列表。如果查詢被改變,比如返回更多的列,或者列的次序發生變化,開發者必須修改和重新部署枚舉列表。遺憾的是,要保持枚舉列表與查詢的匹配,對于管理者來說是一個有些困難的任務。例如,為了快速、明白地標識出ADO代碼引用的是哪一個列,你可以結合下面的SELECT語句和枚舉列表:
SELECT CatName, CatType, CatSize from Cats Where...
Enum enuCatsQuery
CatName
CatType
CatSize
End Enum

注意,SELECT語句返回的列與枚舉列表中聲明的列完全匹配。此后,當你需要引用Recordset的Fields集合時,可以使用下面的代碼:
StrMyName = Rs(enuCatsQuery.CatName)

按照這種方法,代碼不僅具有較好的可讀性,而且它仍舊是編譯時綁定,代碼的運行速度明顯加快。

然而,要避免延遲綁定,你還可以使用另外一種方法。[email protected]列表服務上一場長時間的討論得出了一種我稱之為預先綁定(Prebinding)的方法,它結合了兩種技術。當你只需引用Field對象一次時,這種技術沒有什么幫助;但在客戶機/服務器應用中,預先綁定方法非常理想。使用這種方法時,你要創建多個獨立的、命名的Field對象,并把這些對象設置為Recordset對象Fields集合中的成員。編寫代碼的時候,你首先要為每一個想要使用的字段創建一個命名的Field對象,例如:
Dim fldName as ADODB.Field
Dim fldType as ADODB.Field
Dim fldSize as ADODB.Field

創建這些Field對象需要一定的開銷。然而,你應該估量一下,這是一次性的開銷,但它卻能夠戲劇性地改善性能。

打開Recordset之后,你只需一次性地把這些命名的Field對象設置為SELECT查詢選擇出來的列:
If fldName is Nothing then
Set fldName = RS!CatName
Set fldType = RS!CatType
Set fldSize = RS!CatSize
End if

你可以在這里用引號包圍字符串的方法引用列,甚至也可以使用感嘆號操作符。由于這里的代碼只運行一次,不論使用什么方法,性能的差異都不大。接下來,當你需要引用Field對象(查詢之后)時,只需使用預先綁定的變量即可:
strName = fldName
strType = fldType
strSize = fldSize

這種預先綁定方法的性能甚至比序數引用方式(例如RS(0))都要好。

五、客戶機/服務器、中間層和ASP策略

在編寫代碼的時候,你還必須考慮到其他一些影響性能的因素。其中一些因素與ADO沒有什么關系——它們與COM有關。Microsoft最近的一份白皮書指出,在Windows 2000 ASP頁面中執行ADO操作(連接,查詢,處理)要比調用COM組件執行同樣的代碼更快。這個結論并不令人奇怪:當我們從VB調用一個外部COM組件(處于當前進程之外的一些代碼),訪問COM組件以及把控制傳遞給它時在后臺進行的操作復雜得出奇,而且速度很慢。雖然我們沒有必要刻意避免調用COM組件去運行ADO代碼。但是,我們不應該簡單地把多個獨立的ADO操作封裝成大量的小型COM組件,然后在需要的時候每次都去調用它們。相反,我們應該盡量把全部邏輯封裝到一個COM組件里面,使得程序只需一次調用,COM就能夠完成大多數(如果不是全部)操作。我相信,你已經發現運行二進制形式的(例如COM組件)ADO代碼要比運行ASP之類的解釋執行代碼要快。因此,你應該尋找一些方法,減少進入COM組件和從COM組件返回所需要的昂貴開銷。

如果你離不開Command對象,或者不能預先綁定Field對象并在必要時重用,那么你應該考慮避免多余的對象創建操作的技術。在這種情況下,把存儲過程作為Connection對象的方法調用有著更重要的意義。另外,用先行綁定的方式引用Field屬性也有助于改善性能。記住操作完成后必須進行的清理工作:關閉Connection和Recordset對象,然后把它們設置成Nothing。

為了讓代碼和代碼編寫者都表現出最好的性能,請記住以下基本規則:利用連接池和異步連接;減少ADO代碼和數據庫服務器通信的次數;選用一種COM-先行綁定技術;除非必要,盡量避免使用代價昂貴的ADO對象,例如Recordset和Command對象;如有可能,用Return Status和Output參數替代記錄集。盡可能地提高查詢的效率,如有可能,不要忘了利用存儲過程。詳細地告訴ADO你想要它做些什么,避免讓ADO猜測你的意圖——顯式地指定ADO CommandType,使用adExecuteNoRecords之類的選項。

對于本文所介紹的所有技巧和其他文章提出的編程忠告,我建議你以審視的目光看待它們。我們所做的工作、所編寫的代碼、所構造的系統,都屬于非常復雜的東西,許多不斷變化的因素影響著它們。理解了這一點之后,如果你對本文所討論的某一項技術感興趣,可以先進行一下試驗。如果它確實有效,那么就正式實現它,然后再進行測試。如果這時它仍舊有效,那么恭喜你。如果它不再有效,你得看看是否違反了應用該技術的必要條件。
体彩7星彩规则 2019股票配资平台排行榜 证券投资学股票分析报告 同花顺股票行情软件 个人小额投资理财产品 炒股软件哪个最好 腾讯上证指数行情中心 股票涨跌有什么规律吗 点石策略 影响股票涨跌的因素 模拟炒股系统 股票配资平台哪个安全靠谱认准大牛时代 股票分析 股票指数投资策略主要包括完全复制法 金牛配资 牛盛配资 涵乔配资