SQL unleashed: 17 ways to speed your SQL queries

[Blu3HaSky.]

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.

 

[ From InfoWorld’s experts: What is SQL? The language of databases explained. | Database slow? Improve the speed and scalability of your RDBMS with these 21 rules for faster SQL queries. • 7 essential SQL Server security tips. ]

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.