SqlDataReader are these two the same Which one is faster

Question

I am working with SqlXml and the stored procedure that returns a xml rather than raw data. How does one actually read the data when returned is a xml and does not know about the column names. I used the below versions and have heard getting data from SqlDataReader through ordinal is faster than through column name. Please advice on which is best and with a valid reason or proof

1. sqlDataReaderInstance.GetString(0);

2. sqlDataReaderInstance[0];

Switchback

@Kangkan i believe method too relies on the indexer, correct me if wrong! – Switchback

Related: datareader-hardcode-ordinals – Gunfire

This link: blog.maskalik.com/ado-net/data-reader-performance-optimizations says "reader.GetString(0)" will be faster. Though I'm skeptical of his timings I think he has a point overall. – Gunfire

Answer 1

and have heard getting data from SqlDataReader through ordinal is faster than through column name

Both your examples are getting data through the index (ordinal), not the column name:

Getting data through the column name:

while(reader.Read())
{
    ...
    var value = reader["MyColumnName"];
    ...
}

is potentially slower than getting data through the index:

int myColumnIndex = reader.GetOrdinal("MyColumnName");
while(reader.Read())
{
    ...
    var value = reader[myColumnIndex];
    ...
}

because the first example must repeatedly find the index corresponding to "MyColumnName". If you have a very large number of rows, the difference might even be noticeable.

In most situations the difference won't be noticeable, so favour readability.

UPDATE

If you are really concerned about performance, an alternative to using ordinals is to use the DbEnumerator class as follows:

foreach(IDataRecord record in new DbEnumerator(reader))
{
    ...
    var value = record["MyColumnName"];
    ...
}

The DbEnumerator class reads the schema once, and maintains an internal HashTable that maps column names to ordinals, which can improve performance.

Charter

Joe, I believe all the major ADO.NET connectors do maintain an internal hashtable for column name to ordinal mapping. I have confirmed for SQLite and MySQL. So "record["string"]" is fairly efficient anyway (I mean even without DbEnumerator class). But I do think foreach-ing the reader like you did does have its benefits. – Gunfire

using the updated solution, I get the following error: foreach statement cannot operate on variables of type System.Data.Common.DbEnumerator' because it does not contain a definition for GetEnumerator' or is inaccessible – Schmaltzy

Answer 2

Compared to the speed of getting data from disk both will be effectively as fast as each other.

The two calls aren't equivalent: the version with an indexer returns an object, whereas GetString() converts the object to a string, throwing an exception if this isn't possible (i.e. the column is DBNull).

So although GetString() might be slightly slower, you'll be casting to a string anyway when you use it.

Given all the above I'd use GetString().

Gavra

+1 @Jeremy i would mark this answer if you provided some kind of profile,proof – Switchback

Answer 3

Indexer method is faster because it returns data in native format and uses ordinal.

Have a look at these threads:

1. Maximize Performance with SqlDataReader
2. .NET SqlDataReader Item[] vs. GetString(GetOrdinal())?

Overrate

does this mean i could also do instance.Item[0] ? – Switchback