We’ve all been frustrated by slow sites. We’ve all been to sites that would be great, if only they could render a page before I finish a game or two of solitaire. Many of us have also been on the other side; spending hours optimizing our sites just to cut the rendering time by a tenth of a second.

Developers, I’ve got some good news. Turn off YSlow and leave your javascript readable!  Stop debating between concatenating strings or using stringbuilders! BLAME SQL!

In many cases, I see developers ignoring SQL optimization. It may be because they’re more familiar with client-side tech or it could be the years of memorizing every design pattern they can, but almost every web application can benefit from a little TLC. Here are a few quick pointers.

1. Know what your SQL looks like.

Web developers rely on a lot of different tools and applications. Whether you’re using a large application like a CMS or a smaller ORM tool, it’s important to know what SQL your code is generating. In many cases, the SQL generated is optimized with only the most common use cases in mind or in some cases barely optimized at all. Don’t be afraid to peak at the stored procs your apps are using or do some logging to what your code is actually doing to your poor database. Just because you didn’t write it, doesn’t mean you shouldn’t know what it does.

2. Add some indexes

Having a basic understanding of indexes can help improve performance immensely.  To start off, make sure your tables have at least a Primary Key. Next, look at any columns you regularly use to filter selects or join on. Unless these column contain a lot of similar values, it’s a good idea to index them. Finally, if you frequently select a subset of data from a table, such as first and last name from a contacts table, you can create a covering index that will usually speed up these queries.

3. Look at your joins

 If you need a map to find your way through the maze of nasty joins in your queries, it’s time to optimize. First of all, make sure that you’re only joining to get data that you’re actually using. Joins can exponentially increase the time it takes to perform a query, so joining to get data that isn’t used wastes a lot. Also, when joining, it’s best to join on indexes. It’s also better join on numeric types than non-numeric types.

4. Subqueries are usually bad

This is a short one. If you’ve got subqueries, figure out how to make them joins. There are almost no cases when a subquery is more efficient than a join, and if you know those cases, you probably don’t need to take SQL advice from me.

5. Use stored procs for common queries

Stored procedures provide several performance improvements. Stored procs are partially compiled at creation and don’t need to generate a new execution plan at runtime. Additionally, combining statements into a stored proc can provide details to SQL Server that allow it to create a more efficient execution plan than passing individual queries. It may be tempting to just sling SQL at the database, but even a few stored procs can do wonders.