Performance Sin - Chatty Database Access And Loops (Plus Another Free Performance Tool)

Chatty database access is the surefire way for slow performance caused by  resources starvation that might  even lead to denial of service. Following is a real world case.

Customer

Service Unavailable message is consistently observed when there are more than 150 users access the web site. We think IIS cannot handle more than 150 users. What would you suggest?

Support

Let's see what "Service Unavailable" means. "IIS cannot start any new worker processes because of limited system resources...". Let's see the code.

The code

The code was calling on DB Access function that was put inside for loop that was called inside event handler. The event handler was called on each GridVew's row creation. Meaning, DB access in nested loop. In case of small GridVew of 10 rows and 10 cells the database would be accessed 100 times for each request. 150 concurrent users would create significant load of 15,000 connections:

SQL Server Profiler

After running SQL Server profiler it became clear that database access should be significantly improved. Following is the number of SQL Commands performed as a result of single request:

Conclusion

For improved performance avoid chatty data base access. Apply caching techniques instead.

More free performance tools

If you use SQL Server 2005 Express which does not come with built-in profiler you might find useful the following free tool:

Profiler for Microsoft SQL Server 2005 Express Edition

My related posts

• IIS7 Admin Pack Offers Built In Performance Analysis Reports
• Improve .Net Applications Performance Effectively And Efficiently
• Free Performance Tool - Analyze IIS Logs Like A Pro With Funnel Web Analyzer
• Stress Test ASP.NET Web Application With Free WCAT Tool
• Performance Testing For The Masses
• Performance Sin - Using Exceptions To Control Flow

Comments

• Anonymous
  April 28, 2008
  You've been kicked (a good thing) - Trackback from DotNetKicks.com
• Anonymous
  May 02, 2008
  Serving images dynamically may cause performance hit. Dynamically served images require more HTTP requests
• Anonymous
  May 03, 2008
  Je viens de découvrir cet outil au détour d'un post d'Alik Levin . Comme son nom l'indique, son but est
• Anonymous
  June 22, 2008
  Your ASP.NET application performs slower than expected? How many times do you access your database for
• Anonymous
  July 31, 2008
       To achieve best performance you need to make decisions based on trade-off between
• Anonymous
  July 31, 2008
      In my previous post -  Best ASP.NET Performance Winner For Data Binding - Hands
• Anonymous
  July 31, 2008
  In my previous post - Best ASP.NET Performance Winner For Data Binding - Hands Up To Response.Write()
• Anonymous
  November 01, 2008
  Field experience proves - the earlier performance is tackled in development lifecycle the better results