CodeSOD: Set Your Performance Target
The power of SQL is that you describe to the database what you want, and the database figures out how to execute that query as efficiently as possible. This means that, at least in theory, you optimize your database access not by changing the query, but by tuning the database to run that query efficiently.
In practice, every database has quirks, and frequently you do tune the query a bit, to trick the optimizer into running it efficiently. And sometimes, you have to modify the query because people are dumb.
Jakard was tracking down a performance issue in the database. There was one query that was taking over 30 minutes to produce less than 30 records. That seemed bad, so Jakard took a look at the query.
select distinct * from( select distinct Category, Name from CategoryStorageA union select distinct Category, Name from CategoryStorageB)
For reasons unknown to us, the various categories for products were split across two tables. In both tables, Category is the primary key, but there's no guarantee of uniqueness between the two tables.
With that information, it's easy to see that whoever originally wrote this query either didn't understand what a primary key is, what the distinct operator does, or what a union actually is.
The same data could be retrieved much more directly:
select Category, Namefrom CategoryStorageAunionselect Category, Namefrom CategoryStorageB
The fact that Category is the primary key guarantees that each individual query has no duplicate rows. And the union is a basic set operation- it also guarantees uniqueness. By removing the superfluous distincts and the wrapper query which was definitely throwing off the optimizer, the 30 minute runtime dropped to fractions of a second.
The good news is that this was an easy fix. The bad news is that it gave Jakard a reputation as a performance wizard, who could, in a few minutes, fix any badly performing query and convert it to something blazingly fast. And the really bad news: many of the other performance problems are similar misunderstandings of set theory or basic database operations, which are relatively easy to fix, meaning Jakard's reputation as a wizard has only grown since fixing this.
[Advertisement] Otter - Provision your servers automatically without ever needing to log-in to a command prompt. Get started today!