This is the first in a series of posts where I share a quick, easy to implement tip that I’ve found useful in my development career

Websites can be slow for many reasons, but an overwhelmingly common one is time spent performing database queries. Now database queries are likely a very important part of rendering the webpage – the information to populate a page has to come from somewhere. But sometimes rendering a page is slowed down by either unnecessary queries or improperly optimized queries.

Examples:

• Querying data that isn’t used anywhere
• Querying data not using an index
• Performing multiple queries where one faster one would suffice
• Slow, large queries

But there is a simple mechanism that can indirectly help prevent and cure both of these issues:

Display the number of DB queries, and the total DB time in a diagnostic box below every page.

Here is an example diagnostic block that appears on the bottom of one of my sites (screenshot in the wild for this page):

I enable this diagnotic box on all development instances, and on production when I’m logged in. For a larger site, you might want to enable it when you are accessing the site from your office or VPN. The diagnostic box could also include more specific information if you’d like (the specific queries which are begin run, time spent in other places, etc).

Benefits

• I can instantly see if a slow page load is due to the database or not. This is especially helpful when debugging things on production which might act differently than development instances.
• During development when I’m adding features, I instantly get a feel for whether the queries pulling the data are lightweight or not. This helps me weigh the benefits of the feature versus the time cost of accessing the data. It also is a good warning for queries that I might need to manually optimize or cache.
• When I’m working with sites that use an ORM, it helps give me an indication that the ORM is generating the types of queries that I’m expecting.
• When I remove features or move a particular data fetch to a cache, I can confirm that the number of queries is going down as I expect

Final word

I certainly did not invent this idea, and some type of a diagnostic mode is a common feature on almost any large site. But if you don’t have it, I highly recommend taking the time to implement it. Having a diagnostic box wont make your site faster on its own, but it will encourage better habits among developers and over the long term can have a drastic effect on site speed.

For those interested, here is my query counting code for sqlalchemy that I use for the above site.

» Discuss on hacker news (also a bunch of other open source options for common frameworks are being posted in the comments)