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

Ed Prochak · *nix forums Guru Wannabe Joined: 17 Mar 2005 Posts: 271

I've got no answer for you, just an observation:

--- the posted code:

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

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

--- does not correspond to the posted output:
Starting first Read at 15:07:58.203 0.016
Finished first Read at 15:08:07.922 9.719

So maybe you forgot to recompile?

Roel Schreurs · *nix forums beginner Joined: 14 Mar 2005 Posts: 3

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

Google