sqldatareader Connection pooling bug?

  • Thread starter Thread starter Willy Yao
  • Start date Start date
W

Willy Yao

We have the database connection pooling problem with our asp.net program.

In order to test this, I wrote the following simple test code one one aspx
page, and then load testing this page with 200 users

I could see the database connection went up to maximum 100 on my database,
then gave error message saying connection timeout, maximum pooling reached
etc.

it seems connection pooling doesn't work at all for datareader. I have
seen people posted the same problem, but never saw any definite solution to
it. I suspect it is a bug in .NET.

I tried used dataset. It seems that dataset doesn't have this problem.

Any thought on this?



Dim oCnn As SqlConnection

Dim oData As SqlDataReader

Dim oCmd As SqlCommand

oCnn = New SqlConnection("server=hostname;database=test;user id=user1;
password=pwd1;")

oCnn.Open()

oCmd = New SqlCommand("select * from CLIENT", oCnn)

oData = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

oData.Read()

Response.Write(oData(0))

oData.Close()

oCnn.Close()

oCmd = Nothing

oData = Nothing

oCnn = Nothing
 
Thanks for your reply.

But if you look at the code I wrote below, I did close the datareader and
dataconnection. I couldn't see any place that have code error.

It is a very simple code.
 
In our production code, we do have all the exception handlings. The code I posted was for testing purpose, and from the load testing result, we could see there was no exception throwned. And even after I put the exception control in the testing code, we still have the same problem. So I guess it is not the code problem, I am wondering if anyone has done stress testing for the datareader on two separated quad-CPUs database server and web server. I am thinking it might be the speed issue. Using SQL profiler, I could see the connection reset, but only after 7 second to 30 seconds. That would make pooling connection doesn't have enough time to return to pool and have to create new connections. The network communication time might be one issue too.

Thanks for you reply.
 
If the request for connections is out pacing the
connection manager, is it possible you would have to boost
up the maximum pooled connections to pass this test?

Either that or maybe your test does not accurately model
real system usage?

Jeff

-----Original Message-----
In our production code, we do have all the exception
Click to expand...
handlings. The code I posted was for testing purpose, and
from the load testing result, we could see there was no
exception throwned. And even after I put the exception
control in the testing code, we still have the same
problem. So I guess it is not the code problem, I am
wondering if anyone has done stress testing for the
datareader on two separated quad-CPUs database server and
web server. I am thinking it might be the speed issue.
Using SQL profiler, I could see the connection reset, but
only after 7 second to 30 seconds. That would make pooling
connection doesn't have enough time to return to pool and
have to create new connections. The network communication
time might be one issue too.
Thanks for you reply.



"David Browne" <davidbaxterbrowne no potted
Click to expand...
(e-mail address removed)> wrote in message
 
I see this thread is still open, so I might just end it. I found out what
causes the problem. It is Microsoft datareader issue. You'd better loop
through all of the records of datareader before you close it even if you
just use the first couple of records. Otherwise, it will take longer time
for the sql server to close the connection. And if the requests to the
database are too fast(in our case, 200 to 400 hit/s), it will take up to 8
s to 20 s for a connection to close.
So definitely we would get over 100 connections in the pool.
just make some empty loop.
while oReader.read()
end while

Thanks all.

Willy
 
Back
Top