Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

10 more do's and don'ts for faster SQL queries

Sean McCown | Sept. 18, 2014
Everyone wants faster database queries, and both SQL developers and DBAs can turn to many time-tested methods to achieve that goal. Unfortunately, no single method is foolproof or ironclad. But even if there is no right answer to tuning every query, there are plenty of proven do's and don'ts to help light the way. While some are RDBMS-specific, most of these tips apply to any relational database.

Everyone wants faster database queries, and both SQL developers and DBAs can turn to many time-tested methods to achieve that goal. Unfortunately, no single method is foolproof or ironclad. But even if there is no right answer to tuning every query, there are plenty of proven do's and don'ts to help light the way. While some are RDBMS-specific, most of these tips apply to any relational database.

My previous collection of SQL do's and don'ts concentrated on individual query performance. While you'll definitely find more of that here, the main focus is on increasing concurrency in your system. By minimizing locking, I/O, and network traffic, you can not only make your queries run much faster, but even more important, you can make queries behave themselves on a system with hundreds or even thousands of concurrent users.

1. Do use temp tables to improve cursor performance

I hope we all know by now that it's best to stay away from cursors if at all possible. Cursors not only suffer from speed problems, which in itself can be an issue with many operations, but they can also cause your operation to block other operations for a lot longer than is necessary. This greatly decreases concurrency in your system.

However, you can't always avoid using cursors, and when those times arise, you may be able to get away from cursor-induced performance issues by doing the cursor operations against a temp table instead. Take, for example, a cursor that goes through a table and updates a couple of columns based on some comparison results. Instead of doing the comparison against the live table, you may be able to put that data into a temp table and do the comparison against that instead. Then you have a single UPDATE statement against the live table that's much smaller and holds locks only for a short time.

Sniping your data modifications like this can greatly increase concurrency. I'll finish by saying you almost never need to use a cursor. There's almost always a set-based solution; you need to learn to see it.

2. Don't nest views

Views can be convenient, but you need to be careful when using them. While views can help to obscure large queries from users and to standardize data access, you can easily find yourself in a situation where you have views that call views that call views that call views. This is called nesting views, and it can cause severe performance issues, particularly in two ways. First, you will very likely have much more data coming back than you need. Second, the query optimizer will give up and return a bad query plan.

 

1  2  3  4  5  6  Next Page 

Sign up for CIO Asia eNewsletters.