22

What's the difference between reading a value from an SqlDataReader using this syntax:

Dim reader As SqlClient.SqlDataReader
reader("value").ToString()

OR

Dim reader As SqlClient.SqlDataReader
reader.GetString(reader.GetOrdinal("value"))
TylerH
  • 20,799
  • 66
  • 75
  • 101
Tesseract
  • 1,547
  • 4
  • 15
  • 16

4 Answers4

16

I think that the reason to use GetOrdinal() is so that you can cache the result and re-use it multiple times for performance.

E.g.

Dim reader As SqlClient.SqlDataReader
int valueOrdinal = reader.GetOrdinal("value");
while ( ... )
{
    var value = reader.GetString(valueOrdinal);
}
waterlooalex
  • 13,642
  • 16
  • 78
  • 99
  • 1
    Does anybody have an idea about the performance impact of using GetOrdinal(..) inside row loop compared to for example the actual data retrieval from DB? – Juha Palomäki Jun 05 '13 at 16:12
  • 4
    I have a performamce test for a Web API service that reads about 30-40 records from DB stored proc (with multiple resultsets) and returns Json of approx 8K size. In this test I replaced all GetOrdinal() to static int constants and as a result I got 2% increase of the performance... so does it worth effort to create such kind of cache of column names? - I guess in most scenarious not. Reading data from DB and data serialization to Json in real scenarious will influence performance more, so I think caching of ordinals will be the last thing to do when you already optimized everything else... – Bogdan_Ch Dec 27 '14 at 21:04
  • I may have read wrong, but I believe that using GetOrdinal also parses a row without being reliant on the index of each item because a result may not always be indexed how one would expect. Using GetOrdinal allows accessing a reader's column values without having to know exactly where the index position of the value is. – Anthony Tristan Jul 10 '19 at 14:49
8

GetOrdinal performs a case-sensitive lookup first. If it fails, a second case-insensitive search is made. GetOrdinal is kana-width insensitive.Because ordinal-based lookups are more efficient than named lookups, it is inefficient to call GetOrdinal within a loop. Save time by calling GetOrdinal once and assigning the results to an integer variable for use within the loop.

Source: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getordinal.aspx

unkulunkulu
  • 11,576
  • 2
  • 31
  • 49
joe
  • 34,529
  • 29
  • 100
  • 137
  • 8
    Word for word copy and pasted from http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getordinal.aspx ;-) – jpoh Jul 03 '09 at 13:39
  • 4
    Why not just link to the whole article? His comment is missing lots of content. – Dave Apr 20 '10 at 07:27
  • 8
    Typically you don't simply link to the whole article because it may not be available forever, it is also a lot to wade through. You quote the relevant portion and link to it. – Chet May 18 '16 at 15:29
3

I just want to add that the context of how many records you are expecting plays a big role because if you are returning a single row then performance difference between those two would not be significant. However if you are looping over many rows then using typed accessor is better for performance since it's optimized. So in that case if you need to get best performance by using a column name then call GetOrdinal once, put it into a variable and then use typed accessor with the column ordinal in your loop. This would yield the best performance.

if you are curious about the performance difference check out my blog post

Sergey
  • 3,214
  • 5
  • 34
  • 47
0

Your mileage may vary, but...

The more rows you are fetching, the more of a performance improvement you will see. I like to use column aliases in my SELECT statements, like

select
    physical_column_name as "MyFieldName"

and have written a method, should be self-explanatory, 

public Dictionary<String, Int32> GetOrdinalsByName(DbDataReader reader)

So, then my assignments look like

    public void BindRow(DbDataReader dr)
    {
        TerminationDate = dr.GetDateTime(_columnOrdinals["TerminationDate"]);

Dictionaries execute close to O(1); so, this is a reasonable tradeoff between performance and maintainability.

ChrisG65
  • 59
  • 2