Foliotek Developer Blog

Page Level Web Application Database Optimization

Do you know what queries are required to run for a single page load in your application? If not, perhaps you should.

That is a great question I wish I would have asked myself a long time ago, but didn’t know to ask it.

Over the past several years, I have enjoyed the challenge of database query optimization. I work for a small company, and no one else seems to have much of an interest in this sort of thing, so that means I get to do it. It is certainly interesting to see how a small rewrite of a query or a new index can achieve significant results. However, I recently began looking at optimization from a different perspective–that of the individual page.

How many queries are required to load a single page? How many should it take?

To identify expensive queries that need optimization, I ran a SQL Profiler session that would catch queries with Reads greater than 1000, 5000 or maybe 10000. Or maybe more than 500 CPU. However, one day I decided to see all the queries that ran on a given page load, and I set the Read threshold all the way down to 1. I also set up the web application running on my machine to point to a test database and restricted the Profiler session accordingly.

From SQL Server Management Studio:

  • Tools > SQL Server Profiler
  • Enter Database credentials, then click Connect
  • Event Selection tab
  • Check “Show all events” and “Show all columns”
  • Column Filters - DatabaseName: TestDBName
  • HostName: ComputerName (ws###)
  • Reads: 1
  • Run

After clicking “run” and loading the first page, I was literally stunned.

One page was running far more queries than I had ever imagined. But why? I started looking through the results. The column “TextData” shows the first 100 or so characters, and then the window on the bottom shows the full query. Suffice it to say that there were many opportunities for optimization.

What I Learned

Repetitive Queries

The first thing I noticed was that on many of the page loads, there were many redundant queries. The page would query the database for the User or Company multiple times, sometimes one right after the other. None of this was intended. The impact of certain ways of doing things, however, was never realized in terms of the load on the database.

One of the biggest things to optimize are things like side menus, top menus, or footers–things that are displayed on every page. Any inefficiencies in these will be hit every time anyone loads any page. That also means, though, that any increased inefficiency can have a huge impact. If 100 pages are hit every second, and there are 20 queries that can be eliminated from each page load, you have just eliminated 1000 queries a second.

Many of these queries are small, to be sure. It takes very little if any CPU and only a few reads to retrieve a User from the User table. However, the cumulative increased traffic can be significant.

In many cases, I realized that I could just pass the user to a method that accessed a particular property on the row instead of getting the user inside the method itself. Or I could just pass in the property itself. Alternatively, I could create a page-level (or control-level [.ascx]) variable for User or Company or other tables used in many places.

private DataAccess.User _currentUser;  
public DataAccess.UserCurrentOrganization  
{  
    get  
    {  
        if (_currentUser== null)  
            _currentUser= DataAccess.User.Get(LoggedInUserID);  
        return _currentUser;  
    }
}

Unused Queries

It took some time, but I matched every query to the line of code that generated it. I found a few places where a query was called, but then the result was never used. This, of course, is bound to happen in a large application as modifications are introduced. But again, removing these for every page load can certainly help.

Unnecessary Queries

I also encountered a few places where unnecessary queries were being run. For instance, upon login, the system should check the expiration date of the user’s registration. That was the intent of the code. However, at some point, the code was moved and ended up getting run on every page load. So, the code was necessary on login, but not on every page. I never would have known it ran on every page had I not seen it for myself in the SQL Profiler session.

Final Thoughts

It is, indeed, rewarding to identify a very expensive query and optimize it down to something very small. Yet, I’ve learned that there are not only gigantic weeds. Sometimes there are millions of smaller weeds. It is better to get rid of both.