Performance of sqlDataReader.Close() is very bad?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a piece of code which goes like this.

Datareader dr = Datareader on (Select ItemName, ItemValue, LotValid from
Items where ItemName = something)
while dr.read
{
read and assign values
if LotValid = false;
break;
}
dr.close();

The issue arises when the number of records fetched by the query is high and
the LotValid boolean is false very soon.
Even when I call dr.Close(), it goes through the entire QuerySet and skips
each row individually. This hits the overall performance of the method very
badly.
Above mentioned code is just a sample scenario and optimisations like
putting the LotValid logic in the StoredProcedure cannot be done.
 
You are right, It would be syntactically wrong, I was trying to get the
pseudo code across for easy reading. The code would look like

SqlCommand cmd = new SqlCommand("Select ItemName, ItemValue, LotValid from
Items where ItemName = something");
//Lets say the query returne 10000 rows
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//read and assign all values from datareader- skipped here
if (dr["LotValid"] == "false")//Lets say his condition comes true for the
50th row
break;
}
dr.Close();
---------------------------------------------------------------------------------------------
 
I created 10,000 records and wrote this code in vb.net to emulate what you
did, it worked fine and terminates properly at 50 items.

Dim conString As String =
"Server=LOUNGE;Database=TEST;Trusted_Connection=True;"
Dim sqlString As String = "SELECT * FROM [Test];"
Dim con As New Data.SqlClient.SqlConnection(conString)
Dim sqlCommand As New Data.SqlClient.SqlCommand(sqlString, con)

Dim rdr As Data.SqlClient.SqlDataReader

Try
con.Open()
rdr = sqlCommand.ExecuteReader
Dim x As Int16 = 0
While rdr.Read()
Response.Write(rdr(0).ToString & "<BR>")
If x > 50 Then Exit While
x += 1
End While
rdr.Close()
Catch ex As Exception
Response.Write("ERROR : " & ex.ToString)
Finally
con.Close()

End Try

--
( OHM ) - One Handed Man
AKA Terry Burns - http://TrainingOn.net
Santosh said:
You are right, It would be syntactically wrong, I was trying to get the
pseudo code across for easy reading. The code would look like

SqlCommand cmd = new SqlCommand("Select ItemName, ItemValue, LotValid from
Items where ItemName = something");
//Lets say the query returne 10000 rows
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//read and assign all values from datareader- skipped here
if (dr["LotValid"] == "false")//Lets say his condition comes true for the
50th row
break;
}
dr.Close();
---------------------------------------------------------------------------------------------

OHM ( One Handed Man ) said:
I think your code is invalid.
Click to expand...
Click to expand...
 
I think he means that the the closing of the datareader takes a long time,
and he is assuming that it must be going through the rest of the result set.
Not that the codes is actually continuing to loop.

Here is a snippet from the documentation:
The Close method fills in the values for output parameters, return values
and RecordsAffected, increasing the time that it takes to close a
SqlDataReader that was used to process a large or complex query. When the
return values and the number of records affected by a query are not
significant, the time that it takes to close the SqlDataReader can be
reduced by calling the Cancel method of the associated SqlCommand object
before calling the Close method.

According to this, performance should be improved by calling Cancel on the
SqlCommand object before closing the datareader.

OHM ( One Handed Man ) said:
I created 10,000 records and wrote this code in vb.net to emulate what you
did, it worked fine and terminates properly at 50 items.

Dim conString As String =
"Server=LOUNGE;Database=TEST;Trusted_Connection=True;"
Dim sqlString As String = "SELECT * FROM [Test];"
Dim con As New Data.SqlClient.SqlConnection(conString)
Dim sqlCommand As New Data.SqlClient.SqlCommand(sqlString, con)

Dim rdr As Data.SqlClient.SqlDataReader

Try
con.Open()
rdr = sqlCommand.ExecuteReader
Dim x As Int16 = 0
While rdr.Read()
Response.Write(rdr(0).ToString & "<BR>")
If x > 50 Then Exit While
x += 1
End While
rdr.Close()
Catch ex As Exception
Response.Write("ERROR : " & ex.ToString)
Finally
con.Close()

End Try

--
( OHM ) - One Handed Man
AKA Terry Burns - http://TrainingOn.net
Santosh said:
You are right, It would be syntactically wrong, I was trying to get the
pseudo code across for easy reading. The code would look like

SqlCommand cmd = new SqlCommand("Select ItemName, ItemValue, LotValid
from
Items where ItemName = something");
//Lets say the query returne 10000 rows
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//read and assign all values from datareader- skipped here
if (dr["LotValid"] == "false")//Lets say his condition comes true for the
50th row
break;
}
dr.Close();
---------------------------------------------------------------------------------------------

OHM ( One Handed Man ) said:
I think your code is invalid.

if LotValid = false; //<< This is an assignment not an equality
test

while dr.read
{
read and assign values
if LotValid = false;
break;
}
dr.close();
Click to expand...
Click to expand...
Click to expand...
 
OK thanks for the insite. In my test, the result was instant without using
cancel.

--
( OHM ) - One Handed Man
AKA Terry Burns - http://TrainingOn.net

Marina Levit said:
I think he means that the the closing of the datareader takes a long time,
and he is assuming that it must be going through the rest of the result
set. Not that the codes is actually continuing to loop.

Here is a snippet from the documentation:
The Close method fills in the values for output parameters, return values
and RecordsAffected, increasing the time that it takes to close a
SqlDataReader that was used to process a large or complex query. When the
return values and the number of records affected by a query are not
significant, the time that it takes to close the SqlDataReader can be
reduced by calling the Cancel method of the associated SqlCommand object
before calling the Close method.

According to this, performance should be improved by calling Cancel on the
SqlCommand object before closing the datareader.

OHM ( One Handed Man ) said:
I created 10,000 records and wrote this code in vb.net to emulate what you
did, it worked fine and terminates properly at 50 items.

Dim conString As String =
"Server=LOUNGE;Database=TEST;Trusted_Connection=True;"
Dim sqlString As String = "SELECT * FROM [Test];"
Dim con As New Data.SqlClient.SqlConnection(conString)
Dim sqlCommand As New Data.SqlClient.SqlCommand(sqlString, con)

Dim rdr As Data.SqlClient.SqlDataReader

Try
con.Open()
rdr = sqlCommand.ExecuteReader
Dim x As Int16 = 0
While rdr.Read()
Response.Write(rdr(0).ToString & "<BR>")
If x > 50 Then Exit While
x += 1
End While
rdr.Close()
Catch ex As Exception
Response.Write("ERROR : " & ex.ToString)
Finally
con.Close()

End Try

--
( OHM ) - One Handed Man
AKA Terry Burns - http://TrainingOn.net
Santosh said:
You are right, It would be syntactically wrong, I was trying to get the
pseudo code across for easy reading. The code would look like

SqlCommand cmd = new SqlCommand("Select ItemName, ItemValue, LotValid
from
Items where ItemName = something");
//Lets say the query returne 10000 rows
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//read and assign all values from datareader- skipped here
if (dr["LotValid"] == "false")//Lets say his condition comes true for
the
50th row
break;
}
dr.Close();
---------------------------------------------------------------------------------------------

:

I think your code is invalid.

if LotValid = false; //<< This is an assignment not an equality
test

while dr.read
{
read and assign values
if LotValid = false;
break;
}
dr.close();
Click to expand...
Click to expand...
Click to expand...
 
I guess it depends on the actual query and the sql server, etc.

OHM ( One Handed Man ) said:
OK thanks for the insite. In my test, the result was instant without using
cancel.

--
( OHM ) - One Handed Man
AKA Terry Burns - http://TrainingOn.net

Marina Levit said:
I think he means that the the closing of the datareader takes a long time,
and he is assuming that it must be going through the rest of the result
set. Not that the codes is actually continuing to loop.

Here is a snippet from the documentation:
The Close method fills in the values for output parameters, return values
and RecordsAffected, increasing the time that it takes to close a
SqlDataReader that was used to process a large or complex query. When the
return values and the number of records affected by a query are not
significant, the time that it takes to close the SqlDataReader can be
reduced by calling the Cancel method of the associated SqlCommand object
before calling the Close method.

According to this, performance should be improved by calling Cancel on
the SqlCommand object before closing the datareader.

OHM ( One Handed Man ) said:
I created 10,000 records and wrote this code in vb.net to emulate what
you did, it worked fine and terminates properly at 50 items.

Dim conString As String =
"Server=LOUNGE;Database=TEST;Trusted_Connection=True;"
Dim sqlString As String = "SELECT * FROM [Test];"
Dim con As New Data.SqlClient.SqlConnection(conString)
Dim sqlCommand As New Data.SqlClient.SqlCommand(sqlString, con)

Dim rdr As Data.SqlClient.SqlDataReader

Try
con.Open()
rdr = sqlCommand.ExecuteReader
Dim x As Int16 = 0
While rdr.Read()
Response.Write(rdr(0).ToString & "<BR>")
If x > 50 Then Exit While
x += 1
End While
rdr.Close()
Catch ex As Exception
Response.Write("ERROR : " & ex.ToString)
Finally
con.Close()

End Try

--
( OHM ) - One Handed Man
AKA Terry Burns - http://TrainingOn.net
You are right, It would be syntactically wrong, I was trying to get the
pseudo code across for easy reading. The code would look like

SqlCommand cmd = new SqlCommand("Select ItemName, ItemValue, LotValid
from
Items where ItemName = something");
//Lets say the query returne 10000 rows
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//read and assign all values from datareader- skipped here
if (dr["LotValid"] == "false")//Lets say his condition comes true for
the
50th row
break;
}
dr.Close();
---------------------------------------------------------------------------------------------

:

I think your code is invalid.

if LotValid = false; //<< This is an assignment not an equality
test

while dr.read
{
read and assign values
if LotValid = false;
break;
}
dr.close();
Click to expand...
Click to expand...
Click to expand...
 
Thanks Marina, that is exactly what I meant. I found a post on .NET 247 which
says ---This is by design. When you ask for 100 or 10000 rows to be piped to
your
app with the DataReader, the engine provides those rows--whether you use
them all or not. If you close the Reader, ADO.NET has no choice but to
comply with your first request and fetch all of the rows to empty the pipe.
It's better to:
a) Ask for just the rows you need
b) If you have to close the reader before EOF, use the Cancel method on
the Command. This (might) stop the flow of rows--assuming the server can
handle it.--- However, I cannot ask for just the rows that I need and the
second method doesn't seem to help.

Marina Levit said:
I guess it depends on the actual query and the sql server, etc.

OHM ( One Handed Man ) said:
OK thanks for the insite. In my test, the result was instant without using
cancel.

--
( OHM ) - One Handed Man
AKA Terry Burns - http://TrainingOn.net

Marina Levit said:
I think he means that the the closing of the datareader takes a long time,
and he is assuming that it must be going through the rest of the result
set. Not that the codes is actually continuing to loop.

Here is a snippet from the documentation:
The Close method fills in the values for output parameters, return values
and RecordsAffected, increasing the time that it takes to close a
SqlDataReader that was used to process a large or complex query. When the
return values and the number of records affected by a query are not
significant, the time that it takes to close the SqlDataReader can be
reduced by calling the Cancel method of the associated SqlCommand object
before calling the Close method.

According to this, performance should be improved by calling Cancel on
the SqlCommand object before closing the datareader.

I created 10,000 records and wrote this code in vb.net to emulate what
you did, it worked fine and terminates properly at 50 items.

Dim conString As String =
"Server=LOUNGE;Database=TEST;Trusted_Connection=True;"
Dim sqlString As String = "SELECT * FROM [Test];"
Dim con As New Data.SqlClient.SqlConnection(conString)
Dim sqlCommand As New Data.SqlClient.SqlCommand(sqlString, con)

Dim rdr As Data.SqlClient.SqlDataReader

Try
con.Open()
rdr = sqlCommand.ExecuteReader
Dim x As Int16 = 0
While rdr.Read()
Response.Write(rdr(0).ToString & "<BR>")
If x > 50 Then Exit While
x += 1
End While
rdr.Close()
Catch ex As Exception
Response.Write("ERROR : " & ex.ToString)
Finally
con.Close()

End Try

--
( OHM ) - One Handed Man
AKA Terry Burns - http://TrainingOn.net
You are right, It would be syntactically wrong, I was trying to get the
pseudo code across for easy reading. The code would look like

SqlCommand cmd = new SqlCommand("Select ItemName, ItemValue, LotValid
from
Items where ItemName = something");
//Lets say the query returne 10000 rows
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//read and assign all values from datareader- skipped here
if (dr["LotValid"] == "false")//Lets say his condition comes true for
the
50th row
break;
}
dr.Close();
---------------------------------------------------------------------------------------------

:

I think your code is invalid.

if LotValid = false; //<< This is an assignment not an equality
test

while dr.read
{
read and assign values
if LotValid = false;
break;
}
dr.close();
Click to expand...
Click to expand...
Click to expand...
 
Santosh said:
Thanks Marina, that is exactly what I meant. I found a post on .NET 247
which
says ---This is by design. When you ask for 100 or 10000 rows to be piped
to
your
app with the DataReader, the engine provides those rows--whether you use
them all or not. If you close the Reader, ADO.NET has no choice but to
comply with your first request and fetch all of the rows to empty the
pipe.
Yes.

It's better to:
a) Ask for just the rows you need
b) If you have to close the reader before EOF, use the Cancel method on
the Command. This (might) stop the flow of rows--assuming the server can
handle it.--- However, I cannot ask for just the rows that I need
Click to expand...

Why?

and the
 
My code and the SQL Queries is not handwritten. It is generated from our code
generator. So I will need something which is very generic.
 
:-) I would tend to agree to this in a normal application. But when there are
layers of abstraction and generalizations, A bad design that works seems to
be the best alternative.
Can you think of someway to work around this.

Miha Markic said:
Hi Santosh,

This is called bad bad bad design.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Santosh said:
My code and the SQL Queries is not handwritten. It is generated from our
code
generator. So I will need something which is very generic.
Click to expand...
Click to expand...
 
Santosh said:
:-) I would tend to agree to this in a normal application. But when there
are
layers of abstraction and generalizations, A bad design that works seems
to
be the best alternative.
Can you think of someway to work around this.
Click to expand...

Perhaps use better architecture as I do. It is hard to speculate without
much information though but using optimal sql commands with database is
critical as in a normal application most time is spent in database
operations. And you could easily kill your application if you don't pay
attention to this.
 
Back
Top