Steady performance decrease with ExecuteArray(int)

Steady performance decrease with ExecuteArray(int)

Postby klaus linzner » Thu 18 Jul 2013 08:01

Hello Devart,
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);
                           
                }

            }
        }
    }
}
klaus linzner
 
Posts: 28
Joined: Thu 16 May 2013 09:18

Re: Steady performance decrease with ExecuteArray(int)

Postby Pinturiccio » Fri 19 Jul 2013 11:52

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

Re: Steady performance decrease with ExecuteArray(int)

Postby klaus linzner » Mon 22 Jul 2013 08:00

Thank you. Glad you could reproduce it in your environment too...
klaus linzner
 
Posts: 28
Joined: Thu 16 May 2013 09:18

Re: Steady performance decrease with ExecuteArray(int)

Postby klaus linzner » Wed 07 Aug 2013 07:56

Did you make any progress on this issue and/or do you know when a fix will be released?
klaus linzner
 
Posts: 28
Joined: Thu 16 May 2013 09:18

Re: Steady performance decrease with ExecuteArray(int)

Postby Pinturiccio » Thu 08 Aug 2013 12:54

We are investigating the issue and will notify you about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44

Re: Steady performance decrease with ExecuteArray(int)

Postby klaus linzner » Mon 23 Sep 2013 11:15

Please don't get me wrong - but this bug is now open for more than two months and we've still got no info if/when a bugfix could be released...
klaus linzner
 
Posts: 28
Joined: Thu 16 May 2013 09:18

Re: Steady performance decrease with ExecuteArray(int)

Postby Pinturiccio » Tue 24 Sep 2013 12:32

We have improved the performance of the operation of assigning TIMESTAMP and INTERVAL values to In/InOut parameters and fixed memory leak in this operation. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44

Re: Steady performance decrease with ExecuteArray(int)

Postby Pinturiccio » Thu 14 Nov 2013 13:33

New build of dotConnect for Oracle 8.1.36 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 valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=28334
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle