What is the most important effect on performance in a database-backed web application?

Question

I was recently asked to speed up a C#/ASP.NET/SQL Server business app website. Since I just started, I don't know too much about the internals. So where do I start? Sight unseen, what is the single most important thing affecting performance on a system like this? Database tuning? Hardware? Individual page optimization? What is the first thing you'd look at?

EDIT: After I actually do the work, I'll come back and post the answer. ;)

EDIT again: "Profile" is currently the most-voted answer, and I agree that that is clearly what one should do. But I was looking for guesses/experience as to what the profiling results would show, so I don't think that answer counts...

Answer 1

The first thing i would look at is the database, make sure there are indexes where indexes should be.

Answer 2

I'd vote for database locking issues from my experience.

Answer 3

What is the first thing you'd look at?

A profiler.

Answer 4

The first thing I'd do is get a copy of JetBrains' dotTrace application, and use it to profile the site. Don't make assumptions about where the performance problem lies - go find out!

Answer 5

Anything could be the problem. Take a page that is behaving badly and trace the flow of control. Use the debugger and the sql profiler and see EVERYTHING that page does. It should be clear what to fix then.

Remember to start on a page users are frustrated with being slow. Don't bother with other pages (eg the login page, which almost never is an issue.)

Of course, with experience you could just look at the codes (DB + .NET) and know what the problem is, but you don't have the experience. Thus another option is to hire a consultant who has experience.

Edit - My guesses (cuz I want a cookie)

• If it is AJAX it could be the JavaScript
• Otherwise it is most likely the TSQL
• Unless the programmer did not know how to use TSQL and just pulled in whole DB Tables and manipulated the data in C#
• or the programmer had no understanding of the page model (or MVC model) and it is the most bizarre code you have ever seen in your life.

I've see all of these.

Answer 6

Using a profiler is a good way to measure performance but tricky if you don't do a reasonable emulation of production performance.

Most DB-backed web applications are I/O-bound not compute-bound. Absent a profiler, I would guess query performance. You can improve query performance by running an explain query play to figure out what indexes you need. Or avoid having to optimize a query by putting result sets/active records into a cache.

Answer 7

For the database part, here is a good start what to look for:

Troubleshooting Performance Problems in SQL Server 2005

But I wouldn't also be suprised if you find ineffective client code. Especially when communicating with the database.

Answer 8

It turns out that the most egregious problem was a few trouble pages that hammered the database with thousands of SQL queries. The code was relatively innocent-looking, just some data bound grids. However, C#/LINQ was a bit too powerful for its (or our) own good: if you bind a grid to a table, but the grid wants to display a field from another table linked via foreign key, it does it! But it does it by running a zillion queries:

SELECT ... FROM other_table WHERE Id = 1
...
SELECT ... FROM other_table WHERE Id = 2
...
SELECT ... FROM other_table WHERE Id = 3
...
SELECT ... FROM other_table WHERE Id = 4
...

and so on... by making sure that the dataset that was bound to the control had all necessary data without requerying the database, the problems eased noticeably.

Now back to profiling...