Maximize Performance with SqlDataReader

9. August 2006 19:40

I recently took a closer look to compare the performance variations among some of the possible ways to interact with a SqlDataReader object (ordinal position, column name, etc).  Before you start telling me that I wasn't thorough, I never intended this to be an exhaustive, scientific analysis.  I just wanted to get a general idea of whether there is a measurable difference between some of the approaches.

For the test, I wrote a simple console application that queries for all records in the Orders table of the Northwind database (830 records in my database).  Each field in the table is loaded into local variables for every record in the result set.  I examined three different approaches to using the SqlDataReader.  For each approach, the query and load operation is executed 1000 times to achieve some measurable execution.  It should also be noted that I compiled the application in release mode for .NET 1.1 and .NET 2.0 to compare the two versions.  However, the application and database were both on my local machine.

Again, the only intent here is to get an idea of the general measurable differences.

Ordinal Position
This approach relies on the usage of the indexer property of the class to access a specific field based on its ordinal position in the result set.  It is often presumed to be the fastest approach since the position is already known and doesn't require searching the fields in the result set to obtain the value.  However, the indexer property has a return value of type object.  Therefore, it is usually necessary to cast the object to the desired type (such as string, int, etc).

Here are a couple of excerpts to illustrate this type of usage:

int orderID = ConvertDBValueToInt32(reader[0]);

public int ConvertDBValueToInt32(object value)
{
    return (DBNull.Value != value) ? Convert.ToInt32(value) : 0;
}

Get Methods
This approach involves using the plentiful Get methods of the SqlDataReader class, such as GetInt32, GetString, GetDouble, etc.  These methods do not perform any kind of conversion for you.  The underlying data must already be of the expected type.  In addition, it requires using the ordinal position as a parameter of the Get methods.  I often hear people argue that this is equivalent to the previously described ordinal position approach.

Here are a couple of excerpts to illustrate this type of usage:

int orderID = ConvertDBValueToInt32(reader, 0);

public int ConvertDBValueToInt32(SqlDataReader reader, int index)
{
    return (!reader.IsDBNull(index) ? reader.GetInt32(index) : 0);
}

Column Name
This approach also relies on the usage of the indexer property.  However, it uses the overload that accepts the column name of the field in the result set.  Rather than directly accessing the specific position in the result set, it is necessary for the SqlDataReader to search through the columns to match the names based on a string comparison.  This is obviously going to be more expensive, but I wanted to see if it was measurable. 

It should also be noted that the initial search for a match is case-sensitive.  If a match is not found, a second case-insensitive search is performed.  I tested this scenario as well using all uppercase, lowercase, and mixed case. 

Here are a couple of excerpts to illustrate this type of usage:

int orderID = ConvertDBValueToInt32(reader["OrderID"]);

public int ConvertDBValueToInt32(object value)
{
    return (DBNull.Value != value) ? Convert.ToInt32(value) : 0;
}

Test Results
To try and get a better estimation, I executed each test three times and averaged the results.  Here are the results for each version of .NET by SqlDataReader usage.  The value shown is the execution time (in seconds) for 1000 querying and loading iterations.

 .NET 1.1.NET 2.0
Ordinal Position:8.479.33
Get Methods:11.368.07
Case Sensitive:14.5112.53
Case Insensitive (All Upper):13.9312.23
Case Insensitive (All Lower):14.4712.48
Case Insensitive (Mixed):14.4812.57

As expected, ordinal position performed better than the other categories, but only in version 1.1.  Surprisingly, the get methods surpassed ordinal position in .NET 2.0.  As far as column names are concerned, there is a relatively minor discrepancy between the various cases.  However, I thought it was interesting that some of the case-insensitive variations performed better than the case-sensitive test.

This test was primarily for my own curiousity.  It wasn't necessarily intended to serve as an argument for the "best" way to use a SqlDataReader.  Personally, I prefer to use ordinal position because I had believed it to be the most efficient approach.  However, these test results have me reconsidering whether I will continue to so when working in .NET 2.0 even though it appears to be a minor difference between the get methods and ordinal position on that version of the framework. 

In many situations, you probably wouldn't even notice a visible difference in performance regardless of the manner in which you read data from a SqlDataReader.  However, it is something you should remember when you need to squeeze every bit of performance.  When in doubt, profile your code and measure the difference.  As always, make sure you understand the consequences of how you use the framework.

Comments are closed

About Me

I'm a passionate software developer and advocate of the Microsoft .NET platform.  In my opinion, software development is a craft that necessitates a conscious effort to continually improve your skills rather than falling into the trap of complacency.  I was also awarded as a Microsoft MVP in Connected Systems in 2008, 2009, and 2010.


Can’t code withoutThe best C# & VB.NET refactoring plugin for Visual Studio
Follow jeff_barnes on Twitter

View Jeff Barnes's profile on LinkedIn

 

Shared Items

Disclaimer

Anything you read or see on this site is solely based on my own thoughts.  The material on this site does not necessarily reflect the views of my employer or anyone else.  In other words, I don't speak for anyone other than myself.  So, don't assume I am the official spokesperson for anyone.