We have an WCF application which uses NHibernate to query data from the database. After installing the application into a new test environment we're facing some performance problems with queries. Our old and new environment use different Oracle-servers but both of the databases have the same data.
We have went through our NHibernate logs and identified the problematic part:
2010-12-02 07:14:22,673 NHibernate.SQL - SELECT this_.CC...
2010-12-02 07:14:22,688 NHibernate.Loader.Loader - processing result set
2010-12-02 07:14:27,235 NHibernate.Loader.Loader - result set row: 0
In this case the query returned one row. But it seems that in our new environment the "processing result set" is taking much longer (5 seconds vs 0.5 seconds) than in our other environment. Is there some way to find out exactly what inside the "processing result set" is taking so long?
Note. Executing the same exact query directly into the DB with Toad doesn't reproduce the problem. With Toad, both database server are equally fast.
We are using DetachedCriteria to create the query and then it is executed like this:
Dim criteria As ICriteria = crit.GetExecutableCriteria(GetSession())
Return New Generic.List(Of T)(criteria.List(Of T))
The version of NHibernate is 2.1.2.4 and we're using ActiveRecord 2.1.0 to create the mappings. Oracle servers are of version 10g.
So in our case we have two environments that have the same version of the application with identical configuration files and are querying against identical databases, but which have different application and oracle servers. In one environment querying through the NHibernate takes around 5.5 seconds and in the another 0.5 seconds. The results are consistent and the same query has been executed around 50 times to both environments.
Is there something in the Oracle configuration which could cause it to misbehave with NHiberate? And is there a way to get more detailed logging out from NHibernate so that the exact problem inside the "processing result set" could be found?
Any advice is greatly appreciated.
We were able to fix our problem by switching the database drivers from Microsoft's to Oracle's ODP.net. Now, both servers are equally fast and even our previously fast server executes the queries much more rapidly. We don't know what setting in our new server made the Microsoft's Oracle driver so slow.
And it seems that Microsoft is nowadays recommending everyone to use something else than their own Oracle-drivers. http://blogs.msdn.com/b/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx