Improving DataReader Performance with Typed Accessors

Problem

You need to improve performance when accessing data from a DataReader .

Solution

Use DataReader typed accessors to improve performance by eliminating repeated boxing and unboxing of object data to and from .NET Framework data types.

The sample code measures the time to access data in a DataReader using three techniques: typed accessor, column ordinal, and column name . The user specifies the technique by selecting a radio button. To ensure accuracy in each case, the routine reads all data from the DataReader 100 times and measures the total time in ticks , which are 100-nanosecond intervals.

The C# code is shown in Example 9-10.

Example 9-10. File: DataReaderPerformanceForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

// . . . 

Cursor.Current = Cursors.WaitCursor;

int orderId;
String customerId;
int employeeId;
DateTime orderDate;
DateTime requiredDate;
DateTime shippedDate;
int shipVia;
Decimal freight;
String shipName;
String shipAddress;
String shipCity;
String shipRegion;
String shipPostalCode;
String shipCountry;

String sqlText = "SELECT OrderID, CustomerID, EmployeeID, " +
 "OrderDate, RequiredDate, ShippedDate, " +
 "ShipVia, Freight, ShipName, ShipAddress, ShipCity, " +
 "ShipRegion, ShipPostalCode, ShipCountry " +
 "FROM Orders";

// Create the connection and the command.
SqlConnection conn = new SqlConnection(
 ConfigurationSettings.AppSettings["Sql_ConnectString"]);
SqlCommand cmd = new SqlCommand(sqlText, conn);

String accessMethod = typedAccessorRadioButton.Checked ? "Typed accessor":
 ordinalRadioButton.Checked ? "Ordinal":"Column name";
int startTick = 0;
int elapsedTick = 0;

conn.Open( );
for(int i = 1; i < 100; i++)
{
 // Create the DataReader and retrieve all fields for each
 // record in the DataReader according to user request.
 SqlDataReader dr = cmd.ExecuteReader( );

 if (typedAccessorRadioButton.Checked)
 {
 startTick = Environment.TickCount;
 while (dr.Read( ))
 {
 orderId = dr.GetInt32(0);
 if(!dr.IsDBNull(1))
 customerId = dr.GetString(1);
 if(!dr.IsDBNull(2))
 employeeId = dr.GetInt32(2);
 if(!dr.IsDBNull(3))
 orderDate = dr.GetDateTime(3);
 if(!dr.IsDBNull(4))
 requiredDate = dr.GetDateTime(4);
 if(!dr.IsDBNull(5))
 shippedDate = dr.GetDateTime(5);
 if(!dr.IsDBNull(6))
 shipVia = dr.GetInt32(6);
 if(!dr.IsDBNull(7))
 freight = dr.GetDecimal(7);
 if(!dr.IsDBNull(8))
 shipName = dr.GetString(8);
 if(!dr.IsDBNull(9))
 shipAddress = dr.GetString(9);
 if(!dr.IsDBNull(10))
 shipCity = dr.GetString(10);
 if(!dr.IsDBNull(11))
 shipRegion = dr.GetString(11);
 if(!dr.IsDBNull(12))
 shipPostalCode = dr.GetString(12);
 if(!dr.IsDBNull(13))
 shipCountry = dr.GetString(13);
 }
 elapsedTick += Environment.TickCount-startTick;
 }

 if (ordinalRadioButton.Checked)
 {
 startTick = Environment.TickCount;
 while (dr.Read( ))
 {
 if (!dr.IsDBNull(0))
 orderId = Convert.ToInt32(dr[0]);
 if (!dr.IsDBNull(1))
 customerId = Convert.ToString(dr[1]);
 if (!dr.IsDBNull(2))
 employeeId = Convert.ToInt32(dr[2]);
 if (!dr.IsDBNull(3))
 orderDate = Convert.ToDateTime(dr[3]);
 if (!dr.IsDBNull(4))
 requiredDate = Convert.ToDateTime(dr[4]);
 if (!dr.IsDBNull(5))
 shippedDate = Convert.ToDateTime(dr[5]);
 if (!dr.IsDBNull(6))
 shipVia = Convert.ToInt32(dr[6]);
 if (!dr.IsDBNull(7))
 freight = Convert.ToDecimal(dr[7]);
 if (!dr.IsDBNull(8))
 shipName = Convert.ToString(dr[8]);
 if (!dr.IsDBNull(9))
 shipAddress = Convert.ToString(dr[9]);
 if (!dr.IsDBNull(10))
 shipCity = Convert.ToString(dr[10]);
 if (!dr.IsDBNull(11))
 shipRegion = Convert.ToString(dr[11]);
 if (!dr.IsDBNull(12))
 shipPostalCode = Convert.ToString(dr[12]);
 if (!dr.IsDBNull(13))
 shipCountry = Convert.ToString(dr[13]);
 }
 elapsedTick += Environment.TickCount-startTick;
 }

 if (columnNameRadioButton.Checked)
 {
 startTick = Environment.TickCount;
 while (dr.Read( ))
 {
 if (dr["OrderID"] != DBNull.Value)
 orderId = Convert.ToInt32(dr["OrderID"]);
 if (dr["CustomerID"] != DBNull.Value)
 customerId = Convert.ToString(dr["CustomerID"]);
 if (dr["EmployeeID"] != DBNull.Value)
 employeeId = Convert.ToInt32(dr["EmployeeID"]);
 if (dr["OrderDate"] != DBNull.Value)
 orderDate = Convert.ToDateTime(dr["OrderDate"]);
 if (dr["RequiredDate"] != DBNull.Value)
 requiredDate =
 Convert.ToDateTime(dr["RequiredDate"]);
 if (dr["ShippedDate"] != DBNull.Value)
 shippedDate =
 Convert.ToDateTime(dr["ShippedDate"]);
 if (dr["ShipVia"] != DBNull.Value)
 shipVia = Convert.ToInt32(dr["ShipVia"]);
 if (dr["Freight"] != DBNull.Value)
 freight = Convert.ToDecimal(dr["Freight"]);
 if (dr["ShipName"] != DBNull.Value)
 shipName = Convert.ToString(dr["ShipName"]);
 if (dr["ShipAddress"] != DBNull.Value)
 shipAddress = Convert.ToString(dr["ShipAddress"]);
 if (dr["ShipCity"] != DBNull.Value)
 shipCity = Convert.ToString(dr["ShipCity"]);
 if (dr["ShipRegion"] != DBNull.Value)
 shipRegion = Convert.ToString(dr["ShipRegion"]);
 if (dr["ShipPostalCode"] != DBNull.Value)
 shipPostalCode =
 Convert.ToString(dr["ShipPostalCode"]);
 if (dr["ShipCountry"] != DBNull.Value)
 shipCountry = Convert.ToString(dr["ShipCountry"]);
 }
 elapsedTick += Environment.TickCount-startTick;
 }

 dr.Close( );
}

resultTextBox.Text += "Access method: " + accessMethod +
 "; Elapsed time: " + elapsedTick + " ticks." + Environment.NewLine;

conn.Close( );

Cursor.Current = Cursors.Default;

Discussion

You can access the data in a DataReader row using a column name, a column ordinal, or a typed accessor method such as GetInt32( ) and GetString( ) . The typed accessor allows a column value to be accessed in its native data type reducing the amount of type conversion required when retrieving a column value. When the underlying type is known, this reduces the type conversion effort required when retrieving the column value and thereby improves performance. For a list of typed accessor methods for SQL Server and OLE DB data providers, see Recipe 2.8.

Each typed accessor takes a single argument: the zero-based column ordinal of the column for which to retrieve the value. An IndexOutOfRangeException is raised if the ordinal value is not valid. An InvalidCastException is raised if the accessor method specifies an invalid cast. If the column might contain a nonexistent or missing value, call the IsDBNull( ) method prior to calling the typed accessor method to avoid raising an exception in case the column value is equivalent to DBNull .

Executing the solution shows the following relative performance when accessing DataReader column values using the different methods:

  • A column ordinal is about 30% faster than a column name
  • A typed accessor method is about 15% faster than a column ordinal and 40% faster than a column name.

Connecting to Data

Retrieving and Managing Data

Searching and Analyzing Data

Adding and Modifying Data

Copying and Transferring Data

Maintaining Database Integrity

Binding Data to .NET User Interfaces

Working with XML

Optimizing .NET Data Access

Enumerating and Maintaining Database Objects

Appendix A. Converting from C# to VB Syntax



ADO. NET Cookbook
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
Year: 2002
Pages: 222
Authors: Bill Hamilton

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net