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

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

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

Patrice

--

"amerrell" <(E-Mail Removed)> a écrit dans le message de
news:(E-Mail Removed)...
> 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

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.


>>> Patrice<(E-Mail Removed)> 2/1/2005 12:12:20 PM >>>
What you see is likely the pooling feature :
http://msdn.microsoft.com/library/de...taprovider.asp

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

Patrice

--

"amerrell" <(E-Mail Removed)> a écrit dans le message de
news:(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:%23%(E-Mail Removed)...
> 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.
>
>
>>>> Patrice<(E-Mail Removed)> 2/1/2005 12:12:20 PM >>>
> What you see is likely the pooling feature :
> http://msdn.microsoft.com/library/de...taprovider.asp
>
> Adding "pooling=false" in your connection string should disable pooling...
>
> Patrice
>
> --
>
> "amerrell" <(E-Mail Removed)> a écrit dans le message de
> news:(E-Mail Removed)...
>> 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
>
>
>
>

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

>>> William (Bill) Vaughn<(E-Mail Removed)> 2/1/2005 2:23:19 PM >>>
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" <(E-Mail Removed)> wrote in message
news:%23%(E-Mail Removed)...
> 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.
>
>
>>>> Patrice<(E-Mail Removed)> 2/1/2005 12:12:20 PM >>>
> What you see is likely the pooling feature :
> http://msdn.microsoft.com/library/de...taprovider.asp
>
> Adding "pooling=false" in your connection string should disable pooling...
>
> Patrice
>
> --
>
> "amerrell" <(E-Mail Removed)> a écrit dans le message de
> news:(E-Mail Removed)...
>> 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
>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>>>> William (Bill) Vaughn<(E-Mail Removed)> 2/1/2005 2:23:19 PM
>>>> >>>
> 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" <(E-Mail Removed)> wrote in message
> news:%23%(E-Mail Removed)...
>> 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.
>>
>>
>>>>> Patrice<(E-Mail Removed)> 2/1/2005 12:12:20 PM >>>
>> What you see is likely the pooling feature :
>> http://msdn.microsoft.com/library/de...taprovider.asp
>>
>> Adding "pooling=false" in your connection string should disable
>> pooling...
>>
>> Patrice
>>
>> --
>>
>> "amerrell" <(E-Mail Removed)> a écrit dans le message de
>> news:(E-Mail Removed)...
>>> 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
>>
>>
>>
>>
>
>
>
>

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

>>> William (Bill) Vaughn<(E-Mail Removed)> 2/1/2005 6:13:08 PM >>>
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>>>> William (Bill) Vaughn<(E-Mail Removed)> 2/1/2005 2:23:19 PM
>>>> >>>
> 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" <(E-Mail Removed)> wrote in message
> news:%23%(E-Mail Removed)...
>> 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.
>>
>>
>>>>> Patrice<(E-Mail Removed)> 2/1/2005 12:12:20 PM >>>
>> What you see is likely the pooling feature :
>> http://msdn.microsoft.com/library/de...taprovider.asp
>>
>> Adding "pooling=false" in your connection string should disable
>> pooling...
>>
>> Patrice
>>
>> --
>>
>> "amerrell" <(E-Mail Removed)> a écrit dans le message de
>> news:(E-Mail Removed)...
>>> 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
>>
>>
>>
>>
>
>
>
>

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.
__________________________________

"AMerrell" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>>>> William (Bill) Vaughn<(E-Mail Removed)> 2/1/2005 6:13:08 PM
>>>> >>>
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>>>>> William (Bill) Vaughn<(E-Mail Removed)> 2/1/2005 2:23:19 PM
>>>>> >>>
>> 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" <(E-Mail Removed)> wrote in message
>> news:%23%(E-Mail Removed)...
>>> 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.
>>>
>>>
>>>>>> Patrice<(E-Mail Removed)> 2/1/2005 12:12:20 PM >>>
>>> What you see is likely the pooling feature :
>>> http://msdn.microsoft.com/library/de...taprovider.asp
>>>
>>> Adding "pooling=false" in your connection string should disable
>>> pooling...
>>>
>>> Patrice
>>>
>>> --
>>>
>>> "amerrell" <(E-Mail Removed)> a écrit dans le message de
>>> news:(E-Mail Removed)...
>>>> 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
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>
>
>
>

William (Bill) Vaughn wrote:
> 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

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.
__________________________________

"Eric" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> William (Bill) Vaughn wrote:
>> 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