6. Do pre-stage data
This is one of my favorite topics because it's an old technique that's often overlooked. If you have a report or a procedure (or better yet, a set of them) that will do similar joins to large tables, it can be a benefit for you to pre-stage the data by joining the tables ahead of time and persisting them into a table. Now the reports can run against that pre-staged table and avoid the large join.
You're not always able to use this technique, but when you can, you'll find it is an excellent way to save server resources.
Note that many developers get around this join problem by concentrating on the query itself and creating a view-only around the join so that they don't have to type the join conditions again and again. But the problem with this approach is that the query still runs for every report that needs it. By pre-staging the data, you run the join just once (say, 10 minutes before the reports) and everyone else avoids the big join. I can't tell you how much I love this technique; in most environments, there are popular tables that get joined all the time, so there's no reason why they can't be pre-staged.
7. Do delete and update in batches
Here's another easy technique that gets overlooked a lot. Deleting or updating large amounts of data from huge tables can be a nightmare if you don't do it right. The problem is that both of these statements run as a single transaction, and if you need to kill them or if something happens to the system while they're working, the system has to roll back the entire transaction. This can take a very long time. These operations can also block other transactions for their duration, essentially bottlenecking the system.
The solution is to do deletes or updates in smaller batches. This solves your problem in a couple ways. First, if the transaction gets killed for whatever reason, it only has a small number of rows to roll back, so the database returns online much quicker. Second, while the smaller batches are committing to disk, others can sneak in and do some work, so concurrency is greatly enhanced.
Along these lines, many developers have it stuck in their heads that these delete and update operations must be completed the same day. That's not always true, especially if you're archiving. You can stretch that operation out as long as you need to, and the smaller batches help accomplish that. If you can take longer to do these intensive operations, spend the extra time and don't bring your system down.
Enjoy faster SQL
Follow these dos and don'ts whenever you can when writing queries or processes to improve your SQL performance, but remember to evaluate each situation individually to see which method works best -- there are no ironclad solutions. You'll also find that many of these tips will increase your concurrency and generally keep things moving more smoothly. And note that while the physical implementation of these tips will change from one vendor to the next, the concepts and issues that they address exist in every SQL platform.
Jennifer McCown contributed to this article.
This article, "7 performance tips for faster SQL queries," was originally published at InfoWorld.com. Read Sean McCown's Database Underground blog and follow the latest developments in data management at InfoWorld.com.
Read more about data management in InfoWorld's Data Management Channel.