In our .net framework 2.0 based application we were using System.Data.Oracleclient and now migrating to ODP.Net, the volume of the project is too high,
so we cannot do the entire migration on one go, as a result the application is using 2 providers System.Data.Oracleclient & ODP.Net as of now.
Now we are changing our OS, from Windows xp 32bit to Windows 7 64bit. While doing so we observed the following,
1) A query executes in < 1 sec using System.Data.Oracleclient & ODP.Net 10g 64bit (Oracle.DataAccess.dll version 18.104.22.168).
and the same query executes in < 1 sec on Oracle SQL Developer v1.5.
2) However the same query is taking 2-3 mins to execute using System.Data.OracleClient with ODP.Net 11g 64bit (Oracle.DataAccess.dll version 22.214.171.124).
we found a remarkable performance degradation in point 2),
we have to use System.Data.OracleClient with ODP.Net 11g 64bit (Oracle.DataAccess.dll version 126.96.36.199) on Windows 7 64bit OS,
but we cannot live with the performance degradation as mentioned in point 2),
and we cannot convert all code which uses System.Data.OracleClient to ODP.Net very quickly.
So can anyone help us, on why do we see such remarkable performance degradation as mentioned in point 2), and what do we do to resolve this problem.
Does this happen if you run that same query again right after the first one finishes? Some people have issues with ODP having a long "warmup" time, which slows down the first query dramatically (though 3 minutes excessive even for that).
Beyond that, the only other culprit I see is a potential issue going between 32 and 64 bit. If you run the application in 32 bit mode with a 32 bit ODP.net 11 driver on the Win7 system, do you see similar performance issues?
Thanks Tridas for your reply, and sorry could not reply earlier,
The query is the very first query executes on asp.net page load, but three times, with 3 different parameters, one after another,
and observed all of them slow, the datareader.read() method takes time to fetch records from database.
We don't have Windows 7 with 32 bit, so I cannot test right now with ODP.Net 11g 32 bit, do you have any other idea or options, we really need a help on this.
32 bit Oracle clients play nice on Windows 7 64 bit. I'm using one right now. If you're using 32 bit applications, it's necessary to have a 32 bit client for them to work. On a 64 bit system, you can install both 32 and 64 bit clients (in different Oracle homes).
So in order to test it, you need to recompile the application for x86 (so that it runs in 32 bit mode) and put a 32 bit Oracle client on the system. Since it's ASP.net, you'd also need to configure the IIS Application pool (advanced settings on the pool) to enable 32 bit mode. Then try it again.
I'm not sure if that'll solve the problem or not, but I haven't got a better idea right now.
Thanks a ton, this workaround worked fine for me. Now the application is running fine with ODP.Net 11g 32 bit and System.Data.OracleClient.
We are trying to remove entire dependency of System.Data.OracleClient in that case, we can use ODP.Net 64bit easily. Thanks again.