Why doesn't ADO.Net reuse the connections in the connection pool

[Rob Kraft]

I have an application that makes hundreds of database calls in the first few
seconds it loads up. During this time, it opens 100+ connections. ALL of
the connections use the exact same connection string. Connection pooling is
turned on. This is a VStudio 2005 C# ADO.Net 2.0 .Net 2.0 application. It
uses default settings against SQL Server 2005. Is my app so fast that the
connections don't get re-used? Also, ALL of the connections are opened
inside a USING statement, and dispose always gets called.
I guess I am trying to determine how ADO.Net decides to open a new
connection in the pool versus re-using an existing sleeping connection.

 

[William Vaughn]

If your application queries or other operations are not complete, the pooled
Connection cannot be reused. 100 is a lot of connections. I would tend to
look at ways to execute SPs or even ad-hoc queries that return multiple
resultsets. I would also consider using SQLCe to store application
configuration and lookup table data instead of initializing through the
server.

See my whitepaper on the connection pool or Chapter 9 of my book.
http://betav.com/blog/billva/2007/05/managing_and_monitoring_net_co.html


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Rob Kraft]

Thanks for the quick reply Bill. There is a lot of useful info in the link
you provided. Your indication that the results I was seeing is not what I
should expect confirmed that I should spend more time investigating, which I
did. Eventually I found that one of our frequently called methods did not
have the USING clause wrapped around it - that was the major cause of the
hanging connections.

Thanks,

Rob Kraft