7 performance tips for faster SQL queries

InfoWorld - SQL developers on every platform are struggling, seemingly stuck in a DO WHILE loop that makes them repeat the same mistakes again and again. That's because the database field is still relatively immature. Sure, vendors are making some strides, but they continue to grapple with the bigger issues. Concurrency, resource management, space management, and speed still plague SQL developers whether they're coding on SQL Server, Oracle, DB2, Sybase, MySQL, or any other relational platform.

Part of the problem is that there is no magic bullet, and for almost every best practice, I can show you at least one exception. Typically, a developer finds his or her own favorite methods -- though usually they don't include any constructs for performance or concurrency -- and doesn't bother exploring other options. Maybe that's a symptom of lack of education, or the developers are just too close to the process to recognize when they're doing something wrong. Maybe the query runs well on a local set of test data but fails miserably on the production system.

[ Read Sean McCown's Database Underground blog. | Stay up to date on data management issuws in InfoWorld's Technology: Data Management newsletter. | And keep up on software developments trends in our Developer World newsletter. ]

I don't expect SQL developers to become administrators, but they must take production issues into account when writing their code. If they don't do it during initial development, the DBAs will just make them go back and do it later -- and the users suffer in the interim.

There's a reason why we say tuning a database is both an art and a science. It's because very few hard-and-fast rules exist that apply across the board. The problems you've solved on one system aren't issues on another, and vice versa. There's no right answer when it comes to tuning queries, but that doesn't mean you should give up.

There are some good principles you can follow that should yield results in one combination or another. I've encapsulated them in a list of SQL dos and don'ts that often get overlooked or are hard to spot. These techniques should give you a little more insight into the minds of your DBAs, as well as the ability to start thinking of processes in a production-oriented way.

1. Don't use UPDATE instead of CASE This issue is very common, and though it's not hard to spot, many developers often overlook it because using UPDATE has a natural flow that seems logical.

Take this scenario, for instance: You're inserting data into a temp table and need it to display a certain value if another value exists. Maybe you're pulling from the Customer table and you want anyone with more than $100,000 in orders to be labeled as "Preferred." Thus, you insert the data into the table and run an UPDATE statement to set the CustomerRank column to "Preferred" for anyone who has more than $100,000 in orders. The problem is that the UPDATE statement is logged, which means it has to write twice for every single write to the table. The way around this, of course, is to use an inline CASE statement in the SQL query itself. This tests every row for the order amount condition and sets the "Preferred" label before it's written to the table. The performance increase can be staggering.