IDataReader’s GetInt32() and GetOrdinal(), and performance

Yesterday, I came across a code that simply pulls data with DataReader. These days, you would usually use NHibernate, Entity Framework, or whatever you like to access data. They hide away unnecessary details from you and you can enjoy easy access to your data, instantly and conveniently.

This is what I found.

var reader = command.ExecuteReader();
while (reader.Read())
{
    var entryId = reader.GetInt32(reader.GetOrdinal("entryId"));
    var entryType = (EntryType)reader.GetInt32(reader.GetOrdinal("Type"));
    ...
}

GetOrdinal() returns the column ordinal, given the name of the column. It is a case-sensitive lookup. GetInt32() gets the value of the specified column as 32-bit integer.

I don’t find this code very charming. You are calling two methods, GetInt32() and GetOrdinal() within a loop, and it’s obviously ineffective.

First, you can replace GetInt32 to (int) casting.

var reader = command.ExecuteReader();
while (reader.Read())
{
    var entryId = (int)reader["entryId"];
    var entryType = (EntryType)reader["Type"];
    ...
}

It looks simpler.
GetInt32 is not very effective as it does a few more checks. Usually, you don’t need to care, but still it makes you feel good to know a more performing option.
Look at the following source of GetInt32

public override int GetInt32(int i)
{
  this.ReadColumn(i);
  return this._data[i].Int32;
}

internal int Int32
{
  get
  {
    this.ThrowIfNull();
    if (SqlBuffer.StorageType.Int32 == this._type)
      return this._value._int32;
    else
      return (int) this.Value;
  }
  ....

Secondly, GetOrdinal().

In the second example, when you do reader[“entryId”], it internally calls GetOrdinal(). So, you are still calling GetOrdinal(). In fact, you can’t avoid calling GetOrdinal() with datareader, as it is the way DataReader retrieve its data.

Look at the source code of GetOrdinal()

public override int GetOrdinal(string name)
{
  SqlStatistics statistics = (SqlStatistics) null;
  try
  {
    statistics = SqlStatistics.StartTimer(this.Statistics);
    if (this._fieldNameLookup == null)
    {
      if (this.MetaData == null)
        throw SQL.InvalidRead();
      this._fieldNameLookup = new FieldNameLookup((IDataRecord) this, this._defaultLCID);
    }
    return this._fieldNameLookup.GetOrdinal(name);
  }
  finally
  {
    SqlStatistics.StopTimer(statistics);
  }
}

It even uses timer while it’s looking the column name!
GetOrdinal is a rather expensive operation, especially in a loop. So, you can take it out of the loop and make the code faster.

var reader = command.ExecuteReader();
int colEntryId = reader.GetOrdinal("entryId");
int colType = reader.GetOrdinal("Type");
while (reader.Read())
{
    var entryId = reader[colEntryId];
    var entryType = (EntryType)reader[colType];
    ...
}

So, you call GetOrdinal() just once and avoid using GetInt32. The code is more performing and looks simpler.
Well, this is an art people are forgetting. Even I don’t usually use DataReader these days, but if you care about performance and good code, knowing it still gives you a geeky joy.

IDataReader’s GetInt32() and GetOrdinal(), and performance