Compare different data access technologies in ADO. NET (PerformanceCompariso)

PriyaDhawanMicrosoftDeveloperNetworkJanuary2002msdn. microsoft. comen-uslibraryms978388.aspx Overview: compares the performance of different data access technologies in a typical application environment. Applicable to Microsoft. NETFrameworkBeta2 and MicrosoftSQLServer2000. (page 23

Priya Dhawan Microsoft Developer Network January 2002 https://msdn.microsoft.com/en-us/library/ms978388.aspx Overview: compares performance of different data access technologies in typical application environments. Applicable to Microsoft. NET Framework Beta2 and Microsoft SQL Server 2000. (page 23

Priya Dhawan
Microsoft Developer Network

January 2002

Https://msdn.microsoft.com/en-us/library/ms978388.aspx

Summary: The performance of different data access technologies is compared in a typical application environment. Applicable to Microsoft. NET Framework Beta2 and Microsoft SQL Server 2000. (23 pages)

Introduction

The architecture selection of data access mode will affect the performance, scalability, maintainability and ease of use of the program. This article focuses on the performance of these choices. Data access technologies include Microsoft ADO. NET Command, DataReader, DataSet, and XMLReader. Here, Microsoft SQL ServerTM 2000 databases are used to compare the differences between these technologies in some typical application environments. In these comparisons, a series of command operations will be executed on the Customer, Order, and OrderDetail data within a certain user load range.

Test scenario

The performance of any data operation depends on the following factors:

Object Construction and object filling in Data Access bring about great system overhead. For example, using DataSet of ADO. NET for instance and filling operations takes more system overhead than using DataReader or XMLReader for the same operation.

Data access technology imposes different loads on databases. For example, when an application reads data, DataSet and DataReader use different connection methods. Using the data access technology of stored procedures requires less database workload than using dynamic SQL expressions. The Conversion Between Relational Data and XML is similar to the use of server resources.

The number of round-trip accesses to the database data is also a factor, especially when the lock and transaction cross-data round-trip.

The amount of data transmitted over the network is also a key factor. The data displayed in xml format is much larger than that in other formats.

We use some common operations in business applications, such as getting a customer column, querying a customer's related order or inserting an order to compare different data access technologies of ADO. NET. To make the test more reliable, the database loads more than 100,000 lines of customer accounts, 1 million lines of orders (10 orders per customer) and more than 5 million rows of Order details (each order has 5 details ). The data is stored in an SQL Server 2000 database and connected to SQL Server through SQL Server. NET data provider. Some of the methods used here use the XML feature of SQL Server 2000.

GetOrderStatus

The GetOrderStatus method accepts an OrderId and returns an integer that represents the order status.

GetCustomer

The GetCustomer method accepts a CustomerId parameter and returns a row of records about the customer information.

GetCustomers

The getmers MERs method accepts a CustomerId and a parameter that specifies the number of rows you want to read. All rows with a CustomerID greater than the CustomerID passed to the Web service method read the top n rows of data and return.

We perform a paging test in a large number of customer records with different pages. The number of pages of these customer records is 100,500 and 1000, respectively.

GetOrders

The GetOrders method obtains a series of hierarchical orders from the database and their corresponding details. This method accepts an OrderId and a parameter that specifies the number of orders to read. Among all records whose OrderId is greater than the input OrderId, the Top n rows of records are read.

We perform a paging test in a large number of customer records with different pages. The number of pages recorded by these customers is 10 orders (50 details), 50 orders (250 details), and 100 orders (500 details ).

InsertCustomer

The InsertCustomer method accepts a customer data, inserts a customer row into the database, and then returns the CustomerId as an integer.

InsertCustomers

The InsertCustomers method accepts a series of customer class sets and inserts the corresponding customer records of multiple rows into the database.

InsertOrder

The data received by the InsertOrder method contains an order record with multiple detail data, and inserts the corresponding Order and OrderDetails information into the database. The test method is to insert an order header and different details.

Test Tool

Based on our testing purpose, we use Application Center Test (ACT), which is suitable for stress testing on Web servers and analyzes the performance and scalability of Web programs. Web programs include ASP pages and the components they use. To learn more