Closing SqlDataReader takes too long.

  • Thread starter Thread starter Reddy
  • Start date Start date
R

Reddy

Hi,

I am using .NET 1.1 on Win2k and XP and SQLServer 2000. I am using
DataReader to read some data (say 100 rows) using
"SELECT ..... FROM .... WHERE ...."
(not a stored procedure). Since my query is a simple select, I am not
modifying any of the data using the query. In *SOME* of my queries
it is taking significantly long time (2 seconds compared to few milli
seconds) to close the data reader. As suggested in the .NET
documentation I am cancelling the command before closing the reader.
I am getting intermittent exceptions that says
"System.Data.SqlClient.SqlException: Operation cancelled by user."

But I am cancelling my command only after reading all my data.
Why is it throwing this exception at that point? This exception comes
for the first time I run my application where the connection has to
be established with database.

Sometimes it comes up even after the connection is established. It
is totally random.

Can somebody shed some light on this extended delay in closing
readers and cancelling commands.

thanks,
- Reddy

-------------------------------------------------------------------------------
SqlDataReader.Close() help:
------------------------------
"The Close method fills in the values for output parameters,
return values and RecordsAffected, increasing the amount of time
it takes to close a SqlDataReader that was used to process a large
or complicated query. In cases where the return values and the
number of records affected by a query are not significant, the
amount of time it takes to close the SqlDataReader can be reduced
by calling the Cancel method of the associated SqlCommand object
before calling the Close method."


-------------------------------------------------------------------------------

...
dataCommand.CommandText = "SELECT CUSTOMER_NAME, CITY FROM CUSTOMER
WHERE CUSTOMER_ID > @CUSTOMER_ID";
IDbDataParameter param = datacommand.CreateParameter();
param.ParameterName = "@CUSTOMER_ID";
param.ParameterValue = 5;
dataCommand.Parameters.Add(param)
IDataReader reader = dataCommand.ExecuteReader();
while(reader.Read())
{
myArrayList.Add(new CustomerInfo(reader.GetString(0),
reader.GetString(2)));
}
dataCommand.Cancel();
dataCommand.Dispose(); // I tried without this line as weel. No
luck.
reader.Close();
reader = null;
dataCommand = null;

transaction = connection.BeginTransaction(); // <--------
exception thrown here
dataCommand = connection.CreateCommand();
dataCommand.Transaction = transaction;
...
...
...

-------------------------------------------------------------------------------

Exception: System.Data.SqlClient.SqlException
Message: Operation cancelled by user.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, TdsParserState state)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.ThrowAttentionError()
at System.Data.SqlClient.TdsParser.ProcessDone(SqlCommand cmd,
RunBehavior run)
at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand
cmdHandler, SqlDataReader dataStream)
at System.Data.SqlClient.TdsParser.Run(RunBehavior run)
at System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String
sqlBatch, String method)
at System.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel
iso)
at System.Data.SqlClient.SqlConnection.BeginTransaction()
at System.Data.SqlClient.SqlConnection.System.Data.IDbConnection.BeginTransaction()
at (my code line #....)
-------------------------------------------------------------------------------
 
When you close the datareader we will under the covers pull all of the
records from the wire, calling cancel in time can help since it may stop
SqlServer from pumping more data into the wire, but there are no guarantees.
The best thing you can do is to make sure your select is selecting a
moderate number of rows and that you are not selecting any large fields like
text or ntext unless you need it right then and there, you should not be
seeing any problems with 100 rows without large fields.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Reddy said:
Hi,

I am using .NET 1.1 on Win2k and XP and SQLServer 2000. I am using
DataReader to read some data (say 100 rows) using
"SELECT ..... FROM .... WHERE ...."
(not a stored procedure). Since my query is a simple select, I am not
modifying any of the data using the query. In *SOME* of my queries
it is taking significantly long time (2 seconds compared to few milli
seconds) to close the data reader. As suggested in the .NET
documentation I am cancelling the command before closing the reader.
I am getting intermittent exceptions that says
"System.Data.SqlClient.SqlException: Operation cancelled by user."

But I am cancelling my command only after reading all my data.
Why is it throwing this exception at that point? This exception comes
for the first time I run my application where the connection has to
be established with database.

Sometimes it comes up even after the connection is established. It
is totally random.

Can somebody shed some light on this extended delay in closing
readers and cancelling commands.

thanks,
- Reddy

-------------------------------------------------------------------------- -----
SqlDataReader.Close() help:
------------------------------
"The Close method fills in the values for output parameters,
return values and RecordsAffected, increasing the amount of time
it takes to close a SqlDataReader that was used to process a large
or complicated query. In cases where the return values and the
number of records affected by a query are not significant, the
amount of time it takes to close the SqlDataReader can be reduced
by calling the Cancel method of the associated SqlCommand object
before calling the Close method."


-------------------------------------------------------------------------- -----

...
dataCommand.CommandText = "SELECT CUSTOMER_NAME, CITY FROM CUSTOMER
WHERE CUSTOMER_ID > @CUSTOMER_ID";
IDbDataParameter param = datacommand.CreateParameter();
param.ParameterName = "@CUSTOMER_ID";
param.ParameterValue = 5;
dataCommand.Parameters.Add(param)
IDataReader reader = dataCommand.ExecuteReader();
while(reader.Read())
{
myArrayList.Add(new CustomerInfo(reader.GetString(0),
reader.GetString(2)));
}
dataCommand.Cancel();
dataCommand.Dispose(); // I tried without this line as weel. No
luck.
reader.Close();
reader = null;
dataCommand = null;

transaction = connection.BeginTransaction(); // <--------
exception thrown here
dataCommand = connection.CreateCommand();
dataCommand.Transaction = transaction;
...
...
...

-------------------------------------------------------------------------- -----

Exception: System.Data.SqlClient.SqlException
Message: Operation cancelled by user.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, TdsParserState state)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.ThrowAttentionError()
at System.Data.SqlClient.TdsParser.ProcessDone(SqlCommand cmd,
RunBehavior run)
at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand
cmdHandler, SqlDataReader dataStream)
at System.Data.SqlClient.TdsParser.Run(RunBehavior run)
at System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String
sqlBatch, String method)
at System.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel
iso)
at System.Data.SqlClient.SqlConnection.BeginTransaction()
at System.Data.SqlClient.SqlConnection.System.Data.IDbConnection.BeginTransacti
on()
at (my code line #....)
--------------------------------------------------------------------------
Click to expand...
-----
 
We have implemented our own "Paging" using data reader, which would
give me 100 rows at a time.
-------------------------------------------
while(recordCount < (startIndex+numRowsPerPage) && reader.Read())
{
if (recordCount < startIndex)
{
recordCount ++;
continue;
}

..... add data to my ArrayList
}
-------------------------------------------

Since there is no sure way in SQLServer to "Give me rows 1000 to
1100", we are using the above approach.

Why is SqlDataReader pulling in All the data? If I say Close() that
means that "I read enough. STOP PULLING DATA!!". Performance is very
bad if the query is running against some table which has millions of
rows. I think it is very misleading to "Auto fetch *ALL* the data"
without asking for it. It would be nice to have some kind of
"ReadAheadRowCount" parameter in SqlDataReader which would get only so
many rows without asking for it. Oracle has "FetchSize" in
OracleDataReader to do similar thing.

What is the workaround for this?

thanks,
- Reddy

Angel Saenz-Badillos said:
When you close the datareader we will under the covers pull all of the
records from the wire, calling cancel in time can help since it may stop
SqlServer from pumping more data into the wire, but there are no guarantees.
The best thing you can do is to make sure your select is selecting a
moderate number of rows and that you are not selecting any large fields like
text or ntext unless you need it right then and there, you should not be
seeing any problems with 100 rows without large fields.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Reddy said:
Hi,

I am using .NET 1.1 on Win2k and XP and SQLServer 2000. I am using
DataReader to read some data (say 100 rows) using
"SELECT ..... FROM .... WHERE ...."
(not a stored procedure). Since my query is a simple select, I am not
modifying any of the data using the query. In *SOME* of my queries
it is taking significantly long time (2 seconds compared to few milli
seconds) to close the data reader. As suggested in the .NET
documentation I am cancelling the command before closing the reader.
I am getting intermittent exceptions that says
"System.Data.SqlClient.SqlException: Operation cancelled by user."

But I am cancelling my command only after reading all my data.
Why is it throwing this exception at that point? This exception comes
for the first time I run my application where the connection has to
be established with database.

Sometimes it comes up even after the connection is established. It
is totally random.

Can somebody shed some light on this extended delay in closing
readers and cancelling commands.

thanks,
- Reddy

-------------------------------------------------------------------------- -----
SqlDataReader.Close() help:
------------------------------
"The Close method fills in the values for output parameters,
return values and RecordsAffected, increasing the amount of time
it takes to close a SqlDataReader that was used to process a large
or complicated query. In cases where the return values and the
number of records affected by a query are not significant, the
amount of time it takes to close the SqlDataReader can be reduced
by calling the Cancel method of the associated SqlCommand object
before calling the Close method."


-------------------------------------------------------------------------- -----

...
dataCommand.CommandText = "SELECT CUSTOMER_NAME, CITY FROM CUSTOMER
WHERE CUSTOMER_ID > @CUSTOMER_ID";
IDbDataParameter param = datacommand.CreateParameter();
param.ParameterName = "@CUSTOMER_ID";
param.ParameterValue = 5;
dataCommand.Parameters.Add(param)
IDataReader reader = dataCommand.ExecuteReader();
while(reader.Read())
{
myArrayList.Add(new CustomerInfo(reader.GetString(0),
reader.GetString(2)));
}
dataCommand.Cancel();
dataCommand.Dispose(); // I tried without this line as weel. No
luck.
reader.Close();
reader = null;
dataCommand = null;

transaction = connection.BeginTransaction(); // <--------
exception thrown here
dataCommand = connection.CreateCommand();
dataCommand.Transaction = transaction;
...
...
...

-------------------------------------------------------------------------- -----

Exception: System.Data.SqlClient.SqlException
Message: Operation cancelled by user.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, TdsParserState state)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.ThrowAttentionError()
at System.Data.SqlClient.TdsParser.ProcessDone(SqlCommand cmd,
RunBehavior run)
at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand
cmdHandler, SqlDataReader dataStream)
at System.Data.SqlClient.TdsParser.Run(RunBehavior run)
at System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String
sqlBatch, String method)
at System.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel
iso)
at System.Data.SqlClient.SqlConnection.BeginTransaction()
at System.Data.SqlClient.SqlConnection.System.Data.IDbConnection.BeginTransacti
on()
at (my code line #....)
--------------------------------------------------------------------------
Click to expand...
-----
Click to expand...
 
The paging that you have implemented does not have any effect here, what
matters is the SQL statement that you have sent to the Server. If you ask
Sql Server to give you 5k rows it will happily start pushing tds packages
across the wire until it is full, as you read more records from the wire it
will continue sending more records.

When you do a datareader close we will try to clean the wire so that you can
use the connection again, under the covers we will continue reading records
from the wire and throwing them away until it is clean. In some situations
(like when you request 5k rows, hopefully) calling Cancel will stop the
server from pushing data and we will be able to clean the wire faster.

To make this model easier to visualize think of the communication between
the server and the client (TDS) as a two directional queue in which you can
only switch directions when the entire queue is empty. A queue is a
First-In-First-Out collection, everything needs to be read in the order
received and you cannot skip to the queue item that you are interested in.
Each connection has exactly one queue to the server, when it is empty the
client can enter a packet in the queue and it will get pushed to the server,
the Server removes it, processses it and when the queue is empty starts
pushing elements into this queue in the opposite direction. The client
cannot do anything (except cancel which is asynchronous) while the server is
pushing data into this collection, the only thing that it can do is keep
reading. This is for the current release of SqlClient you can't get two
readers on a single command, you cannot execute again on that connection
because the queue is not empty.

Do we believe that this is a limitation with our current design? you bet!
and we are definitelly working to make things better. Is it really annoying
to be asked a question followed by an emphatic response? yes! So I will stop
that now... hopefully you get the idea.

In your post you mention that there is no way to ask Sql Server to "Give me
rows 1000 to 1100", fortunatelly this is not quite true. Sql Server
implements a proprietary TSQL command called TOP that allows you to page
through results, it would work something like this.

to get the first 100 elements you would
Select Top 100 * from mytable order by <primaryKeyField>
then to start paging you would
Select Top 100 * from mytable where <primaryKeyField> > <primaryKey of last
record I read> order by <primaryKeyField>

Not a perfect solution, this is another item that we know needs improvement,
but it will get you where you want to go in most cases.
I hope this helped
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Reddy said:
We have implemented our own "Paging" using data reader, which would
give me 100 rows at a time.
-------------------------------------------
while(recordCount < (startIndex+numRowsPerPage) && reader.Read())
{
if (recordCount < startIndex)
{
recordCount ++;
continue;
}

..... add data to my ArrayList
}
-------------------------------------------

Since there is no sure way in SQLServer to "Give me rows 1000 to
1100", we are using the above approach.

Why is SqlDataReader pulling in All the data? If I say Close() that
means that "I read enough. STOP PULLING DATA!!". Performance is very
bad if the query is running against some table which has millions of
rows. I think it is very misleading to "Auto fetch *ALL* the data"
without asking for it. It would be nice to have some kind of
"ReadAheadRowCount" parameter in SqlDataReader which would get only so
many rows without asking for it. Oracle has "FetchSize" in
OracleDataReader to do similar thing.

What is the workaround for this?

thanks,
- Reddy

"Angel Saenz-Badillos[MS]" <[email protected]> wrote in message
When you close the datareader we will under the covers pull all of the
records from the wire, calling cancel in time can help since it may stop
SqlServer from pumping more data into the wire, but there are no guarantees.
The best thing you can do is to make sure your select is selecting a
moderate number of rows and that you are not selecting any large fields like
text or ntext unless you need it right then and there, you should not be
seeing any problems with 100 rows without large fields.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Reddy said:
Hi,

I am using .NET 1.1 on Win2k and XP and SQLServer 2000. I am using
DataReader to read some data (say 100 rows) using
"SELECT ..... FROM .... WHERE ...."
(not a stored procedure). Since my query is a simple select, I am not
modifying any of the data using the query. In *SOME* of my queries
it is taking significantly long time (2 seconds compared to few milli
seconds) to close the data reader. As suggested in the .NET
documentation I am cancelling the command before closing the reader.
I am getting intermittent exceptions that says
"System.Data.SqlClient.SqlException: Operation cancelled by user."

But I am cancelling my command only after reading all my data.
Why is it throwing this exception at that point? This exception comes
for the first time I run my application where the connection has to
be established with database.

Sometimes it comes up even after the connection is established. It
is totally random.

Can somebody shed some light on this extended delay in closing
readers and cancelling commands.

thanks,
- Reddy
Click to expand...
--------------------------------------------------------------------------
-----
SqlDataReader.Close() help:
------------------------------
"The Close method fills in the values for output parameters,
return values and RecordsAffected, increasing the amount of time
it takes to close a SqlDataReader that was used to process a large
or complicated query. In cases where the return values and the
number of records affected by a query are not significant, the
amount of time it takes to close the SqlDataReader can be reduced
by calling the Cancel method of the associated SqlCommand object
before calling the Close method."
Click to expand...
--------------------------------------------------------------------------
-----

...
dataCommand.CommandText = "SELECT CUSTOMER_NAME, CITY FROM CUSTOMER
WHERE CUSTOMER_ID > @CUSTOMER_ID";
IDbDataParameter param = datacommand.CreateParameter();
param.ParameterName = "@CUSTOMER_ID";
param.ParameterValue = 5;
dataCommand.Parameters.Add(param)
IDataReader reader = dataCommand.ExecuteReader();
while(reader.Read())
{
myArrayList.Add(new CustomerInfo(reader.GetString(0),
reader.GetString(2)));
}
dataCommand.Cancel();
dataCommand.Dispose(); // I tried without this line as weel. No
luck.
reader.Close();
reader = null;
dataCommand = null;

transaction = connection.BeginTransaction(); // <--------
exception thrown here
dataCommand = connection.CreateCommand();
dataCommand.Transaction = transaction;
...
...
...
Click to expand...
--------------------------------------------------------------------------
-----

Exception: System.Data.SqlClient.SqlException
Message: Operation cancelled by user.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, TdsParserState state)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.ThrowAttentionError()
at System.Data.SqlClient.TdsParser.ProcessDone(SqlCommand cmd,
RunBehavior run)
at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand
cmdHandler, SqlDataReader dataStream)
at System.Data.SqlClient.TdsParser.Run(RunBehavior run)
at System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String
sqlBatch, String method)
at System.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel
iso)
at System.Data.SqlClient.SqlConnection.BeginTransaction()
at
Click to expand...
System.Data.SqlClient.SqlConnection.System.Data.IDbConnection.BeginTransacti
on()
at (my code line #....)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top