When I was first learning .NET, I was told that indexing the columns of a SqlDataReader using an int was MUCH faster than using the string indexer. After I got over my initial horror (the string indexer was so much easier to read!), I decided that if this was the way it was, at least I could use an enum to enumerate the columns of a resultset.
However, I recently decided to test this. I wrote the following simple code to test it, using the pubs database. (You can paste this code into a new console app project and it should work if you have pubs on the local default SQL Server instance.)
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace ReaderIndexerTest
{
class
Program
{
static
void Main(string[] args) {
DateTime start = DateTime.Now;
for (int i = 1; i < 1000 ; i++ )
IntIndexer();
DateTime end = DateTime.Now;
TimeSpan duration = end.Subtract(start);
Console.WriteLine(duration.TotalMilliseconds.ToString());
Console.ReadKey();
}
static
string ConnStr {
get {
return
"server=(local);database=pubs;Trusted_Connection=Yes;";
}
}
///
<summary>
/// Use integers to index the columns of a DataReader
///
</summary>
static
void IntIndexer() {
List<Author> list = new
List<Author>();
SqlConnection conn = new
SqlConnection(ConnStr);
conn.Open();
SqlCommand cmd = new
SqlCommand("select * from authors", conn);
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
while (reader.Read()) {
Author a = new
Author();
a.au_id = (string)reader[0];
a.au_lname = (string)reader[1];
a.au_fname = (string)reader[2];
a.phone = (string)reader[3];
a.address = (string)reader[4];
a.city = (string)reader[5];
a.state = (string)reader[6];
a.zip = (string)reader[7];
a.contract = (bool)reader[8];
list.Add(a);
}
reader.Close();
}
}
///
<summary>
/// Use strings to index the columns of a DataReader
///
</summary>
static
void StringIndexer() {
List<Author> list = new
List<Author>();
SqlConnection conn = new
SqlConnection(ConnStr);
conn.Open();
SqlCommand cmd = new
SqlCommand("select * from authors", conn);
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
while (reader.Read()) {
Author a = new
Author();
a.au_id = (string)reader["au_id"];
a.au_lname = (string)reader["au_lname"];
a.au_fname = (string)reader["au_fname"];
a.phone = (string)reader["phone"];
a.address = (string)reader["address"];
a.city = (string)reader["city"];
a.state = (string)reader["state"];
a.zip = (string)reader["zip"];
a.contract = (bool)reader["contract"];
list.Add(a);
}
reader.Close();
}
}
}
///
<summary>
/// Quick and dirty class to hold data to simulate object population
///
</summary>
class
Author
{
public
string au_id;
public
string au_lname;
public
string au_fname;
public
string phone;
public
string address;
public
string city;
public
string state;
public
string zip;
public
bool contract;
// For debugging
public
override
string ToString() {
return au_id + "\t" + au_lname + "\t" + au_fname + "\t" + phone;
}
}
}
To simulate the overhead of actually reading the data, this code calls the database 1000 times, gets the entire contents of the dbo.authors table, and builds a list of objects using the data. In order to switch which indexer method is used, edit the method called by the for loop in Main()—use either IntIndexer() or StringIndexer().
When I ran it, here's what I found on my machine:
In other words, the string indexer was about 3% or so slower than the int indexer, including the query overhead. (I purposely choose a simple query with a reasonable amount of data, so that the database execution time would not dominate the execution time.) Note that there may be other combinations of resultset columns, rows, data, etc that return slightly different results, but this query is similar to the types of resultset I often include in applications. (Well, actually it's about the same amount of data, but it's a much simpler query.)
For me at least, the string indexer is clearly a winner. It is MUCH easier to read, much more resilient to refactorings, and has nearly as good performance. And I've learned my lesson—rather than believing performance folklore, it's important to actually do some simple tests and see if there's a significant difference in performance.
There may be some applications where 3% or so makes a huge difference, but for the types of business applications I usually write, that performance gain is not worth the loss of code readability and maintainability, especially when I have nearly a thousand queries that all need to be maintained. When performance becomes an issue, I'll optimize the problematic section of code (and query), rather than optimize all the readers prematurely.