Performance Issue

[adsivlad]

Hello,

We are thinking of using dotConnect for Oracle as an Oracle Provider and replacing OracleClient which is currently used in our application.

The problem is we did a couple of tests on SELECT statement performance only and it seems to be slower.
We carried out the tests several times and it seems to take 50% more than OracleClient/ ODP.NET

I worked exactly like presented here: https://www.devart.com/dotconnect/oracl ... mance.html

But I stopped at part 1.
The SELECT took 303 ms as compared to 189 ms for OracleClient and 206 for ODP.NET
Testing on 10000, 100000, 1000000, 10000000 records gives the same proportion. DevArt is 50% slower than the other 2.

We have dotConnect 8.3.135.0

Can you please explain how your tests give other results?

We use Oracle 11g

Thanks,

[adsivlad]

We have tried out version 8.5.506 of dotConnect Oracle Express and it is even slower.

The SELECT took 786 ms as compared to 189 ms for OracleClient and 206 for ODP.NET.

Can you please send me the code for the test found here https://www.devart.com/dotconnect/oracl ... mance.html so I can try to test the performance?

[adsivlad]

I did a stupid mistake in my tests.
Instead of calling DataReader Read I called NextResult.
Basically only 1 record was fetched from database.

Thanks annyway,

[adsivlad]

Hello,

I am testing the performance of DevArt when inserting records to Oracle database.
I made a test similar to https://www.devart.com/dotconnect/oracl ... mance.html

The code is the following:

using DAC = Devart.Data.Oracle;//DevArt
using OC = System.Data.OracleClient;//OracleClient
using ODP = Oracle.DataAccess.Client;//OPD.NET

private void CompareInsertMultipleConnections(int count)
{
Stopwatch stopWatch = new Stopwatch();

stopWatch.Start();

for (int i = 0; i < count; i++)
{
OC.OracleConnection conn1 = new OC.OracleConnection(ConfigurationManager.ConnectionStrings["Oracle"].ConnectionString);
conn1.Open();
OC.OracleCommand command1 = conn1.CreateCommand();
command1.CommandText = "INSERT INTO Detail (Code,Master,Field1,Field2) VALUES(:Code,:Master,:Field1,:Field2)";
command1.Parameters.Add(":Code", OC.OracleType.Int32);
command1.Parameters.Add(":Master", OC.OracleType.Int32);
command1.Parameters.Add(":Field1", OC.OracleType.VarChar);
command1.Parameters.Add(":Field2", OC.OracleType.VarChar);
command1.Parameters[0].Value = i;
command1.Parameters[1].Value = i;
command1.Parameters[2].Value = "01234567890123456789";
command1.Parameters[3].Value = "01234567890123456789";
command1.ExecuteNonQuery();
command1.Dispose();
conn1.Close();
}

stopWatch.Stop();
Trace.WriteLine(count + " records inserted(multiple connections) OracleClient in " + stopWatch.ElapsedMilliseconds + " ms");
stopWatch = new Stopwatch();
stopWatch.Start();

for (int i = 0; i < count; i++)
{
DAC.OracleConnection conn2 = new DAC.OracleConnection(ConfigurationManager.ConnectionStrings["Oracle"].ConnectionString);
conn2.Open();
DAC.OracleCommand command1 = conn2.CreateCommand();
command1.CommandText = "INSERT INTO Detail (Code,Master,Field1,Field2) VALUES(:Code,:Master,:Field1,:Field2)";
command1.Parameters.Add(":Code", DAC.OracleDbType.Integer);
command1.Parameters.Add(":Master", DAC.OracleDbType.Integer);
command1.Parameters.Add(":Field1", DAC.OracleDbType.VarChar);
command1.Parameters.Add(":Field2", DAC.OracleDbType.VarChar);
command1.Parameters[0].Value = i;
command1.Parameters[1].Value = i;
command1.Parameters[2].Value = "01234567890123456789";
command1.Parameters[3].Value = "01234567890123456789";
command1.ExecuteNonQuery();
command1.Dispose();
conn2.Close();
}

stopWatch.Stop();
Trace.WriteLine(count + " records inserted(multiple connections) DevArt in " + stopWatch.ElapsedMilliseconds + " ms");

stopWatch = new Stopwatch();
stopWatch.Start();

for (int i = 0; i < count; i++)
{
ODP.OracleConnection conn3 = new ODP.OracleConnection(ConfigurationManager.ConnectionStrings["Oracle"].ConnectionString);
conn3.Open();
ODP.OracleCommand command1 = conn3.CreateCommand();
command1.CommandText = "INSERT INTO Detail (Code,Master,Field1,Field2) VALUES(:Code,:Master,:Field1,:Field2)";
command1.Parameters.Add(":Code", ODP.OracleDbType.Int32);
command1.Parameters.Add(":Master", ODP.OracleDbType.Int32);
command1.Parameters.Add(":Field1", ODP.OracleDbType.Varchar2);
command1.Parameters.Add(":Field2", ODP.OracleDbType.Varchar2);
command1.Parameters[0].Value = i;
command1.Parameters[1].Value = i;
command1.Parameters[2].Value = "01234567890123456789";
command1.Parameters[3].Value = "01234567890123456789";
command1.ExecuteNonQuery();
command1.Dispose();
conn3.Close();
}

stopWatch.Stop();
Trace.WriteLine(count + " records inserted(multiple connections) ODP in " + stopWatch.ElapsedMilliseconds + " ms");
}

The results are the following when the method is called with 10000 records:
10000 records inserted(multiple connections) OracleClient in 20100 ms
10000 records inserted(multiple connections) DevArt in 28194 ms
10000 records inserted(multiple connections) ODP in 24269 ms

The performance of DevArt is the poorest.

I have tried calling command.Prepare() before setting values of parameters but the comparison results are the same.

On your site the performance of DevArt is better than the other 2.
Can you tell me what I am doing wrong?

We have dotConnect 8.3.135.0

We use Oracle 11g

Thanks,

[Pinturiccio]

Thank you for the provided code. When studying the code we came to the following conclusions:
1. First connection opening is always longer with dotConnect for Oracle than with ODP.NET. Since we support connections via any Oracle Clients, installed on the computer, and the client is loaded dynamically.

2. If you want to test the performance of inserting data, it's better to take connection opening/closing out of the cycle.

3. For better performance with dotConnect for Oracle, add the Statement Cache Size parameter to the connection string and set it to a value that is larger than zero, for example, 50. For more information, please refer to https://www.devart.com/dotconnect/oracl ... eSize.html

4. When (2) and (3) are applied, the performance is comparable with ODP.NET: it can be slightly higher or lower.

[dumian]

Hi Team,

can you please share the test code for the example from here
https://www.devart.com/dotconnect/oracl ... mance.html

Regards,
Johannes

[Pinturiccio]

Please email us to support at devart dot com