Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Poor performance loading data due to IDataReader.GetOrdinal

Description

The issue I’m facing was reported about 4 years ago @ https://forum.hibernate.org/viewtopic.php?f=25&t=989865

The problem is that NHibernate.Type.Nullable.NullSafeGet(IDataReader rs, string[] names, ISessionImplementor session, object owner) uses IDataReader.GetOrdinal for every single value in the ResultSet.

The MSDN page for GetOrdinal http://msdn.microsoft.com/en-us/library/system.data.idatarecord.getordinal.aspx states
“Because ordinal-based lookups are more efficient than named lookups, it is inefficient to call GetOrdinal within a loop. Save time by calling GetOrdinal one time and assigning the results to an integer variable for use within the loop.”

This problem does not manifest itself using the SqlDataProvider because it implements a caching mechanism to eliminate this problem. However, I’m using ODP.NET and its implementation of GetOrdinal is naïve and performs very slowly.

For a record set with 40 columns and 4000 rows, it takes about 3.7 seconds to Hydrate the nHibernate objects. The same database call using DataSets and DataAdapters takes about 0.3 seconds. This is because the DataAdapter does not use GetOrdinal at all.

ADO.NET Provider: ODP.NET 2.112.1.0 with 11g
NET 4.0

Environment

None

Attachments

1

testing discovered

Activity

Oskar Berggren 
November 14, 2012 at 12:01 PM

Mass-closing issues resolved with no fix-version and untouched for 30 days.

cremor 
July 11, 2012 at 7:15 AM

For everyones information:
OracleDataReader.GetOrdinal is only slow if the column names don't match case-sensitive. If it doesn't find a case-sensitive match it does a case-insensitive search by calling String.ToUpper very often (two times per column in the result set until it finds a match). 80% of the execution time of GetOrdinal is then used in String.ToUpper.

Since column names in Oracle are usually in upper case (only when defined in double quotes they are not) it is usually the best to map all column names in upper case when using Oracle.

Greg Persson 
July 9, 2012 at 5:45 PM

Fantastic, you've made my year. Thank you very much.

Alex Zaytsev 
July 9, 2012 at 5:44 PM

> What are the other affects of that configuration?
Only that.

/// A ResultSet delegate, responsible for locally caching the columnName-to-columnIndex
/// resolution that has been found to be inefficient in a few vendor's drivers (i.e., Oracle
/// and Postgres).

> Is there a documentation page for it?
Not sure.

Greg Persson 
July 9, 2012 at 5:35 PM

Thank you! That seems to have fixed this issue What are the other affects of that configuration? Is there a documentation page for it?

Not an Issue

Details

Assignee

Reporter

Components

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created July 9, 2012 at 3:51 PM
Updated November 14, 2012 at 12:01 PM
Resolved July 9, 2012 at 6:33 PM
Who's Looking?