ADO.NET Connection Pooling & SQLServer

Question

• What is it?
• How do I implement connection pooling with MS SQL?
• What are the performance ramifications when
• Executing many queries one-after-the other (i.e. using a loop with 30K+ iterations calling a stored procedure)?
• Executing a few queries that take a long time (10+ min)?
• Are there any best practices?

Answer 1

Connection pooling is a mechanism to re-use connections, as establishing a new connection is slow.

If you use an MSSQL connection string and System.Data.SqlClient then you're already using it - in .Net this stuff is under the hood most of the time.

A loop of 30k iterations might be better as a server side cursor (look up T-SQL cursor statements), depending on what you're doing with each step outside of the sproc.

Long queries are fine - but be careful calling them from web pages as Asp.Net isn't really optimised for long waits and some connections will cut out.

Answer 2

A little more info on the connection pooling thing... you're using it already with SqlClient, but only if your connection string is identical for each new connection you open. My understanding is that the framework will pool connections automatically when it can, but if the connection string varies even slightly from one connection to the next, then the new connection won't come from the pool - it gets created anew (so it's more expensive).

You can use the Performance Monitor app with XP/Vista to watch SQL connections and you'll see pretty quickly whether or not pooling is being used. Look under the ".NET CLR Data" category" in Performance Monitor.