I'm currently building an application that has pretty high db performance requirements. In general, ADO OracleCommand's ExecuteArray is pretty much perferct for the scenario needed.
But it seems that there's a steady performance decrease: Taking close to 50ms for the first execution of 2500 records the performance remains below 100ms for about 30 executions.
After 100 executions it takes more than 200ms, and after 300 executions we hit the 1 second mark.
With Pooling=false it helps to close the connection and reopen it; with pooling set to true the performance decrease goes on.
I tried (pretty much) the same code using Oracle's ODP to check if it's likely a Connectivity Db or driver issue, but its performance stays below 50ms the whole time.
Do you know of any tweaks that should or shouldn't be done?
Best regards, Klaus
PS: Here's a small code sample I used to reproduce this issue without any application overhead. It calls a db StoredProcedure with some params, inside the db the storedprocedure has a null implementation.
Code: Select all
public static void RunPerformanceTest(string connectionString, string traceFile)
{
const int batchSize = 2500;
const string sqlCommandtext = "DEFAULT_SAVE_DELETE";
int[] operationValues = new int[batchSize];
string[] componentValues = new string[batchSize];
string[] keyValues = new string[batchSize];
OracleTimeStamp[] eventTimeValues = new OracleTimeStamp[batchSize];
string[] dataValues = new string[batchSize];
Parallel.For(0, batchSize, i =>
{
operationValues[i] = i;
componentValues[i] = i.ToString();
keyValues[i] = i.ToString();
DateTimeOffset eventTime = DateTimeOffset.Now.AddHours(i);
eventTimeValues[i] = new OracleTimeStamp(eventTime.Date, eventTime.Offset);
dataValues[i] = i.ToString();
});
using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
using (OracleCommand command = connection.CreateCommand())
{
command.CommandText = sqlCommandtext;
command.CommandType = CommandType.StoredProcedure;
command.PassParametersByName = true;
command.Parameters.Add("P_OPERATION", OracleDbType.Integer);
command.Parameters.Add("P_COMPONENT", OracleDbType.NVarChar, 50);
command.Parameters.Add("P_KEY", OracleDbType.Integer, 50);
command.Parameters.Add("P_EVENTTIME", OracleDbType.TimeStampTZ);
command.Parameters.Add("P_DATA", OracleDbType.NVarChar, 100);
int countExecutions = 0;
while (!Program.exit)
{
if (countExecutions%100 == 0)
{
Console.WriteLine("Reopen connection - will improve performance if pooling=false");
connection.Close();
connection.Open();
}
countExecutions++;
using (OracleTransaction transaction = connection.BeginTransaction())
{
command.Transaction = transaction;
command.Parameters["P_OPERATION"].OracleValue = operationValues;
command.Parameters["P_COMPONENT"].OracleValue = componentValues;
command.Parameters["P_KEY"].OracleValue = keyValues;
command.Parameters["P_EVENTTIME"].OracleValue = eventTimeValues;
command.Parameters["P_DATA"].OracleValue = dataValues;
Stopwatch execution = Stopwatch.StartNew();
command.ExecuteArray(batchSize);
execution.Stop();
transaction.Commit();
Console.WriteLine("Execution took {0} ms", execution.ElapsedMilliseconds);
}
}
}
}
}