Click here to monitor SSC

ASP.NET performance: is it all about the database?

Published 25 January 2012 7:09 am

Developing ASP.NET applications with a data layer, you may be well aware of the pain that performance issues can cause. Perhaps your apps response times drag, connections lag, and the overwhelming user experience is of watching the “page loading” graphic spin endlessly round and round. Sure, your application may be performing poorly. But you don’t need a performance profiling tool to fix that, right? What you really need is a better DBA.

Here’s why you might be wrong.

Think your ASP.NET applications performance problems all lie in the database? Prove it!

Yes, database issues underlie a lot of ASP.NET performance problems. Time spent retrieving data can dwarf the time your application spends executing your code. But that doesn’t mean the problem is out of your hands. To begin with, just assuming the problem lies in the database is a quick route to a smug DBA if it later turns out you’re wrong. Far better to know for sure how much of your applications time is spent waiting around for the database to do something, and how many round-trips to and from the database it makes.

So, how do you know for sure what your application is telling the database to do? If you have a really simple application, and wrote it all yourself, you may genuinely just know. But if your application uses enterprise framework or legacy code, has multiple developers, or has just been sitting around without your attention for a while, reviewing the static source code or stepping through line by line might take too long and fail to show up lurking problems. With a performance profiling tool, on the other hand, you can see exactly what the application does as it runs, pinpoint the calls it makes to the database, and show you at a glance what proportion of your execution time is spent waiting for the database.

Visualizing all of your applications database interactions gives you a quick, comprehensive view of how your code interacts with the database. If you see you’re telling the database to do something perverse, or inefficient, you can make big performance savings by simplifying and optimizing. You might find that data caching, enabling paging, or making better use of background processing makes the whole thing run faster and more smoothly. Secondly, if your own code really is unimpeachable, a visualization like this gives you the smoking gun to brandish at your DBA.

When It’s Not The Database

Sometimes you’ll find yourself with a database problem and no one to blame it on. For example, if your applications a custom frontend onto a commercial database, changing the way that database works might be out of your hands. Then you’ll need to look at interfacing with it more efficiently, or concentrate on improvements to your own codes performance.

Occasionally, and it takes a brave dev to admit this, you may even find that the problem is actually in your code, not in the database.

In both cases, profiling first lets you find the true culprit – be it the database or the .NET code – and therefore find the best solution. That might mean tweaking a method call, or it might, indeed, mean marching down to the sysadmin basement with the incontrovertible truth in your hands.

Further reading

4 Responses to “ASP.NET performance: is it all about the database?”

  1. tcurtin says:

    I agree. I’ve experience a lot of programmers using technologies to implement a solution to learn it rather than realizing that it’s not necessary or simply wrong.
    AJAX is a technology that is widely over used. For instance, I ripped out a web report that used ASPNet.AJAX that took 1.5 minutes to run as a result of web service calls for each row and column. The denormalized view of the data was all written client side.
    I ripped out the report and rebuilt the logic in SQL Server and used a GridView databound control and the report took 2-3 seconds total, to display.

  2. rwillemain says:

    Thank you for the article. What is a recommended approach the author would take in deciding upon what tool(s) to use for doing this profiling ?
    Differentiating between application and network latency puzzles me somewhat.

  3. Michaela Murray says:

    Sorry it’s taken me so long to get back to you, for some reason I’ve only just seen your comment. I would start by using a browser plug-in like Firebug to see if static resources like images were taking a long time to download. Having ruled out general network slowness, I’d move on to looking at the server side components, if possible running everything on the same development box to remove network latency. Profilers usually offer a choice between elapsed (‘wall-clock’) time and CPU time, and switching between the two indicates whether the problem is CPU-bound or database-bound.

    Determining whether slowness is due to the database itself or to latency between the database and the web server is harder, it’s probably a case of transplanting the queries to the database and running them there under the guidance of someone who knows one end of a query plan from the other.

    Does that help?

Leave a Reply