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());
}
}
}
-------------------------------------------------------------------------------
performance issue using Devart Oracle
-
- Posts: 9
- Joined: Mon 11 Jan 2010 09:46
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
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
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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.
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.
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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.
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.