Performance comparison ODP.NET (managed) vs Devart direct

Performance comparison ODP.NET (managed) vs Devart direct

Postby Tobsel » Tue 03 Jan 2017 16:00

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
Tobsel
 
Posts: 9
Joined: Fri 28 May 2010 12:31

Re: Performance comparison ODP.NET (managed) vs Devart direct

Postby Pinturiccio » Fri 06 Jan 2017 11:04

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Re: Performance comparison ODP.NET (managed) vs Devart direct

Postby Pinturiccio » Fri 03 Feb 2017 17:10

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.
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Re: Performance comparison ODP.NET (managed) vs Devart direct

Postby Tobsel » Sat 04 Feb 2017 15:19

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
Tobsel
 
Posts: 9
Joined: Fri 28 May 2010 12:31

Re: Performance comparison ODP.NET (managed) vs Devart direct

Postby Pinturiccio » Tue 07 Feb 2017 11:08

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.
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Re: Performance comparison ODP.NET (managed) vs Devart direct

Postby Tobsel » Thu 23 Mar 2017 10:27

Hello,
is there anything new?
Tobsel
 
Posts: 9
Joined: Fri 28 May 2010 12:31

Re: Performance comparison ODP.NET (managed) vs Devart direct

Postby Pinturiccio » Fri 24 Mar 2017 10:25

We are working on the issue and will post here when we get any results.
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle