Thursday, October 15, 2009

Issue with System.Data.OracleClient and NHibernate when querying big tables and using parameterized queries

Recently I was faced with an interesting problem. We were trying to query a table with millions of records using Parameterized queries. This application used NHibernate and System.Data.OracleClient microsoft driver to talk to Oracle database.

Issue:

Parameterized Queries fired via NHibernate were taking significant amount of time (2 min) to query a table with 2000000 records.

Reason:

After a lot of debugging and trial and error I found that using parameterized queries in NHibernate with the microsoft driver was the reason for the bottleneck. This issue appears only when there is significant amount of data in the table we are querying.

Solutions:

1. Replace the current Microsoft oracle driver with an oracle developed Oracle Data Client Driver. Running the same query using new driver eliminates the performance bottelneck. For this to work you will need to have Oracle Data Access Components (ODAC) installed on the machine from which you intend to fire the query.

2. If for some reason you are unable to change the driver then heres how to fix the problem with the microsoft driver.

Solution in short:

When setting a parameter on the query via NHibernate use "query.SetAnsiString" instead of "query.SetParameter" when querying against VARCHAR columns.

How Did I find it:

  • Created 200000 records in our local database
  • Profiled the existing application to find out which call is taking the most time. It was OracleReader.Read call that was taking 90% of the time.
  • Tried to reproduce the same problem using a sample program which used only the Microsoft Oracle driver (without using NHibernate) and by executing the same query that NHibernate was firing. To my surprise I was not able to reproduce the same problem simply by executing the same parameterized query from this sample program.
  • Then I debugged through the NHibernate code to find out what exactly was going wrong with NHibernate.
  • Then I copied the same steps that NHibernate was performing on Microsoft Oracle driver into our sample application. At this point I could reproduce the problem
  • Further investigation revealed that the "OracleParameter.DBType" value for the parameter was set as "String". If we don't set this value Default is "AnsiString".
  • If we use "AnsiString" to set the parameter's DBType value then the same parameterized query runs fast.
  • Since in my application I was using "query.SetParameter" to set the parameter, NHibernate was setting the DBType to string (which is correct), but because of this the Microsoft Oracle driver is not able to run the query fast.
  • The last step was to use "query.SetAnsiString" instead of "query.SetParameter" via NHibernate and WOW it worked.
Happy NHibernating!

Have some Fun!