ADO.NET and Sleeping processes

[amerrell]

How can I prevent the flood of sleeping processes that occur from my database queries using datareaders?

Here is how I make my connections am I doing somehting wrong?


Dim myConnection As New SqlConnection(GetAppSetting("DBCon"))
Dim myCommand As New SqlCommand("Select * from table where column = @column", myConnection)
myCommand.Parameters.Add("@column", columnvalue)

Try
Dim dr As SqlDataReader
myConnection.Open()
dr = myCommand.ExecuteReader()
While dr.read()
strVar = dr("somecolumn")
end while
dr.close()
myconnection.close()
Catch ex as Exception
Trace.Write("SQLQuery ", ex.Message)
End Try


Thanks for any help...

amerrell

 

[Patrice]

What you see is likely the pooling feature :
http://msdn.microsoft.com/library/d...nectionpoolingforsqlservernetdataprovider.asp

Adding "pooling=false" in your connection string should disable pooling...

Patrice

--

How can I prevent the flood of sleeping processes that occur from my

database queries using datareaders?

 

[AMerrell]

Yeah, The reason I'm wanting to keep polled conections is because of this statement: "Pooling connections can significantly enhance the performance and scalability of your application." My problem is that even when I close the connection it doesn't seem to be releaseing it back to the pool.

My understanding is as long as the connection string doesn't change it should take the next available pooled connection. Instead what it's doing is just creating another pooled connection and sleeping. I eventually hit the max pool size and all applications stop responding.

What I'm trying to figure out is why my connection is not being release back to the pool. Correct me if I'm wrong but I do believe my code does close the connection.

What you see is likely the pooling feature :
http://msdn.microsoft.com/library/d...nectionpoolingforsqlservernetdataprovider.asp

Adding "pooling=false" in your connection string should disable pooling...

Patrice

--

How can I prevent the flood of sleeping processes that occur from my

database queries using datareaders?

 

[William \(Bill\) Vaughn]

Take a gander at my article on connection pooling. The server connections
are NOT closed when the pooled connection is closed--it takes 4-8 minutes
for these to die of old age and be closed. See www.betav.com/articles.htm


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[AMerrell]

Thanks couldn't read the article titled Swimming in the .NET Connection Pool. Have to see if my employerr will get me a subscription. I am reading your article in MSDN Magazine and it is very helpful.

Thank agian.
AMerrell
Take a gander at my article on connection pooling. The server connections
are NOT closed when the pooled connection is closed--it takes 4-8 minutes
for these to die of old age and be closed. See www.betav.com/articles.htm


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William \(Bill\) Vaughn]

MSDN has cross-licensed these articles so you should be able to get it from
there... if not, let me know and I'll fill in the blanks.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[AMerrell]

Just wanted to drop a follow up... After reading your article titled "The .NET Connection Pool Lifeguard" I've managed to get the sleeping processes down to just one.

What I did was changed this line dr = myCommand.ExecuteReader() to this dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection) in all my queries. I do get another sleeping process from one page that calls a function that runs a scalar query while looping through a reader. I'm guessing this is normal since I still have one connection open and I am opening another. Something like the code below. Now I"m really interesed in reading your article "Swimming in the .NET connection pool". I have not been able to find it in the MSDN articles.

What I ended up with is the following code:

Dim myConnection As New SqlConnection(GetAppSetting("DBCon"))
Dim myCommand As New SqlCommand("Select somecolumn from table where column =@column", myConnection)
myCommand.Parameters.Add("@column", columnvalue)

Try
Dim dr As SqlDataReader
myConnection.Open()
dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
While dr.read()
strVar = dr("somecolumn")
strvar = someFunction(strvar)
end while
dr.close()
myconnection.close()
Catch ex as Exception
Trace.Write("SQLQuery ", ex.Message)
End Try

Thanks for the help...

Amerrell
MSDN has cross-licensed these articles so you should be able to get it from
there... if not, let me know and I'll fill in the blanks.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William \(Bill\) Vaughn]

Unfortunately, I can't send you a copy of the article as the copyright
belongs to the magazine that bought it. Most of these articles are drawn
from my books on ADO.NET. However, it looks like you're on the right track.
Remember, if you pass a DataReader to another layer you MUST set the
CommandBehavior.CloseConnection option or the receiving routine can't close
the connection--no one can so it's orphaned. I'm not really a fan of the
DataReader for most work as it's so problematic and requires so much code to
handle properly. Make sure your Finally blocks also close the DataReader in
case something goes wrong.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Eric]

if you pass a DataReader to another layer you MUST set the
CommandBehavior.CloseConnection option or the receiving routine can't close
the connection--no one can so it's orphaned.

Can't the receiving layer close the reader when it's done, even if you
didn't set CommandBehavior?

Eric

 

[William \(Bill\) Vaughn]

Sure it can, but if you don't set the CommandBehaivor the connection remains
open and orphaned.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________