外文翻譯---改進(jìn)ado.net性能

1、<p><b>　　附 錄</b></p><p><b>　　1.英文資料</b></p><p>　　Improving ADO.NET Performance</p><p>　　Improving .NET Application performance and scalability</p>

2、;<p>　　Summary: This chapter provides proven strategies to help you design and develop scalable data access solutions. Topics covered include different techniques to pass data across application layers, managing t

3、he database connection pool, optimizing stored procedure calls, reducing dataset serialization cost, techniques for paging through large result sets, managing transactions, handling BLOBS, and much more.</p><p

4、>　　Objectives</p><p>　　Optimize your data access design. </p><p>　　Choose between DataSets and DataReaders. </p><p>　　Run efficient database commands. </p><p>　　Pass

5、 data between layers efficiently. </p><p>　　Perform efficient transactions. </p><p>　　Optimize connection management. </p><p>　　Evaluate the cost of paging through records. </p&g

6、t;<p>　　Evaluate criteria for analyzing data access performance. </p><p>　　Apply performance considerations to binary large object (BLOB) manipulation. </p><p><b>　　Overview</b&g

7、t;</p><p>　　Well-designed data access code and data processing commands are essential elements for application performance and scalability. Typically, the database is a focal point for application load becau

8、se the majority of application requests require data that comes from a database.</p><p>　　This chapter provides proven strategies for designing and implementing data access code for performance and scalabili

9、ty.</p><p>　　How to Use This Chapter</p><p>　　Use this chapter to improve the implementation of your data access code for performance and scalability. To get the most out of this chapter, consid

10、er the following: </p><p>　　Jump to topics or read beginning to end. The main headings in this chapter help you to quickly identify and then locate the topic that interests you. Alternatively, you can read t

11、he chapter beginning to end to gain a thorough appreciation of the issues that affect ADO.NET performance. </p><p>　　Use the checklist. Use "Checklist: ADO.NET Performance" in the "Checklists&

12、quot; section of this guide to quickly view and evaluate the guidelines presented in this chapter. </p><p>　　Use the "Architecture" section of this chapter to understand how ADO.NET works. By under

13、standing the architecture, you can make better design and implementation choices. Understand core ADO.NET components, such as data provider objects and the DataSet object. </p><p>　　Use the "Design Cons

14、iderations" section of this chapter to understand the high-level decisions that will affect implementation choices for ADO.NET code. </p><p>　　Measure your application performance. learn about the key m

15、etrics that you can use to measure application performance. You have to measure application performance so that you can identify and resolve performance issues. </p><p>　　Test your application performance. &

16、quot;Testing .NET Application Performance" to learn how to apply performance testing to your application. You have to apply a coherent testing process and analyze the results. </p><p>　　Tune your applic

17、ation performance. "Tuning .NET Application Performance" to learn how to resolve performance issues that you identify through the use of tuning metrics. </p><p>　　Tune SQL Server. Read Chapter 14,

18、"Improving SQL Server Performance" to ensure that your Microsoft® SQL Server? database is appropriately configured. </p><p>　　Architecture</p><p>　　ADO.NET relies on data provider

19、s to provide access to the underlying data source. Each data provider exposes a set of objects that you use to manage connections, retrieve data, and update data. The core objects are the following: </p><p>

20、　　Connection </p><p><b>　　Command </b></p><p>　　DataReader </p><p>　　DataAdapter </p><p>　　In addition, ADO.NET provides the DataSet object, which provides

21、a disconnected cache of data. The DataSet object does not require a specific type of data source and is not tied to the underlying data source that the data was obtained from.</p><p>　　The basic ADO.NET arch

22、itecture is shown in Figure 12.1.</p><p>　　Figure 12.1: ADO.NET architecture</p><p>　　The following list outlines the purpose of each of the main ADO.NET objects: </p><p>　　Connecti

23、on. This object represents a connection to a database. </p><p>　　Command. This object represents an SQL statement that is run while connected to a data source. This object can be a stored procedure or a dire

24、ct SQL statement. </p><p>　　DataReader. This object retrieves a read-only, forward-only stream of data from a database. The DataReader object is designed for connected scenarios and offers better performance

25、 than reading data into a DataSet object at the expense of functionality. For more information about how to use DataReader objects and DataSet objects, see "DataSet vs. DataReader" later in this chapter. </p

26、><p>　　DataAdapter. This object channels data to and from a DataSet object and the underlying data source. The DataAdapter object also provides enhanced batch update features that were previously associated wit

27、h the ADO Recordset object. </p><p>　　DataSet. The DataSet object represents a disconnected, cached set of data. The DataSet is independent of the provider and is not tied to the underlying data source that

28、might have been used to populate it. DataSet can easily be passed from component to component through the various layers of an application, and it can be serialized as XML. </p><p>　　You should be aware of t

29、he way a DataSet is internally constructed because the DataSet contains a potentially large number of internal objects. This means that a large number of memory allocations are required to construct a typical DataSet. &l

30、t;/p><p>　　A DataSet consists of one or more DataTable objects together with DataRelation objects that maintain table relationship information. Each DataTable contains DataRow objects and DataColumn objects. Co

31、nstraint objects are used to represent a constraint that can be enforced on one or more DataColumn objects. </p><p>　　Note   You can also use typed datasets that derive from the basic DataSet

32、class. Typed datasets provide benefits at build time and at run time. For more information, see "Typed DataSets" later in this chapter.</p><p>　　DataView. Although the DataView object is not shown

33、in Figure 12.1, you can use a DataView to sort and filter data in a DataTable. This capability is often used for data binding. </p><p>　　Abstracting Data Access</p><p>　　ADO.NET is designed arou

34、nd a set of generic interfaces that abstract the underlying data processing functionality. You can use these interfaces directly to abstract your data access layer so that you can minimize the impact of changing the type

35、 of data source that you use. Abstracting data access is extremely helpful when you are designing systems where your customer chooses the database server.</p><p>　　The core interfaces provided by ADO.NET are

36、 found in the System.Data namespace: </p><p>　　IDbConnection. This is an interface for managing database connections. </p><p>　　IDbCommand. This is an interface for running SQL commands. </p&

37、gt;<p>　　IDbTransaction. This is an interface for managing transactions. </p><p>　　IDataReader. This is an interface for reading data returned by a command. </p><p>　　IDataAdapter. This i

38、s an interface for channeling data to and from datasets. </p><p>　　The various provider objects, such as SqlConnection and OleDbConnection, implement these generic ADO.NET data access interfaces. If you deci

39、de to program against the generic interfaces, be aware of the following issues: </p><p>　　There is some small cost associated with a virtual call through an interface. </p><p>　　Certain expanded

40、 functionality is lost when you use the generic interfaces. For example, the ExecuteXmlReader method is implemented by the SqlCommand object but not by the IDbCommand interface. </p><p>　　There is no generic

41、 base exception type, so you must catch provider-specific exception types, such as SqlException, OleDbException, or OdbcException. </p><p>　　When you use the generic interfaces, you cannot take advantage of

42、database-specific types that are defined for the managed providers; for example, you cannot take advantage of SqlDbType in SqlClient and Oracle-specific types in the Oracle provider. Using specific database types is help

43、ful for type checking and parameter binding. </p><p>　　Performance and Scalability Issues</p><p>　　The following is a list of the main issues that can adversely affect the performance and scalab

44、ility of data access in your application. </p><p>　　Inefficient queries. Queries that process and then return more columns or rows than necessary waste processing cycles that could best be used for servicing

45、 other requests. Queries that do not take advantage of indexes may also cause poor performance. </p><p>　　Retrieving too much data. Too much data in your results is usually the result of inefficient queries.

46、 The SELECT * query often causes this problem. You do not usually need to return all the columns in a row. Also, analyze the WHERE clause in your queries to ensure that you are not returning too many rows. Try to make th

47、e WHERE clause as specific as possible to ensure that the least number of rows are returned. </p><p>　　Inefficient or missing indexes. Query efficiency decreases when indexes are missing because a full table

48、 scan must be performed. Also, as your data grows, tables may become fragmented. Failure to periodically rebuild indexes may also result in poor query performance. </p><p>　　Unnecessary round trips. Round tr

49、ips significantly affect performance. They are subject to network latency and to downstream server latency. Many data-driven Web sites heavily access the database for every user request. While connection pooling helps, t

50、he increased network traffic and processing load on the database server can adversely affect performance. Keep round trips to an absolute minimum. </p><p>　　Too many open connections. Connections are an expe

51、nsive and scarce resource, which should be shared between callers by using connection pooling. Opening a connection for each caller limits scalability. To ensure the efficient use of connection pooling, avoid keeping con

52、nections open and avoid varying connection strings. </p><p>　　Failure to release resources. Failing to release resources can prevent them from being reused efficiently. If you fail to close connections befor

53、e the connections fall out of scope, they are not reclaimed until garbage collection occurs for the connection. Failing to release resources can cause serious resource pressure and lead to shortages and timeouts. </p&

54、gt;<p>　　Transaction misuse. If you select the wrong type of transaction management, you may add latency to each operation. Additionally, if you keep transactions active for long periods of time, the active transa

55、ctions may cause resource pressure. Transactions are necessary to ensure the integrity of your data, but you need to ensure that you use the appropriate type of transaction for the shortest duration possible and only whe

56、re necessary. </p><p>　　Overnormalized tables. Overnormalized tables may require excessive joins for simple operations. These additional steps may significantly affect the performance and scalability of your

57、 application, especially as the number of users and requests increases. </p><p><b>　　2.中文翻譯</b></p><p>　　改進(jìn)ADO.NET性能</p><p>　　改進(jìn).NET應(yīng)用性能和可伸縮性</p><p><b&g

58、t;　　摘要：</b></p><p>　　這章提供證明策略幫助你設(shè)計(jì)并且發(fā)展可調(diào)節(jié)的數(shù)據(jù)存取解決辦法。 本文包括不同的技術(shù)來通過數(shù)據(jù)遍布應(yīng)用層， 管理數(shù)據(jù)庫連接工具， 優(yōu)化儲存的過程調(diào)用，降低數(shù)據(jù)集的大量銷費(fèi)，通過大量結(jié)果數(shù)據(jù)集標(biāo)識的技術(shù)，管理傳送，操作BLOBS等。</p><p><b>　　目標(biāo)</b></p><p>　　

59、· 優(yōu)化數(shù)據(jù)存取設(shè)計(jì)</p><p>　　· 在DataSets 和DataReaders之間做出選擇</p><p>　　· 運(yùn)行有效的數(shù)據(jù)庫命令</p><p>　　· 有效地在層之間流通數(shù)據(jù)</p><p><b>　　· 進(jìn)行有效的交易</b></p>

60、<p><b>　　· 優(yōu)化連接管理</b></p><p>　　· 評估記錄標(biāo)識費(fèi)用</p><p>　　· 評估分析數(shù)據(jù)存取性能標(biāo)準(zhǔn)</p><p>　　· BLOB操作方案的執(zhí)行性能</p><p><b>　　概述</b></p&g

61、t;<p>　　精心設(shè)計(jì)的數(shù)據(jù)訪問代碼和數(shù)據(jù)處理命令是應(yīng)用性能和可伸縮性的根本要素。 通常，數(shù)據(jù)庫是一個(gè)由大量應(yīng)用請求需要來自數(shù)據(jù)庫的數(shù)據(jù)導(dǎo)致的應(yīng)用負(fù)載的焦點(diǎn)。</p><p>　　這章為設(shè)計(jì)和實(shí)施數(shù)據(jù)訪問代碼的性能及可伸縮性提供證明策略。 </p><p><b>　　如何使用</b></p><p>　　使用這章可以改進(jìn)數(shù)據(jù)訪

62、問代碼性能及伸縮性的實(shí)施.為了最有效地使用這章，考慮如下內(nèi)容：</p><p>　　· 只讀標(biāo)題或者從開始讀到結(jié)束。 這章主要標(biāo)題幫助你迅速鑒定然后找到使你感興趣的題目。 或者，你可以從頭到尾地讀獲得完整的影響ADO.NET性能問題那一章。</p><p>　　· 使用檢查表。 使用"檢查表：ADO. NET性能"迅速在"檢查表"

63、部分看到和評估人在這幾章內(nèi)提出的那些指南。</p><p>　　· 使用這章的" 結(jié)構(gòu)"部分理解ADO.NET怎樣工作。 通過理解結(jié)構(gòu)，你能更好地設(shè)計(jì)和實(shí)施選擇。 理解ADO.NET核心組成部分，例如數(shù)據(jù)支持對象和數(shù)據(jù)集對象。</p><p>　　· 使用"設(shè)計(jì)方案"來理解那些將影響ADO.NET 代碼選擇的實(shí)施的高級決定<

64、/p><p>　　·度量應(yīng)用性能。"了解你所能使用度量應(yīng)用性能的關(guān)鍵計(jì)量學(xué)。 你必須度量應(yīng)用性能，以便你能鑒定并且解決執(zhí)行問題。 </p><p>　　· 測試你的應(yīng)用性能。，"測試.NET應(yīng)用性能" 獲悉怎樣把性能試驗(yàn)應(yīng)用于你的應(yīng)用程序。 你必須使用一個(gè)前后一致的測試過程并且分析結(jié)果。 </p><p>　　·

65、; 調(diào)試應(yīng)用性能。通過 "協(xié)調(diào).NET應(yīng)用性能" 來了解怎樣通過使用協(xié)調(diào)計(jì)量學(xué)鑒定解決性能問題。</p><p>　　· 調(diào)試SQL Server。讀第14章“提高SQL性能”，確保你的Microsoft® SQL Server 數(shù)據(jù)庫格式正確。</p><p><b>　　結(jié)構(gòu)性</b></p><p>

66、;　　ADO.NET 依賴數(shù)據(jù)支持提供進(jìn)入基礎(chǔ)數(shù)據(jù)源的途徑。 每個(gè)數(shù)據(jù)支持顯示你所使用的管理連接，恢復(fù)數(shù)據(jù)，更新數(shù)據(jù)。 核心對象是如下內(nèi)容：</p><p><b>　　· 連接 </b></p><p><b>　　· 命令</b></p><p><b>　　· 數(shù)據(jù)讀取器 &

67、lt;/b></p><p><b>　　· 數(shù)據(jù)適配器</b></p><p>　　另外，ADO.NET提供DataSet對象，來提供分離的高速數(shù)據(jù)緩存。 DataSet對象不需要一種數(shù)據(jù)源的具體的類型并且不被所獲得的基礎(chǔ)數(shù)據(jù)源數(shù)據(jù)所束縛。</p><p>　　基本的ADO.NET結(jié)構(gòu)如圖12.1 所示。</p>

68、<p>　　圖12.1 ADO. NET結(jié)構(gòu)圖</p><p>　　下列目錄略述每個(gè)主要ADO.NET 物體的對象：</p><p>　　· 連接。 這個(gè)對象描述對數(shù)據(jù)庫的一個(gè)連接。</p><p>　　· 命令。 這個(gè)對象代表當(dāng)連接一個(gè)數(shù)據(jù)源時(shí)被運(yùn)行的一個(gè)SQL語句。 這個(gè)對象可能是一個(gè)儲存的程序或者一個(gè)直接的SQL語句。 <

69、/p><p>　　· 數(shù)據(jù)讀取器。 這個(gè)對象用來恢復(fù)一條來自數(shù)據(jù)庫的數(shù)據(jù)的只讀數(shù)據(jù)流。 與以功能性作為代價(jià)將數(shù)據(jù)讀進(jìn)一個(gè)DataSet 對象相比較，DataReader對象是為</p><p>　　連結(jié)腳本設(shè)計(jì)的并且能提供更好的性能。 </p><p>　　· 數(shù)據(jù)適配器。 來自一個(gè)DataSet對象和基礎(chǔ)的數(shù)據(jù)源的對象信道數(shù)據(jù)。 數(shù)據(jù)適配器對象也提

70、供以前與Recordset對象相關(guān)的改進(jìn)的批處理特征。</p><p>　　· 數(shù)據(jù)集。 數(shù)據(jù)集對象描述一個(gè)分離的，隱藏的數(shù)據(jù)集。 數(shù)據(jù)集不依賴提供者并不被可能依附于它的基礎(chǔ)的數(shù)據(jù)源所束縛。數(shù)據(jù)集能容易地從部件到部件通過各種各樣層來應(yīng)用， 并且它可以被作為XML 連載。 </p><p>　　你應(yīng)該了解數(shù)據(jù)集內(nèi)部建造的方式，因?yàn)閿?shù)據(jù)集包含一個(gè)潛在的大量的內(nèi)部對象。 這表明許多存儲

71、分配程序被要求建造典型的數(shù)據(jù)集。</p><p>　　數(shù)據(jù)集由一個(gè)或更多數(shù)據(jù)表對象以及數(shù)據(jù)關(guān)系對象組成。 每個(gè)數(shù)據(jù)表包含數(shù)據(jù)行w對象和數(shù)據(jù)列對象。 限制對象用來代表可以被在一個(gè)或更多數(shù)據(jù)列對象上實(shí)施的限制條件。</p><p><b>　　注意</b></p><p>　　你也能使用從基本數(shù)據(jù)集派生來的典型數(shù)據(jù)集。 典型數(shù)據(jù)集優(yōu)越性在于構(gòu)造時(shí)間

72、和運(yùn)行時(shí)間。 </p><p>　　· 數(shù)據(jù)視圖。數(shù)據(jù)視圖對象雖然不在12.1 圖顯示，但是你能使用一個(gè)數(shù)據(jù)視圖使數(shù)據(jù)在數(shù)據(jù)表內(nèi)存儲和分類。 這種性能經(jīng)常用于具有約束力的數(shù)據(jù)。</p><p><b>　　編制數(shù)據(jù)存儲</b></p><p>　　ADO.NET被設(shè)計(jì)為用來提取基礎(chǔ)的數(shù)據(jù)處理的功能性的一般的接口。 你能直接使用這些接口

73、提取你的數(shù)據(jù)存取層， 以便能使改變這類型數(shù)據(jù)源的影響減到最小。 當(dāng)你設(shè)計(jì)你的用戶選擇數(shù)據(jù)庫服務(wù)器的系統(tǒng)時(shí)，對編制數(shù)據(jù)存取極其有幫助。</p><p>　　由ADO. NET提供的核心接口在System.Data namespace中：</p><p>　　· IDbConnection。 這是一個(gè)管理數(shù)據(jù)庫連接的接口。</p><p>　　· I

74、DbCommand。 這是運(yùn)行SQL命令的一個(gè)接口。</p><p>　　· IDbTransaction。 這是管理事務(wù)的一個(gè)接口。</p><p>　　· IDataReader。 這一接口適合讀數(shù)據(jù)以一命令返回。</p><p>　　· IDataAdapter。 這是開辟來自數(shù)據(jù)集數(shù)據(jù)的一個(gè)接口。</p><

75、p>　　例如SqlConnection和OleDbConnection這樣的多種支持對象可以實(shí)現(xiàn)一般的ADO.NET數(shù)據(jù)存取接口。 如果你決定運(yùn)行一般的接口，必須了解下列問題： </p><p>　　· 有一些小的通過一個(gè)接口與一次虛呼叫相關(guān)的花費(fèi)。 </p><p>　　· 當(dāng)你使用一般的接口時(shí)，注意擴(kuò)展的功能丟失。 例如，ExecuteXmlReader 方法

76、以SqlCommand 對象但不是通過IDbCommand接口來實(shí)現(xiàn)。 </p><p>　　· 沒有一般的基礎(chǔ)的異常類型，因此你必須捕獲具體提供者的異常類型，例如SqlException，OleDbException或者OdbcException。</p><p>　　· 當(dāng)你使用一般的接口時(shí)，你不能使用為被管理的提供者確定的特定數(shù)據(jù)庫的類型； 例如，你不能使用Sql

77、Client中的SqlDbType 和Oracle 提供的具體的Oracle的類型。 使用具體的數(shù)據(jù)庫類型對類型檢查和參數(shù)的約束力有幫助。</p><p><b>　　性能和可伸縮性</b></p><p>　　以下內(nèi)容能從反面影響你的應(yīng)用程序中的性能和數(shù)據(jù)存取的可伸縮性的問題的主要目錄。</p><p>　　· 無效問題。 處理、返

78、回更多的行或列而不是用于處理其他請求服務(wù)的進(jìn)程周期的浪費(fèi)。 不利用可能引起劣質(zhì)性能的索引。 </p><p>　　· 恢復(fù)大量數(shù)據(jù)。結(jié)果中的大量數(shù)據(jù)通常致使效率不高。 SELECT * 經(jīng)常引起這個(gè)問題。 通常你不需要返回一行的所有列。 此外，在提問中進(jìn)行WHERE語句分析確保沒返回太多行。 努力使用WHERE語句盡可能保證最小量的行的返回。 </p><p>　　·

79、無效或丟失的索引。 缺少檢索時(shí)效率降低,因?yàn)楸仨殘?zhí)行一遍完整的表掃描。 此外，隨著數(shù)據(jù)增長，表格可能成為碎片。 周期性地再造索引的失敗可能也導(dǎo)致不良的質(zhì)問性能。</p><p>　　· 不必要的循環(huán)。 循環(huán)相當(dāng)影響性能。 他們受網(wǎng)絡(luò)潛伏影響并且到順流服務(wù)器潛伏。 很多數(shù)據(jù)驅(qū)動網(wǎng)站重復(fù)數(shù)據(jù)庫,適合每個(gè)用戶請求進(jìn)入。 當(dāng)使用連接幫助時(shí),增加的網(wǎng)絡(luò)通信量和處理負(fù)荷能影響數(shù)據(jù)庫服務(wù)器性能。保持循環(huán)盡量的小<

80、;/p><p>　　· 大量開放的連接。 連接是一種昂貴和不足的資源，這應(yīng)該通過使用連接工具使用戶共享。 為每名用戶打開一個(gè)連接限制可伸縮性。 保證連接工具的有效利用，避免保持連接開放并且避免大量的連接字符。 </p><p>　　· 釋放資源失敗。 釋放資源失敗能阻止他們被有效地重新使用。 如果連接在斷開之前就失敗，那么只有連接的垃圾回收發(fā)生時(shí)才被恢復(fù)。 不能釋放資源能引

81、起嚴(yán)重的資源壓力并且導(dǎo)致短缺和超時(shí)。</p><p>　　· 事務(wù)錯(cuò)用。 如果你選擇錯(cuò)誤的事務(wù)管理類型，你可能給每次操作添加隱藏。 另外，如果你長時(shí)間保持事務(wù)，可能引起資源壓力。事務(wù)保證數(shù)據(jù)完整的必要性，但必須保證使用適當(dāng)?shù)念愋腿ヌ幚砟切┛赡艿淖疃痰某掷m(xù)的事務(wù),并且只在必需的地方。 </p><p>　　· Overnormalized表。 Overnormalized