SqlDataReader performance differences in accessing values?

Question

When accessing values from an SqlDataReader is there a performance difference between these two:

string key = reader.GetString("Key");

or

string key = reader["Key"].ToString();

In this code sample:

string key;

using (SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        key = reader.GetString("Key");
        // or
        key = reader["Key"].ToString();
    }
}

Answer 1

Even if there is, it's unlikely to be significant in the slightest compared with the cost of getting the data from the database in the first place.

Personally I prefer the first version - it shows that I expect the column to be a string column, rather than taking a number and converting it into a string.

Answer 2

Although your code would need to be modified; using the ordinal is the fastest method.

Answer 3

A Microsoft MVP wrote a blog entry about this: http://jeffbarnes.net/portal/blogs/jeff_barnes/archive/2006/08/09/Maximize-Performance-with-SqlDataReader.aspx

Here are his conclusions:

                                    .NET 1.1 .NET 2.0
Ordinal Position:                   8.47        9.33
Get Methods:                        11.36       8.07
Case Sensitive:                     14.51       12.53
Case Insensitive (All Upper):       13.93 12.23
Case Insensitive (All Lower):       14.47 12.48
Case Insensitive (Mixed):           14.48 12.57

But really, unless you are dealing with a significant number of rows, it isn't worth worrying about.

Answer 4

I’ve just peeked into the .NET source code, and the two access methods are essentially the same. The only difference is that the second does an additional boxing. So the first one corresponds to something like (in case of elementary types):

int key = GetInternalSQLDataAsInt32("Key");

while the second one would be:

int key = (int)(object)GetInternalSQLDataAsInt32("Key");

The GetInternalSQLDataAsInt32(...) function represents the SQL data library machinery of marshalling data from SQL to .NET.

But as pointed above, a more significant difference can be expected between string-based keys and ordinal-based keys.