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

Posted on

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.

About these ads

3 thoughts on “IDataReader’s GetInt32() and GetOrdinal(), and performance

    hyipbox said:
    November 12, 2013 at 11:49 pm

    Does getOrdinal really improve performance? I’m getting “Cannot implicitly converty type int to string ” although i’m using it against a column with string values.

      Andy responded:
      November 13, 2013 at 12:29 pm

      GetOrdinal() just returns the orderinal of the column, given the column name. You can improve the performance by calling it outside the loop, as you don’t have to get the column order each time. I think you try to get the value of the data row. you will have to do something like var value = (string)reader[yourordinal];

    tzachs said:
    October 31, 2014 at 6:34 pm

    Did you benchmark this?
    Because casting to int also has a performance impact… The guy in this link did benchmark and got the opposite results, i.e that GetInt32 is faster (but yes, the GetOrdinal should be cached outside of the loop):

    http://blog.maskalik.com/ado-net/data-reader-performance-optimizations/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s