Thursday, October 25, 2007

Optimize Web Service Database Connection

“Can we optimize the way our Web Service connects to Database Server? Since every time a call is being made to a method contains query, a group of codes to create new database connection is being executed.”

I was asked this question by my supervisor and (thanks to my lack of knowledge on MS SQL Server) it tickles me, so I fired up Google and found this article “10 Tips for Writing High-Performance Web Applications” by Rob Howard for Microsoft. Tips 3 seemed to answer my curiosity, the full article is at this link:
http://msdn.microsoft.com/msdnmag/issues/05/01/ASPNETPerformance/

Tip 3—Connection Pooling

Setting up the TCP connection between your Web application and SQL Server™ can be an expensive operation. Developers at Microsoft have been able to take advantage of connection pooling for some time now, allowing them to reuse connections to the database. Rather than setting up a new TCP connection on each request, a new connection is set up only when one is not available in the connection pool. When the connection is closed, it is returned to the pool where it remains connected to the database, as opposed to completely tearing down that TCP connection.

Of course you need to watch out for leaking connections. Always close your connections when you're finished with them. I repeat: no matter what anyone says about garbage collection within the Microsoft® .NET Framework, always call Close or Dispose explicitly on your connection when you are finished with it. Do not trust the common language runtime (CLR) to clean up and close your connection for you at a predetermined time. The CLR will eventually destroy the class and force the connection closed, but you have no guarantee when the garbage collection on the object will actually happen.

To use connection pooling optimally, there are a couple of rules to live by. First, open the connection, do the work, and then close the connection. It's okay to open and close the connection multiple times on each request if you have to (optimally you apply Tip 1) rather than keeping the connection open and passing it around through different methods. Second, use the same connection string (and the same thread identity if you're using integrated authentication). If you don't use the same connection string, for example customizing the connection string based on the logged-in user, you won't get the same optimization value provided by connection pooling. And if you use integrated authentication while impersonating a large set of users, your pooling will also be much less effective. The .NET CLR data performance counters can be very useful when attempting to track down any performance issues that are related to connection pooling.

Whenever your application is connecting to a resource, such as a database, running in another process, you should optimize by focusing on the time spent connecting to the resource, the time spent sending or retrieving data, and the number of round-trips. Optimizing any kind of process hop in your application is the first place to start to achieve better performance.

The application tier contains the logic that connects to your data layer and transforms data into meaningful class instances and business processes. For example, in Community Server, this is where you populate a Forums or Threads collection, and apply business rules such as permissions; most importantly it is where the Caching logic is performed.

So, basically SQL Server done the optimization job for us, all we need to do is use the same connection string (and the same thread identity if integrated authentication is used) and SQL Server will see if it is still available in the connection pool to be reused.

I found a deeper analysis and recommendation on Connection Pooling at the article “Tuning Up ADO.NET Connection Pooling in ASP.NET Applications” by Dmitri Khanine, this article can be found at this link:
(http://www.15seconds.com/issue/040830.htm)

10 comments:

Scott Levy said...

Nice post. Here’s a product with a powerful database in the cloud with ready-made apps. Just point-and-click to build your custom apps
http://www.caspio.com/

Anonymous said...

Hmm іs anуone else еnсounteгіng problems with the piсturеs on this blog loaԁіng?
Ι'm trying to determine if its a problem on my end or if it's the blog.
Any fеed-back wοuld bе grеatly aρprесіatеԁ.


Feel free to ѕuгf tо my page ...
puzzle games

Anonymous said...

I think the admin of this ѕite is genuinely working harԁ for his web ѕite, sіnce here еveгy data is quality baseԁ stuff.



Stop by my webpаge - fashion games online

Anonymous said...

Thankѕ for shаring уour thοughtѕ on reggae t ѕhirts.

Regarԁѕ

my ωeb blοg; Selektah new youth culture clothing Brand

Anonymous said...

Simplу ωant to say уοur article is aѕ
astounding. The сlarіtу to your put up is simply cool and i can
assume уou are knowledgeable in this subject. Well alοng with youг pегmissіon lеt me to grab your RЅS feed to
stay uрdated wіth forthcοming post.
Τhank уou 1,000,000 and please сarry on
the enjoyable ωork.

my web ѕite ??????as? a?t?????t??

Anonymous said...

Another comparison is how he blew £250, 000 in non-gambling expenditures by middle-class and poor
player. There are many of us dream off the call in order to achieve glory: interfere with
play money. A good handicapper analyzes the side off
betting gakes don'tend with crying! Clinicians report that would suit almost any card.
Not a soul mate, Demi Lovwto are coming from this.
You can also use your debit or credit card. With a good idea, and normally only comes down to thhe airwaves
and streets tto push their message will appear.


Check out my web site :: sportsbet.com.au ()

Anonymous said...

I was recommended this blog by my cousin. I am not
sure whether this post is written by him as nobody else know such detailed about my trouble.
You are wonderful! Thanks!

my web page ... http://www.Actualite-economie.com/bourse/moje-wiki-rudimentary-elements-of-travel-to-paris-an-int... -
puzzlejigsaw.net -

Anonymous said...

Being able to make accurate winning football stats predictions means a winning season. Braces of this type are perfect for athletes who are
returning to football after an injury. Most likely you
will need to double team this participant, and depending on which offensive
lineman you use to double team, the remainder of your offensive line
will be following rules that might permit some defenders to be unblocked.


My website - http://www.nouvelletriche.com/top-eleven-hack-triche-token-nouvelle-astu...

Anonymous said...

I like the helpful information you provide in your articles.
I'll bookmark your weblog and check again here frequently.
I'm quite sure I will learn plenty of new stuff right here!
Good luck for the next!

Look at my homepage: Louis Vuitton USA

Anonymous said...

Hello, I would like to subscribe for thi website to gett hottest updates, so where can i do iit please help.


Here is my blog ... creating a blog