Performance comparison ODP.NET (managed) vs Devart direct

[Tobsel]

Hello,

we use the devart provider many years now in direct mode. Years ago there was no other (good) solution connection to oracle without installing a client. Today oracle has its own managed ODP.NET provider so we made some performance comparison selecting data from a big wide table (270 columns).

The code is the same for Devart and ODP:

var conn = GetConnection();
conn.Open();

var comm = new OracleCommand(iSql);
comm.Connection = conn;

var watch = Stopwatch.StartNew();
OracleDataReader dr = comm.ExecuteReader();
while (dr.Read()) {}
watch.Stop();

conn.Close();

When using this sample devart seems to be faster than ODP. In ODP I now can set the FetchSize of the OracleDataReader which increases the performance dramatically.

var conn = GetConnection();
conn.Open();

var comm = new OracleCommand(iSql);
comm.Connection = conn;

var watch = Stopwatch.StartNew();
OracleDataReader dr = comm.ExecuteReader();
dr.FetchSize = dr.RowSize * 1000;
while (dr.Read()) {}
watch.Stop();

conn.Close();

If I add the bold row above the ODP select is much faster than Devart. Here are the times:

Devart
1 row: 3 ms
35 rows: 7 ms
400 rows: 82 ms
1200 rows: 113 ms
8000 rows: 687 ms

ODP.NET
1 row: 1 ms
35 rows: 2 ms
400 rows: 13 ms
1200 rows: 20 ms
8000 rows: 220 ms

I tried setting the FetchSize at the OracleCommand for Devart but without any changes. Is there anything I can do to get similar performance to the ODP?

Devart: 9.2.172
ODP: 4.121.2.0

[Pinturiccio]

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

[Pinturiccio]

In our tests we have created a table with 270 columns and filled it with data. When testing performance, we have found that dotConnect for Oracle performance in the Direct mode is similar to the Managed ODP.NET performance. However, when the FetchSize property is used in ODP.NET, it has about 10% higher performance in our tests.

Thus, we have reproduced a higher performance of ODP.NET when using the FetchSize property in comparison to dotConnect for Oracle in the Direct mode, but the performance increase isn’t very significant. Maybe your table uses some specific data types or values, which are read much faster in ODP.NET when using the FetchSize property.

Could you send us a DDL script of your table? And, if possible, send us a DML script of your table.

[Tobsel]

Hello,

I sent you a zip file with ddl, dml, version Information of the database and linux, the database properties and the Visual Studio Solution I used.

Setting the FetchSize (dr.FetchSize = dr.RowSize * Convert.ToInt32(1000);) increases performance of ODP far beyond 10%. Even with that smaller table. It is something between 40 and 50 percent.

Thank you very much,
Tobias

[Pinturiccio]

Thank you for the provided data. We have reproduced approximately 30% better performance in ODP.NET in your sample with using the FetchSize property. We will investigate the issue and post here about the results.

[Tobsel]

Hello,
is there anything new?

[Pinturiccio]

We are working on the issue and will post here when we get any results.

[Tobsel]

Hello,

are there any news on this?

[Pinturiccio]

We have studied your sample. Indeed, when we assign 1000 to FetchSize for both ODP.NET and dotConnect for Oracle, ODP.NET is faster. When studying the test project we found out the following:
1. FetchSize - 1000 means 1000 records in dorConnect for Oracle. In ODP.NET it is counted in the following way: dr.RowSize * Convert.ToInt32(iFetchSize). If a row is not completely full, for example, it takes just a half of dr.RowSize, two such rows can be placed in a place for one. As the result, for ODP.NET FetchSize - 1000 means actually more than 1000 rows. Only if all rows contain data of max size (for example strings of max allowed length for the column), FetchSize will correspond the real 1000 rows per round-trip for ODP.NET. Otherwise, dotConnect for Oracle will process 1000 rows per one fetch, and ODP.NET will process more than 1000 rows.

2. We have studied the behaviour of ODP.NET. After retrieving the data it immediately sends them to the buffer. dotConnect for Oracle performs additional data processing. In your example, the DataReader does not actually read data - it just iterates over the rows. If you actually performed reading of the data, dotConnect for Oracle would process them faster. Replace a DataReader with the DataTable in your example and set FetchSize=1000 for both source. In this case dotConnect will be faster.

We came to the conclusion that Devart dotConnect for Oracle in the Direct mode is slower than ODP.NET Managed only when the DataReader iterates over the rows without actual data reading. When the data are actually read, dotConnect is faster.