ADO.NET Connection Pooling & SQLServer

Question :

ADO.NET Connection Pooling & SQLServer,

Answer :

  • 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?

,

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.

Read More  What good technology podcasts are out there?

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.

That’s the answer ADO.NET Connection Pooling & SQLServer, Hope this helps those looking for an answer. Then we suggest to do a search for the next question and find the answer only on our site.

Read More  Document or RPC based web services

Disclaimer :

The answers provided above are only to be used to guide the learning process. The questions above are open-ended questions, meaning that many answers are not fixed as above. I hope this article can be useful, Thank you