performance issue using Devart Oracle

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
mg.ddev.mve
Posts: 9
Joined: Mon 11 Jan 2010 09:46

performance issue using Devart Oracle

Post by mg.ddev.mve » Tue 12 Jan 2010 10:20

Hi,

I'm testing response time using Devart oracle on select queries.

3x Select queries withour parameters
Devart.Data.Oracle.OracleConnection => 25s
System.Data.OracleClient.OracleConnection => 0.003s

Is there anything to do to get better response time ?


Thanks for help

Maxime

Source code :
-------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.OracleClient;
using Devart.Data;
using Devart.Data.Oracle;
using System.Threading;
using System.Diagnostics;

namespace TestsBDD
{
class Program
{
private static System.Data.OracleClient.OracleConnection _ConnectionNativeAvecUnicode = null;
private static Devart.Data.Oracle.OracleConnection _ConnectionDevartAvecUnicode = null;

private static void CreateConnectionDevartAvecUnicode()
{
_ConnectionDevartAvecUnicode = new Devart.Data.Oracle.OracleConnection();
_ConnectionDevartAvecUnicode.Server = "172.16.1.33";
_ConnectionDevartAvecUnicode.Sid = "PROGOSF";
_ConnectionDevartAvecUnicode.Port = 1521;
_ConnectionDevartAvecUnicode.UserId = "TEST";
_ConnectionDevartAvecUnicode.Password = "TEST";
_ConnectionDevartAvecUnicode.Direct = true;
_ConnectionDevartAvecUnicode.ConnectionString += "Pooling=false;";
_ConnectionDevartAvecUnicode.Unicode = true;
}

private static void CreateConnectionNativeAvecUnicode()
{
_ConnectionNativeAvecUnicode = new System.Data.OracleClient.OracleConnection();
_ConnectionNativeAvecUnicode.ConnectionString = "Data Source=PROGOSF;User Id=TEST;Password=TEST;Unicode=true;";
}

static void Main(string[] args)
{
CreateConnectionDevartAvecUnicode();
_ConnectionDevartAvecUnicode.Open();

CreateConnectionNativeAvecUnicode();
_ConnectionNativeAvecUnicode.Open();

TestSelect();

_ConnectionDevartAvecUnicode.Close();
_ConnectionNativeAvecUnicode.Close();

Console.Read();
}

private static void TestSelect()
{
Stopwatch timer = new Stopwatch();
int i = 0;
string sqlselecttable = "SELECT distinct idaction FROM INELEMDOS WHERE NOT EXISTS (SELECT 1 FROM INCPDOS WHERE idaction=INELEMDOS.idaction and iddoss like '0%"+i+"%')";

//////////////////////////////
Console.Write("Connection Devart Avec Unicode - select complexe *3 : ");
Devart.Data.Oracle.OracleCommand _CommandeDevartAvecUnicodeSelectTable = new Devart.Data.Oracle.OracleCommand(sqlselecttable, _ConnectionDevartAvecUnicode);
timer = new Stopwatch();
timer.Start();
for (i = 0; i < 3; i++)
{
_CommandeDevartAvecUnicodeSelectTable.ExecuteNonQuery();
}
timer.Stop();
Console.WriteLine(timer.Elapsed.ToString());


//////////////////////////////
Console.Write("Connection Native Avec Unicode - select complexe *3 : ");
System.Data.OracleClient.OracleCommand _CommandeNativeAvecUnicodeSelectTable = new System.Data.OracleClient.OracleCommand(sqlselecttable, _ConnectionNativeAvecUnicode);
timer = new Stopwatch();
timer.Start();
for (i = 0; i < 3; i++)
{
_CommandeNativeAvecUnicodeSelectTable.ExecuteNonQuery();
}
timer.Stop();
Console.WriteLine(timer.Elapsed.ToString());
}
}
}

-------------------------------------------------------------------------------

mg.ddev.mve
Posts: 9
Joined: Mon 11 Jan 2010 09:46

Post by mg.ddev.mve » Tue 12 Jan 2010 15:15

I did another test :

Instead of using a single Devart.Data.Oracle.OracleCommand to execute my query n time, I tried to create a new Devart.Data.Oracle.OracleCommand before each execution

Previous test :
- Create connection
- Create commande
- Loop x5
--- Execute
- Close connection

New test :
- Create connection
- Loop x5
--- Create commande
--- Execute
- Close connection

Response time is faster when I create a new OracleCommand before each execution !

Results :
- System.Data.OracleClient.OracleConnection, new command => 0.05s
- System.Data.OracleClient.OracleConnection, same command => 0.05s
- Devart.Data.Oracle.OracleConnection, new command => 0.11s
- Devart.Data.Oracle.OracleConnection, same command => 59.1s

Source code
-------------------------------------------------------------------------------
private static void TestSelect()
{
Stopwatch timer = new Stopwatch();
int i = 0;
string sqlselecttable = "SELECT distinct idaction FROM INELEMDOS WHERE NOT EXISTS (SELECT 1 FROM INCPDOS WHERE idaction=INELEMDOS.idaction and iddoss like '0%0%')";

//////////////////////////////
Console.Write("Connection Devart Avec Unicode NewCom - select complexe *5 : ");
Devart.Data.Oracle.OracleCommand _CommandeDevartAvecUnicodeSelectTable;
timer = new Stopwatch();
timer.Start();
for (i = 0; i < 5; i++)
{
sqlselecttable = "SELECT distinct idaction FROM INELEMDOS WHERE NOT EXISTS (SELECT 1 FROM INCPDOS WHERE idaction=INELEMDOS.idaction and iddoss like '0%" + i + "%')";
_CommandeDevartAvecUnicodeSelectTable = new Devart.Data.Oracle.OracleCommand(sqlselecttable, _ConnectionDevartAvecUnicode);
_CommandeDevartAvecUnicodeSelectTable.ExecuteNonQuery();
}
timer.Stop();
Console.WriteLine(timer.Elapsed.ToString());

//////////////////////////////
Console.WriteLine("Connection Devart Avec Unicode ComFix - select complexe *5 : ");
_CommandeDevartAvecUnicodeSelectTable = new Devart.Data.Oracle.OracleCommand(sqlselecttable, _ConnectionDevartAvecUnicode);
timer = new Stopwatch();
timer.Start();
for (i = 0; i < 5; i++)
{
_CommandeDevartAvecUnicodeSelectTable.ExecuteNonQuery();
}
timer.Stop();
Console.WriteLine(timer.Elapsed.ToString());
}

-------------------------------------------------------------------------------

I made another test to get detailed response time for Devart.Data.Oracle.OracleConnection, using the same command each time.

Results : (timer isn't reset between each execution)
exec 1 : 0.04s
exec 2 : 12.98s
exec 3 : 25.71s
exec 4 : 38.40s

First execution is fast, but each other takes 12s !

Do you have the same result ? Is there any way to skip these 12s ?

Source code :
-------------------------------------------------------------------------------
private static void TestSelect()
{
Stopwatch timer = new Stopwatch();
int i = 0;
string sqlselecttable = "SELECT distinct idaction FROM INELEMDOS WHERE NOT EXISTS (SELECT 1 FROM INCPDOS WHERE idaction=INELEMDOS.idaction and iddoss like '0%0%')";
Console.Write("Connection Devart Avec Unicode NewCom - select complexe *5 : ");
Devart.Data.Oracle.OracleCommand _CommandeDevartAvecUnicodeSelectTable;
_CommandeDevartAvecUnicodeSelectTable = new Devart.Data.Oracle.OracleCommand(sqlselecttable, _ConnectionDevartAvecUnicode);
timer = new Stopwatch();
timer.Start();
for (i = 0; i < 5; i++)
{
_CommandeDevartAvecUnicodeSelectTable.ExecuteNonQuery();
Console.WriteLine(timer.Elapsed.ToString());
}
timer.Stop();
Console.WriteLine(timer.Elapsed.ToString());
}

-------------------------------------------------------------------------------

Thanks for help

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 15 Jan 2010 17:19

Thank you for the report, we are investigating the situation. We will notify you about the results.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 22 Jan 2010 14:33

We reproduced the performance issue, however we had execution time about 0.1-0.2 seconds instead of 12-60 seconds. We fixed the problem, look forward to the nearest build. Please check if performance was improved when working with your exact query and database objects.

Note also that ExecuteNonQuery() is not the best method for the select command execution. Please try the ExecuteReader() method which is optimized for select queries.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Tue 09 Feb 2010 14:41

The new 5.55.90 Beta build of dotConnect for Oracle is available for download now. It can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(trial version) or from Registered Users' Area (for users with active subscription only). For more information, please refer to
http://www.devart.com/forums/viewtopic.php?t=17057.

Post Reply