SqlDataReader are these two the same Which one is

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];

Answer 1

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().

Answer 2

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.

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())?