Last Friday I had a short discussion with a colleague on what the 'best' way was to get data out of the DataReader?

There are a number of options:

Use the name of the column:

SqlDataReader dr = cmd.ExecuteReader();

while{dr.Read())
{
  ...
  string columnvalue  = dr["au_lname"].ToString();
  ...
}

The benefit here is the ease in writing the code and the readability.

The second way of approaching the DataReader is using an index:

while{dr.Read())
{
  ...
  string columnvalue  = dr[1].ToString();
  ...
}

This code is a bit harder to read, but is should be faster because there's no need to do a lookup on the column-name.

Once an extra column is added to the query, we'll need to go over all of the indices to confirm we didn't break anything.

To verify that this code is actually faster, I created a small test-program that retrieves data from the pubs-database. The table shows the average time to get records from the authors-table into a variable.

As you can see, it actually turns out to be faster to use an index.

To get the best of both worlds, you can use a combination of both approaches:

const int FIELD_AUID = 0;
const int FIELD_AU_LNAME = 1;
const int FIELD_AU_FNAME = 2;

...

while{dr.Read())
{
  ...
  string columnvalue  = dr[FIELD_AU_LNAME].ToString();
  ...
}

This way changes to the query only require editing of the list of constants.

The performance gain for test-program seems to comparable to changing from the OleDbProvider to SqlClient. So making a small change to the code, has it running about twice as fast.