Comparing the speed of .NET providers for working with Oracle DB

Some time ago, my colleague and I had a little theoretical dispute about how to select and write data faster: by calling DML commands (select / insert) directly from an external application code or is it better to use stored database procedures? The dispute turned into a practical plane when we joined the team of one project using Oracle DB, and I thought which .NET provider would be better to use for our purposes. It was decided to choose not at random, but focusing on the test results, in which we not only compare providers with each other, but also check different approaches to working with the database.

The first two pages of Google identified the test participants:

1. Oracle ODP.NET
2. Devart dotConnect for Oracle

As you can see, the choice is not rich, but the fewer the options, the easier it is to choose.

The capabilities of the providers are almost identical. One of the advantages of the Devart solution is a little easier work with simple transactions (methods are built into the connection class) and the ability to work without an installed Oracle client (the so-called Direct Mode). Also, DevArt speaks in favor of performance tests, in which dotConnect for Oracle (nee OraDirect) puts competitors on the vanes ( see the results ).
')
Since the basis of the project was to be a server with a substantial (in the future) client load, it was interesting to assess the overhead costs that a particular provider brings with it.

As for the dispute, my colleague argued that there is no particular difference between executing an anonymous script with ten insert'ami and calling a stored procedure, with passing parameters to perform these 10 insert'ov. It is similar with data sampling: there is no difference to make select directly, or to call a function that returns, for example, a ref cursor. I advocated the unequivocal superiority of stored procedures.

Well, we are for the scientific approach! Therefore, we create the structure of tables for testing selects and insert s as close as possible to our realities, write some code for testing and start ...

All tests were divided into two groups:

1. A sample of 4 tables: one master-table and three detail-tables (a total of 22 rows for each iteration). The data is immediately fetched from IDataReader and added to the DataTable for further work.
   
   • Each select is performed by a separate command.
   • A batch procedure is called that takes the primary key of the master table at the input and returns a 4 ref cursor (for each table) at the output.
   
   
2. Writing data to two tables: 1 row in master and 8 rows in detail returning the unique identifier of the entry from the master-table generated by the trigger.
   
   • All 9 insert'ov run sequentially.
   • One command is executed with a pre-generated anonymous PL / SQL block containing all the inserts being executed.
   • A batch function is called, in which the parameters for the master table are transferred as is, and the detail-table parameters are in the form of one-dimensional associative arrays.
   • Uses array binding to repeatedly insert records into a detail table

For dotConnect, we tested both the option of working through an Oracle client and direct access. Statistics for all tables was collected. Before each launch, the tables used for testing the inserts were cleared using the script:

truncate table <detail> drop storage; alter table <detail> modify constraint foreignkey01 disable; truncate table <master> drop storage; alter table <detail> modify constraint foreignkey01 enable; 

Each test was run 5 times, with 100,000 iterations each. Of course there were tests with large and smaller numbers of iterations, but starting from 5,000 the results became very similar ...

Update

The initial results and conclusions turned out to be incorrect due to the fact that ODP.NET was given an advantage because of an annoying error: Commit did not occur after each iteration, but after the entire test, unlike dotConnect, which worked honestly.

Also included in the new results is a insert insertion test using array-binding (thanks to VladVR for the idea).
DotConnect as a whole remained the same slower than ODP.NET: when fetching data from 2% to 11%, when pasting using stored procedures and array binding: from 3% to 19%. But at the same time, it turned out to be faster than ODP.NET when inserting records when sequentially calling insert and using an anonymous PL / SQL script: from 8% to 14%.
Direct Mode dotConnect is good only in one discipline: sequential insertion of records, determining the rest by 7-8%. But since it has a number of limitations, it was not considered as a real option.

The average values ​​obtained as a result of tests for 100,000 iterations are shown in the table below. Time in milliseconds.

Test description	dotConnect OCI Mode		dotConnect Direct Mode		ODP.NET
	ms	%	ms	%	ms	%
Select: sequential execution	167267	111%	194648	129%	150563	100%
Select: batch procedure call	147084	102%	161508	112%	144499	100%
Insert: sequential execution	193374	107%	181218	100%	196228	108%
Insert: calling an anonymous PL / SQL block	126916	100%	128962	102%	144762	114%
Insert: batch function call	83692	119%	94004	133%	70580	100%
Insert: array binding	87258	103%	90308	107%	84406	100%

Following the discussion with GlukKazan , tests without a commit after each transaction were also made. They did not change the general picture - only the relative percentages of the advance have changed. Full test results in Excel file on GitHub

The conclusions have not changed:

• For maximum performance, you should still use ODP.NET.
• Use dotConnect when ODP.NET capabilities are not enough, for example, you really need to call a function in the request that returns a ref cursor for each line.
• Selection of data, in principle, can be done as you like. But if every millisecond really counts, it is more advantageous to call a stored procedure that will return several cursors already prepared for fetching.
• As for inserting data, the best option when using both providers is to call a stored procedure. DotConnect in this case works 19% slower than ODP.NET, which determined its fate. This approach is also convenient because all code, including type declarations, is concentrated in one PL / SQL package, which makes editing and managing versioning easier in the future.

Therefore, we have chosen ODP.NET for our project, and we receive and write data using stored procedures. Well, a colleague had to run for the juice, which we then drank together.

The script for creating and populating schema objects, updated project code (C #, VS.2013) and detailed test results are posted on GitHub

PS: Oracle versions 11.2.0.4, ODP.NET Managed 4.121.2.0, DevArt dotConnect for Oracle Trial 8.4.359.0