Why does first reader.Read take all time in stead of command.ExecuteReader?

[Roel Schreurs]

Hi All,

I am using either CoreLab.Oracle.dll (2.40.2.0) and ADO.NET to access an Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production server.

In C#, I am using CoreLab/ORANET classes to access the database. Typically, I create a command object of CommandType StoredProcedure and open a reader on it. The Procedure returns a ref cursor.

I am surprised to see that the command.ExecuteReader() takes little time (e.g. 0.1 s) to complete, while the first reader.Read() takes much more time (e.g. 10 s). In fact, if I execute the procedure some other way (SQL editor in Toad), execution time is comparable to the time reader.Read takes.

ExecuteReader would correspond to OPEN and Read would correspond to FECTH INTO .

I do not think that command.ExecuteReader works asynchronously, since putting the thread to Sleep for 10 seconds does not help reader.Read to complete faster.

Does anybody know why this is so?

The point is that I am allowing the user to cancel ExecuteReader, but the user never gets a chance, since it completes immediately.

See below for details.

Thanks in advance.

// Sample code
OracleDataReader reader = null;

OracleConnection conn = new OracleConnection("");
conn.Open();
OracleCommand command = conn.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = ".";

OracleParameter parameter = command.CreateParameter();
parameter.OracleDbType = OracleDbType.RefCursor;
parameter.ParameterName = "";
parameter.Direction = ParameterDirection.Output;
command.Parameters.Add(parameter);

Debug.WriteLine("Starting ExecuteReader at " +
System.DateTime.Now.TimeOfDay);

reader = command.ExecuteReader();

Debug.WriteLine("Finished ExecuteReader at " +
System.DateTime.Now.TimeOfDay);

//Optional Thread.Sleep(10000);

Debug.WriteLine("Starting first Read at " +
System.DateTime.Now.TimeOfDay);

while (reader.Read())
{
Debug.WriteLine("Finished Reading at " +
System.DateTime.Now.TimeOfDay);
}

-- Results, without Sleep:
Starting ExecuteReader at 15:07:58.063
Finished ExecuteReader at 15:07:58.188 0.125
Starting first Read at 15:07:58.203 0.016
Finished Reading at 15:08:07.922 9.719
...

-- Results, with Sleep:
Starting ExecuteReader at 15:04:27.859
Finished ExecuteReader at 15:04:27.984 0.125
Starting first Read at 15:04:38.000 10.016
Finished Reading at 15:04:47.688 9.688
...

[Paul]

We checked your example. ExecuteReader executes statement, reader.Read fetches record from the executed statement indeed. Possibly fetch operation takes a lot of time

[Guest]

Thanks for your reply. I tested with the following procedure and I must agree with your findings. Using that procedure, ExecuteReader takes indeed the number of seconds passed in argument i_Sleep.

PROCEDURE SlowProcedure(i_Sleep IN NUMBER, o_Cur OUT UntypedCurType)
IS
BEGIN
dbms_lock.sleep(i_Sleep);
OPEN o_Cur FOR
SELECT object_name
FROM user_objects
WHERE object_type = 'TABLE';
END;

However, if I add an ORDER BY clause to my original query, I would expect that creating the cursor takes a lot of time and the first fetch little. Unfortunately, my case is rather too complex to show here. I'll try to find another example that shows this behaviour.