[ Team LiB ]


Recipe 9.6 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: