Most SQL users flunk performance test

When it comes to optimizing database queries, a new study suggests that most programmers could use a refresher course

Image of two switches, each with the word 'FlunkImage credit: flickr/Russell Trow
Can you pass the SQL optimization test?

If you're a programmer, odds are you've had to write code at some point that interacts with a database, which means you've had to write SQL to query data from it. Inefficient queries are often a leading cause of poor application performance, so knowing how to write SQL that performs its task as quickly as possible is critical. Regardless of what programming language you primarily use, and how well you know it, if you don't know how to write efficient SQL, your application performance can suffer.

But how much does the average programmer know about writing good SQL? Based on the results of a recent study, not so much.

Markus Winand is a consultant on SQL performance tuning who offers tips on his website Use The Index, Luke!. Since 2011, more than 28,000 people have taken his 3-Minute Test to check how well they know SQL optimization. It's a short, five question test, meant, primarily, to see how well people know how to use indexes. He recently published the overall performance results and his findings indicate that a significant number of programmers could use a refresher course on SQL performance tuning basics.

Keeping in mind that 4 of the 5 questions had only two answers to choose from (good SQL practice or bad), meaning that the odds of simply guessing the right answer were 50%, Winand found that:

  • 33% of respondents didn't know that that applying functions to columns in a where clause effectively disables indexes

  • 38% failed to demonstrate the proper knowledge of how multi-column indexes work

  • 61% of respondents didn't understand index-only scans

  • Overall, only 38% of respondents passed the test, defined as answering at least 4 of the 5 questions correctly

  • PostgresSQL users did the best, with 50% of those respondents passing, followed by Oracle and SQL Server users (38% of each group passed) and MySQL users coming in last in SQL performance knowledge, with only 33% passing

Read his post for the full results, or download the raw data yourself to dig in further.

Of course, this is a very non-scientific study. For example, people may be going to Winand's site in the first place because they recognize that their SQL knowledge isn't so great. Still, given the fairly large sample size, the results are a little surprising. My guess is that these findings do indicate that the average programmer doesn't understand SQL optimization as well as he or she should. It's an easy thing to overlook or to take for granted.

To be fair, in order to use indexes properly, the indexes first have to exist. As Winand points out, that's where the worlds of developers and DBAs intersect. In order for SQL to be properly optimized, developers and admins must work together to ensure that the correct indexes are created and that the queries are written to use them. So, SQL performance issues aren't all on programmers.

But, still, developers should have a good working knowledge of how to use database indexes. Based on these results, it seems as if too many don't. If you're a programmer who deals with SQL at all, it probably wouldn't be a bad idea to brush up on how to use indexes and general query tuning, if you haven't in a while. Your application's users (or even your DBA) might thank you.

Read more of Phil Johnson's #Tech blog and follow the latest IT news at ITworld. Follow Phil on Twitter at @itwphiljohnson. For the latest IT news, analysis and how-tos, follow ITworld on Twitter and Facebook.

ITWorld DealPost: The best in tech deals and discounts.
Shop Tech Products at Amazon