DataReader Name Based Lookups - What's actually happening under the hood that makes them so much slower than ordinal based lookups in loops?

Question

It makes sense that a name-based lookup might be a little slower with multiple matching rules than one that is an integer index. However, I have a hard-time believing that this explains a 3% (or 5-7% in my project) relative cost for each row when we are talking about an average number of 10-15 [column] entries in the data set. It is worth clarifying in the prior statement I am referring to the number of columns that this "dereferencing" occurs against, and not the number of records in the data set. These costs above are in the context that these lookups are occuring once for every row. So they could be occuring a lot.

idr.GetOrdinal(name) // Name based lookup
idr[name]  // Name-based lookup
idr.GetValue(index) // Ordinal-based lookup

Is there actually additional communication with the database needed to do name-based field lookups? What makes them so much slower?

I also noticed for getting column names the following code:

List<string> columnList = new List<string>();
for (int i = 0; i < idr.FieldCount; i++)
{
    columnList.Add(idr.GetName(i));
}

return columnList;

is much faster than an equivalent version using GetSchemaTable, I'm guessing it's for the same reasons.

This question arises in spirit from: Ordinal-Based Lookups vs. Name Based Lookups

I'd be curious if there's a difference between *one* name-based lookup and 100 name-based lookups. In other words, does the first lookup load all sorts of schema data into memory. — Mike Christensen, Apr 29 '13 at 19:39

score 3 · Answer 1 · answered Apr 29 '13 at 19:59

Here's the code for GetOrdinal for SqlDataReader, .NET 4.0, pulled using ILSpy.

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

As you can see, there's no additional database access. FieldNameLookup uses a Hashtable to store the field names, and is cached after it is loaded the first time GetOrdinal is called. So the GetOrdinal calls will add a very small amount of overhead for the lookups done through FieldNameLookup, and the SqlStatistics calls may also add a little bit of overhead (although probably not much).

A good test would be to use a Hashtable to store the indexes and lookup the indexes for each column in that Hashtable for each row. If performance is the same as calling GetOrdinal for each row, then you can safely assume that the overhead is in the FieldNameLookup calls.

Hope that helps.

score 1 · Answer 2 · answered Apr 29 '13 at 19:52

You can lookup the sourcecode for .NET framework objects to see what is happening under-the-hood. From this example:

override public  Int32 GetOrdinal (string name) { 

            ValidateOpen("GetOrdinal");
            ValidateReader(); 
            DataColumn dc = currentDataTable.Columns[name];

            if (dc != null) {
                return dc.Ordinal;// WebData 113248 
            }
            else{ 
                throw ExceptionBuilder.ColumnNotInTheTable(name, currentDataTable.TableName); 
            }
        }

DataReader Name Based Lookups - What's actually happening under the hood that makes them so much slower than ordinal based lookups in loops?

2 Answers2