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:
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:
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.