DataSets, Data Readers, and Recordsets

Chapter 11 - Performance
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

Once a connection to a database has been established, whether it belongs to a connection pool or not, our focus passes to the range of objects available for communicating with the data source. In this section, we'll compare and contrast the choices before us.

The title of this section includes a word that is practically taboo when writing texts about .NET: Recordset. A Recordset is an ADO (rather than an ADO.NET) object, but it still has to be given the proper respect. Right now, there are millions of lines of ADO code out there, and developers are debating the best way to move that code over to .NET. Should the data access portion be rewritten (that is, converted from ADO to ADO.NET) immediately, or should it be left as ADO while upgrading the rest of the application to .NET? When examining the various approaches to data access in .NET, we should also analyze the performance of the Recordset in order to see whether it measures up to the performance of contemporary approaches.

In order better to understand the differences between each style of data access (ADO.NET's data reader and data set objects, and ADO's Recordset), the WXTestSelectPerf application (which we'll look at in the next Try It Out) was created. This application performs a query against the Northwind database:

     SELECT O.OrderID, O.CustomerID, O.ShipName,            O.ShipAddress, OD.UnitPrice, OD.Discount     FROM Orders AS 0 INNER JOIN [Order Details] AS OD ON O.OrderID=OD.OrderID 

The query is performed multiple times, using the following approaches:

• Using an SqlDataReader object

• Using a DataSet object with the .NET SQL data provider and querying the database for each iteration

• Using a DataSet object with the .NET SQL data provider but performing the query only once

• Using an ADO Recordset object and querying the database for each iteration

• Using an ADO Recordset object but performing the query only once

Try It Out - Evaluating Data Reader Performance

Once again, the full version of this application is available for download from the Wrox web site, but it's quite large and fairly repetitive. In this section, rather than sifting through the complete listing, we'll just look at the key areas and then analyze the results.

First, as stated, all five approaches share the same connection string, so that's declared at the top of the class definition in the code-behind file:

    Private _commandText As String = & _                  "SELECT O.OrderID, O.CustomerID, "O.ShipName, " & _                         "O.ShipAddress, OD.UnitPrice, OD.Discount " & _                  "FROM Orders AS O INNER JOIN [Order Details] " & _                  "AS OD ON O.OrderID=OD.OrderID" 

After this declaration come the private methods that test the performance of the various data access technologies. They necessarily vary a little, but by and large they perform the same task. The listing that follows contains the method for testing a SqlDataReader object; the performance values computed (howLongTotal and howLongSQL) are returned as ByRef parameters:

    Private Sub WXTestDataReader(ByVal conn As SqlConnection, _                                 ByRef howLongTotal As TimeSpan, _                                 ByRef howLongSQL As TimeSpan)      Dim startTime As DateTime      Dim executeStartTime As DateTime      Dim command As SqlCommand      Dim dataReader As SqlDataReader      startTime = DateTime.Now      command = New SqlCommand(_commandText, conn)      executeStartTime = DateTime.Now      dataReader = command.ExecuteReader()      howLongSQL = DateTime.Now.Subtract(executeStartTime)      Dim fieldCount As Integer = dataReader.FieldCount - 1      Dim count As Integer      Dim s As String      While dataReader.Read()        For count = 0 To fieldCount          s = dataReader.GetValue(count).ToString()        Next      End While      howLongTotal = DateTime.Now.Subtract(startTime)      dataReader.Close()    End Sub 

The basic idea behind this example (and the methods that test DataSet and Recordset objects) is to query repeatedly using one approach, and then measure performance. When you execute the WXTestSelectPerf application, you'll see something like this:

Based on performing the query one hundred times, the following results were generated, where the time is in milliseconds:

	Non-query time	Time in Query	Total Time
SqlDataReader	3,735	50	3,785
DataSet (re-select)	1,973	5,137	7,010
DataSet (select once)	1,663	170	1,833
Recordset (re-select)	44,364	220	44,584
Recordset (select once)	39,833	280	44,013

The 'winner' here seems to be the ADO.NET DataSet, when we performed the query once and then iterated through the result set (every row, every column) 100 times - but we need to analyze that a little more closely. Why did this approach win?

A DataSet contains the results of a query, which on this occasion just so happens to contain 2,155 rows. The footprint of this DataSet is not particularly taxing for the web server, so it just kept rereading the entire cached set of data. From the point of view of a web application, though, this might not make a great deal of sense - many such applications are stateless, and would not be able to exploit the caching provided by the DataSet. (In fact, the DataSet might be better used as a client-side representation of data.) Also, it's important to recognize that retrieving a query's result set in its entirety, which is the DataSet model, could be prohibitive for queries that generate large result sets.

The second-best approach appears to be to use a data reader object, which provides fast, forward-only row traversal. The idea is to get the data one row at a time and move through it quickly in a single pass - and this is how a great many real-world web applications behave. They request data, traverse it so that it can be displayed, and then return control back to the client.

In third place, DataSet objects are clearly not meant to be set up 100 times (repeating the query 100 times) and then traversed once per query. This approach really calls for use of a data reader. It only makes sense to use DataSet objects in a situation where the cached data can be reused.

The two examples of using an ADO Recordset to access data were included to demonstrate the overhead associated with calling a COM component from .NET. There will be a large number of companies who are tempted to upgrade to .NET, but to leave their data access code in ADO because it simplifies the upgrade. However, you have to remember that ADO is accessed from .NET using a wrapper DLL that translates each ADO call from .NET (a managed call) to an unmanaged call. This overhead adds dozens of instructions per method or property accessed.

We could dwell on the reasons why ADO performance is so much worse than ADO.NET performance, but in the final analysis, it doesn't really matter. The life lesson here is: move to ADO.NET. The data access constructs (datasets and data readers) used by ADO.NET do not permit many of the performance hampering configuration settings supported by ADO. In addition, the ADO.NET programming model is simpler - you have the cached representation of data offered by datasets, or the fast, forward-only access provided by data readers.