OracleClient vs ODP.NET Performance Benchmark

In my previous post I mentioned that I was doing some comparisons on the performance of System.Data.OracleClient (provided by Microsoft) versus the ODP.NET (provided by Oracle). Since we still use a lot of DataSets on our project I decided that the simpler example that could be used to measure the performance would be to fill a DataTable using a DataAdapter.

For the experiment I took a table that would fill the DataTable with about 100.000 records. I then created two classes, one for each provider. The classes accessed the same database using the same SQL. Here is the code for the class using the OracleClient provider: 

class MicrosoftProvider
{
    public static void RunTest()
    {
        var conn = new System.Data.OracleClient.OracleConnection(Params.ConnectionString);
        var cmd = new System.Data.OracleClient.OracleCommand(Params.CommandText, conn);
        var adapter = new System.Data.OracleClient.OracleDataAdapter(cmd);
        var dataTable = new DataTable();
        adapter.Fill(dataTable);
    }
}

And here is the class using the ODP.NET provider:

class OracleProvider
{
    public static void RunTest()
    {
        var conn = new Oracle.DataAccess.Client.OracleConnection(Params.ConnectionString);
        var cmd = new Oracle.DataAccess.Client.OracleCommand(Params.CommandText, conn);
        var adapter = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
        var dataTable = new DataTable();
        adapter.Fill(dataTable);
    }
}

I ran the test one after the other:

class Program
{
    static void Main(string[] args)
    {
        OracleProvider.RunTest();
        MicrosoftProvider.RunTest();
    }
}

To compare the execution of each provider I ran this code using the VS 2010 Profiler, which by the way is very cool. The visualization of the tests has improved a lot since VS 2008. Here are the results summary:

 Profiler Summary

 As you can see there is a huge difference! The ODP.NET had less than 10% of the samples taken during processing while more than 90% were taken while running the method with the OracleClient provider. The report will also show you the information in more detail:

 Main method details

And the coolest view of them all will even show the percentage used overlapping the code:

Cool, right? I think so! Ok, now back to our tests... Before wrapping the post there is still one test I could do. I wanted to measure the execution time in seconds of each routine. To do this I used the Stopwatch class.

class Program
{
    static void Main(string[] args)
    {
        Time(() => OracleProvider.RunTest());
        Time(() => MicrosoftProvider.RunTest());
    }

    static void Time(Action action)
    {
        Stopwatch stopwatch = new Stopwatch();
        stopwatch.Start();
        action.Invoke();
        stopwatch.Stop();
        Console.WriteLine(stopwatch.Elapsed);
    }
}

When I ran the code I got the following results:

ODP.NET: 20.6756 seconds

OracleClient: 41.7716 seconds

The conclusion for the article is simple. The ODP.NET provider is way faster than the Oracle provider offered by Microsoft. On top of it all, as I mentioned in my previous post, many of the classes in the System.Data.OracleClient namespace are marked as obsolete. Microsoft no longer is going to keep working on them in the future.

ODP.NET OracleParameter

The past weeks I've been doing a lot of work to improve performance at our application at work. One of the things I've done is to replace the Microsoft's OracleClient for the ODP.NET provider. In my tests I've been getting data access times up to 3 times faster than with the OracleClient. I'll post more details on this in a latter article, for now I wanted to focus on the following error I got when switching providers:

Oracle.DataAccess.Client.OracleException: ORA-01008: not all variables bound

 I found this strange since we have a data layer that abstracts the provider and we already have implementations for Oracle and SQLServer and everything was working great. It took me a while but I found out that this is was only happening in queries where I used the same parameter more than once. Here is an example:

SELECT NAME, AGE FROM EMPLOYEE WHERE :AGE > 18 AND :AGE < 40

 What I found out is that the default behavior for the ODP.NET provider is different than the OracleClient provider. The ODP.NET sets the parameters (OracleParameter) on the OracleCommand using the order of the parameters instead of the name. When this happens you'd have to declare the parameter twice with the same value. This behavior can be changed using the BindByName property on the OracleCommand.

var parameter = new OracleCommand();
parameter.BindByName = true;

The good part for us is that since we had an abstraction layer on top of our data layer all we needed to do was make a small change to the Factory class that creates the commands for us. Everything else remains untouched.

Hope this helps.

Oh, and by the way... on .NET 4 many types under the System.Data.OracleClient namespace is marked as obsolete. So you'd better watch out. 

OCIEnvNlsCreate failed with return code -1

"OCIEnvNlsCreate failed with return code -1 but error message text was not available". This was the error that was really anoying my last week. I've had this problem before on one of our servers and this time it showed up in one of my co-workers machine.

Once again Sysinternals came to the rescue. When I used ProcessMonitor to watch the application running I noticed that it couldn't load some of Oracles dlls. The really strange part was that the dlls it was complaining about were from Oracle 10 and the client we were using were from version 11. I then decide to use ProcessExplorer to checkout out application process in order to find out what dlls were really loaded. Imagine my surprise when I saw that the application had the oci.dll from version oracle client 10 running. At some point someone had put a copy of this dll on the windows/system32 folder, and that was the dll that was being loaded instead of the correct one that was in the Oracle install directory.

After we deleted the incorrect dll our program still had the same error. Turns out that the ORACLE_HOME environment variable was not set either. Once we set the variable and restared IIS it was all fine! So, to sum up:

Error:

OCIEnvNlsCreate failed with return code -1 but error message text was not available

Solution:

 1 - Check if the ORACLE_HOME environment variable is set correctly. Here is how to do it.

 2 - Use Process Explorer to check out what versions of Oracle's dlls are loaded. If it's a different version from the version of the client you have installed, remove these incorrect versions from your machine. They may have been there from a previous client installation.

I hope this will help someone else!

Windows 7 x64 and Oracle Client

I installed my Windows 7 x64 this weekend and so far everything is great. Much better than when I was running on Vista.

I thought I had run into my first problem with 7 when was setting up my Oracle connection. I installed the Oracle Client 11g x64 successfully, I could even connect to the database using SqlPlus. My Visual Studio on the other hand disagreed. When I tried to configure a connection to my Oracle server I got the following error:

Exception: Attempt to load Oracle client libraries threw
BadImageFormatException. This problem will occur when running in 64
bit mode with the 32 bit Oracle client components installed

This was weird, after all I could connect to the database through SqlPlus, right? The problem is that VS2008 is 32 bit and as such it needs a 32 Oracle Client in order to connect to the db. When I installed the 32 bit client VS was able to connect to the db on the spot. So the only problem I had with Windows 7 so far had nothing to do with Windows 7, in fact you would run into the same situation in any other 64 bit version of Windows.