Geek Answers Handbook

What is the biggest impact on performance in a database-enabled web application?

Recently, I was asked to speed up the creation of a C # / ASP.NET / SQL Server business application website. Ever since I started, I don’t know too much about the insides. So where to start? Invisible glance, what is the most important thing that affects performance in such a system? Database setup? Hardware? Individual page optimization? What did you first notice?

EDIT: After I really do this work, I will come back and post an answer .;)

EDIT again: “Profile” is currently the most voted answer, and I agree that this is clearly what needs to be done. But I was looking for guesses / experience as to what the profiling results will show, so I don't think the answer counts ...

+3
source share
8 answers

It turned out that the most egregious problem was a few pages of problems that clogged the database with thousands of SQL queries. The code was relatively innocent looking, only some related to grid data. However, C # / LINQ was too powerful for its own (or ours) own good: if you bind a grid to a table, but the grid wants to display a field from another table linked via a foreign key, it does it! But he does this by executing millionth 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 ...

etc., making sure that the data set bound to the control had all the necessary data, without requiring a database, the problems were noticeably weakened.

Now back to the profiling ...

+1
source

What is the first thing you look at?

Profiler.

+10
source

The first thing I would like to do is get a copy of the application > dotTrace and use it to profile the site. Don’t make assumptions about where the performance problem is - go find out!

+6
source

The first thing I would like to see is the database, make sure there are indexes where the indexes should be.

+5
source

Everything can be a problem. Take a page that behaves badly and tracks the flow of control. Use the sql debugger and profiler and see EVERYTHING that this page does. It should be clear what to fix.

Remember to get started with the page, and users are disappointed that they are slow. Don't worry about other pages (like the login page, which is almost never a problem.)

Of course, with experience, you can just look at the codes (DB + .NET) and find out what the problem is, but you have no experience. So another option is to hire a consultant who has experience.

Edit - My hunch (I want a cookie)

  • If it's AJAX, it could be JavaScript
  • Otherwise, it is most likely TSQL
  • If the programmer did not know how to use TSQL and simply pulled out entire database tables and processed the data in C #
  • or the programmer did not understand the page model (or the MVC model), and this is the most bizarre code you've ever seen in your life.

I see it all.

+4
source

Using a profiler is a good way to measure performance, but difficult if you are not doing reasonable performance emulation.

Most supported web application databases are I / O, not computationally related. Lack of a profiler, I would guess about query performance. You can improve query performance by running the explain query game to find out which indexes you need. Or you don’t need to optimize the query by putting the result sets / active records in the cache.

+1
source

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

Troubleshoot performance issues in SQL Server 2005

But I would not be surprised if you find inefficient client code. Especially when communicating with the database.

+1
source

I voted for problems with locking the database from my own experience.

0
source

More articles:


All Articles