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

From: Roel Schreurs <schreurs_roel_at_hotmail.com>
Date: 4 Feb 2005 00:53:08 -0800
Message-ID: <321ebdef.0502040053.7d69d391@posting.google.com>

Hi All,

Nobody responded while posting to
microsoft.public.dotnet.framework.adonet, maybe someone here has a clue.

I am using either CoreLab.Oracle.dll (2.40.2.0) or Oracle.DataAccess.dll (9.2.0.2102) 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 <cursor> and Read would correspond to FECTH <cursor> INTO <record>.

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,

Roel Schreurs

// Sample code
OracleDataReader reader = null;

OracleConnection conn = new OracleConnection("<ConnectionString>"); conn.Open();
OracleCommand command = conn.CreateCommand(); command.CommandType = CommandType.StoredProcedure; command.CommandText = "<Package>.<Procedure>";  

OracleParameter parameter = command.CreateParameter();

parameter.OracleDbType = OracleDbType.RefCursor;
parameter.ParameterName = "<CursorVariable>";
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: 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 first Read at 15:08:07.922 9.719

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 first Read    at 15:04:47.688  9.688

Received on Fri Feb 04 2005 - 02:53:08 CST