danbirken.com

home

Quick tip: Improve developer habits by showing time cost of DB queries

16 Apr 2014

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:

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):

Request Details:
DB - Queries: 4, Time: 5.66 ms

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

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)