Fine Tuning Datasets and Data Readers

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

Earlier, we established that using ADO with the .NET Framework is fraught with performance pitfalls that result in an order-of-magnitude performance degradation. However, ADO.NET has a few pitfalls of its own that we'll address in this last section of the chapter. For example, the elements within a data reader object can be accessed by:

• Indexer ordinal position dataReaderInstance(0) or dataReaderInstance(8)

• Indexer name dataReaderInstance("EmployeeID") or dataReaderInstance("SSN")

• Using an ordinal with a 'get' method dataReaderInstance.GetValue(0) or dataReaderInstance.GetValue(12)

Which one provides the fastest access? You'd expect providing an ordinal to be faster than a by-name lookup, but is it fast enough that it significantly affects performance? To help in the attempt to find an answer, the following enumeration was created in the code-behind file for the WXFineTune ASP.NET application:

    Enum WXColumns      OrderID      CustomerID      ShipName      ShipAddress      UnitPrice      Discount      MaxColumn    End Enum 

This enumeration maps to the following SQL query, where the column names and positions correspond to values in the enumeration:

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

The beauty of an enumeration value is that it can represent either an ordinal (enumValue) or a name (enumValue.ToString()). We can therefore use the WXColumns enumeration to determine empirically whether there is a performance difference in by-name and by-value lookup.

The form in the WXFineTune application uses RadioButton controls to determine which type of lookup should be performed. The code specific to how a data reader object looks up data is as follows:

    Dim s As String    Dim count As WXColumns    While dataReader.Read()      For count = 0 To WXColumns.MaxColumn        If RadiobuttonDataReaderByName.Checked Then          s = dataReader(count.ToString()).ToString()        ElseIf RadioButtonDataReaderOrd.Checked Then          s = dataReader(CType(count, Integer)).ToString()        Else          s = dataReader.GetValue(CType(count, Integer)).ToString()        End If      Next    End While 

The results of running the WXFineTune application for 100 iterations of the query in conjunction with complete traversal of the data are as follows:

	Execution Time (ms)
Data reader, indexer, ordinal	4,887
Data reader, ordinal, 'get' method	4,786
Data reader, index, by name	17,264
Dataset, indexer, ordinal	2,253
Dataset, indexer, by name	12,608

This table demonstrates quite clearly that by-name lookup incurs a significant overhead when compared to by-ordinal lookup (2,253 seconds for DataSet by-ordinal, compared with 12,608 for DataSet byname).

Last of all, you know that it's possible to execute multiple SQL statements with both dataset and data reader objects. For example, running the following four SELECT statements together would produce four result sets:

    SELECT * FROM Customers; SELECT * FROM [Order Details];    SELECT * FROM Orders;SELECT * FROM Products 

The DataSet object handles multiple result sets by placing each result set in a separate table, which is straightforward enough. However, since a data reader provides forward-only traversal, it must take a different tack. When the data reader's Read() method returns False (meaning that all rows of a result set have been read), the NextResult() method can be called in order to start reading from the next result set. At this stage, the Read() method can again be called to traverse the new result set. An example of this in action is as follows:

    Dim dataReader As SqlDataReader = command.ExecuteReader()    Dim count As Integer    Dim fieldCount = dataReader.FieldCount - 1    Do      While dataReader.Read()        For count = 0 To fieldCount          s = dataReader.GetValue(count).ToString()        Next      End While    Loop While dataReader.NextResult() 

It turns out that a data reader object can traverse multiple result sets from a single command about as quickly as it could traverse the commands if they had been submitted separately. In a situation where SQL Server was deployed on a separate machine, however, there would be an advantage to sending over multiple queries to the database as part of a single command. The comparable tradeoff for a DataSet would be in reduced network handshaking against having memory consumed, because multiple result sets are cached at the same time.