What Are Some Performance Pitfalls Effecting Your Website?

At A2 Hosting we’ve been doing a lot of performance optimization on the server side, to make sure our customers’ sites run at their best. But of course, as developers we can always improve our application’s performance too. I thought I’d go over some potential low hanging fruit to look out for in your own applications.

Large Un-indexed SQL Columns

This is a common source of poor performance in web applications. Querying on a large SQL database table can be very fast if you are querying on indexed column(s). But try querying on some unindexed columns and suddenly the response time drops massively; and the more of these columns in your query the worse the situation becomes. If you know a table is likely to grow large, and it gets hit often or at key times when performance will impact end users, it’s time to add an index to those columns. The cost of an index is usually just disk and memory footprint, and nowadays those are both affordable resources. That said, indexing every column in your database is a mistake as well. It should be obvious which columns are most likely to be queried on frequently and those are your best index targets.

Inefficient or Unnecessary Nested Loops

Everybody know nested loops can quickly become a bottle neck, and most developers will try to stop at 2 layers of looping. But sometimes you’ll see people fall into the trap of calling a method or function which itself contains more nested loops. This is not improving the situation. Often times these are cases where there is a looping structure which is calling some function to iterate over some data and return it to the calling loops. If at all possible — and you’re not working within some tight memory constraints — it’s probably best to build up all your data first and then iterate over it once, rather than having these kinds of heavily nested looping structures. Often times passing over all the data once will be much faster than having a nested loop where you build up a chunk of the data and then do a pass over that portion, as long as you have memory available to do so.

Overly Broad Queries

In contrast to the last step, memory use can become an issue on any application with high concurrency. If you have 100 or 1,000 instances of the web application executing at once, each instance using 64MB of memory can quickly become unsustainable and cause massive pagefile hits for many or all users. Generally any query that starts out with “SELECT * FROM” is probably overly broad and you can start pruning from there.

As I said at the outset, these are all pretty low hanging fruit. Hopefully they’re things you’ve had in mind already, and maybe you’ll just be prompted to refactor that function that you were already thinking about. It’s always wonderful when you make a single change and see a notable performance improvement immediately, and often times it’s the simplest and most obvious changes that give you that satisfaction.