Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Why does first reader.Read take all time in stead of command.ExecuteReader?

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

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US