CODEWITHSUNDEEP
c#sqloracle-databaseperformanceodp
romatthe
romatthe

Reputation: 1477

First query with ODP.NET is always slow

Note: I considered posting this on DBA Exchange first, but considering this a .NET client issue, I thought it was best to ask here first.

I have two functions that are stored in my Oracle 11g development server, which are called using ODP.NET (using Oracle.ManagedDataAccess as opposed to Oracle.DataAccess).

The two functions are lightning fast in SQL Developer (which makes sense, they are simple queries selecting on only ~20,000 records), but performance (measured with System.Diagnostics.Stopwatch) was less than stellar when fired from my C# app using ODP.Net.

Here are the results: (Ignore 'Conversion time and composing time, they are not part of the query process)

Connecting time - GET_TVM_ALL: 00:00:00.0553501
Query time - GET_TVM_ALL: 00:00:05.3467058
Conversion time: 00:00:07.6508273
Connecting time - GET_TVM_STATUS_ALL_FUNC: 00:00:00.0006773
Query time - GET_TVM_STATUS_ALL_FUNC: 00:00:00.0256008
Conversion time: 00:00:03.7280097
Composing time: 00:00:00.0157274
Total Elapsed: 00:00:16.7796351

An execution time of 5 seconds for GET_TVM_ALL is ridiculously high. Ever more surprising is that the second query is much, much faster. This is strange, as it is without a doubt a more complex query on more than 20x the amount of records.

So I switched them around, and this is the result:

Connecting time - GET_TVM_STATUS_ALL_FUNC: 00:00:00.0573807
Query time - GET_TVM_STATUS_ALL_FUNC: 00:00:05.2981962
Conversion time: 00:00:03.6474905
Connecting time - GET_TVM_ALL: 00:00:00.0007322
Query time - GET_TVM_ALL: 00:00:00.0070785
Conversion time: 00:00:07.2473809
Composing time: 00:00:00.0154049
Total Elapsed: 00:00:16.2268687

As you can see, it seems like the first query is always slow, regardless of its content. To prove this, I made a silly dummy function:

CREATE OR REPLACE FUNCTION GET_DUMMY
RETURN SYS_REFCURSOR
AS
    -- REFCURSOR to return data
    pCursor SYS_REFCURSOR;
    BEGIN
        OPEN pCursor FOR SELECT 1 FROM DUAL;      
        RETURN pCursor;
    END;

Now, calling that from my code, let's have a look:

Connecting time - GET_DUMMY: 00:00:00.0581149
Query time - GET_DUMMY: 00:00:05.4103165
Conversion time: 00:00:00.0005617
Connecting time - GET_TVM_STATUS_ALL_FUNC: 00:00:00.0006580
Query time - GET_TVM_STATUS_ALL_FUNC: 00:00:00.0759243
Conversion time: 00:00:03.7577602
Connecting time - GET_TVM_ALL: 00:00:00.0000489
Query time - GET_TVM_ALL: 00:00:00.0037654
Conversion time: 00:00:07.5071360
Composing time: 00:00:00.0152159
Total Elapsed: 00:00:16.7819147

So this proves it, the very first query I'm running is ALWAYS slow.

Extra info: I'm opening and closing a new connection for every single function I'm calling.

Here's my helper function by the way:

public static List<T> ExecuteFunction<T>(string strConnection, string strFunction, OracleDbType returnType, List<DataOracleParameter> parameterList) where T : new()
{
    Stopwatch watch = new Stopwatch();

    using (OracleConnection objConnection = new OracleConnection(strConnection))
    {

        // Create the command object and set attributes
        OracleCommand objCommand = new OracleCommand(strFunction, objConnection);
        objCommand.CommandType = CommandType.StoredProcedure;

        // Set the return parameter and type
        OracleParameter returnValue = new OracleParameter();
        returnValue.OracleDbType = returnType;
        returnValue.Direction = ParameterDirection.ReturnValue;
        objCommand.Parameters.Add(returnValue);

        // Set additional parameters
        if (parameterList != null && parameterList.Count > 0)
        {
            foreach (DataOracleParameter parameter in parameterList)
            {
                OracleParameter inputValue = new OracleParameter();
                inputValue.ParameterName = parameter.ParameterName;
                inputValue.OracleDbType = parameter.ParameterType;
                inputValue.Value = parameter.ParameterValue;
                inputValue.Direction = ParameterDirection.Input;
                objCommand.Parameters.Add(inputValue);
            }
        }

        // Create a data adapter to use with the data set
        OracleDataAdapter dataAdapter = new OracleDataAdapter(objCommand);

        // Create and fill the dataset
        DataSet dataSet = new DataSet();

        watch.Start();
        dataAdapter.Fill(dataSet);
        watch.Stop();
        Console.WriteLine("Query time - {0}: {1}", strFunction, watch.Elapsed);

        List<T> valueList = dataSet.Tables[0].ToList<T>();

        return valueList;
    }
}

Upvotes: 8

Views: 3408

Answers (3)

Hambone
Hambone

Reputation: 16377

I encountered this today and it reminded me of an issue from a decade ago with Microsoft's Oracle driver. When we used parameters, it was dog slow, but if we converted to literals, it worked as expected. Parameters, I always thought, were a best practice, so this was very confusing to me.

Turns out it was the Microsoft adapter, which even they admitted was garbage. Switching to ODP.net fixed it.

Fast forward to today... I ran into the same phenomenon using Oracle's Managed ODP.net. When I used parameters (AKA the right way), it took FOREVER to even run the query execute.

using (OracleCommand cmd = new OracleCommand(sql, conn))
{
    cmd.Parameters.Add("FROM_DATE", fromDate);
    cmd.Parameters.Add("DISTRIBUTOR_ID", distributorId);

    using (OracleDataReader reader = cmd.ExecuteReader()) // Bottleneck here
    {
    }
}

When I switched to literals (again, a horrible practice), it ran instantly.

sql = sql.Replace(":DISTRIBUTOR_ID", distributorId.ToString())
    .Replace(":FROM_DATE", string.Format("'{0:dd-MMM-yyyy}'", fromDate));

using (OracleCommand cmd = new OracleCommand(sql, conn))
{
    using (OracleDataReader reader = cmd.ExecuteReader())
    {
    }
}

Disappointing... An issue with managed ODP? Fluke? I will not use this as standard practice, but for now, I'm keeping the literals in this particular code. My application controls the values, so it is SQL injection safe.

P.S. I know I should be using Oracle's to_date and explicit parameter declaration.

Upvotes: 1

Sweet.Shi
Sweet.Shi

Reputation: 476

I have same problem,I rename the iis application pool ,Let the name short,It fixed my problem,Although incomprehensible but It works for me

Upvotes: 0

Alessandro Rossi
Alessandro Rossi

Reputation: 2450

At first I'd suggest you to tune FetchSize on the OracleCommand object.

Upvotes: 2

Related Questions