25 January 2012

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

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

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 3396 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share