The first request with ODP.NET is always slow

Question

Note At first I considered sending this to DBA Exchange, but given this problem with the .NET client, I thought it was better to ask here first.

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

In SQL Developer, two functions are lightning fast (it makes sense, simple queries are only selected for ~ 20,000 records), but the performance (measured using System.Diagnostics.Stopwatch) was less than stellar when launched from my C # application using ODP.Net.

Here are the results: (Ignore "Conversion time and build time, they are not part of the request 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 

A 5 second runtime for GET_TVM_ALL is ridiculously high. Even more surprisingly, the second request is much, much faster. This is strange, as it is without a doubt a more complex query for more than 20x the number of records.

So, I switched them, 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, the first request is always slow, regardless of its contents. To prove this, I made a stupid 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 this from my code, let's see:

 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 request that I run is ALWAYS slow.

Additional information . I open and close a new connection for each individual function that I call.

Here is my helper function:

 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; } } 

Answer 1

First, I suggest you configure FetchSize on an OracleCommand object.

Answer 2

I have the same problem, I rename the iis application pool, Let the name be short, This fixed my problem, Although it is not clear, but it works for me

Answer 3

I ran into this today, and it reminded me of a problem a decade ago with the Microsoft Oracle driver. When we used parameters, it was slow, but if we converted to literals, it worked as expected. Parameters, I always thought, were the best practice, so that really bothered me.

It turns out that it was a Microsoft adapter, which even they recognized as garbage. Switching to ODP.net fixed this.

Fast forward to the future ... I came across the same phenomenon using Oracle Managed ODP.net. When I used the parameters (AKA the right way), it took FOREVER to even run a request for execution.

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

When I switched to literals (again, terrible practice), he immediately ran.

 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 ... Problem with Managed ODP? Fluke? I will not use this as standard practice, but for now I am storing literals in this particular code. My application controls the values, so it is safe for SQL injection.

PS I know that I have to use Oracle to_date and explicitly declare the parameters.