Set performance goals

The beauty of SQL is that you write what you want to a database, and the database figures out how to execute that query as efficiently as possible. This is, at least in theory, the best way to optimize database access. queryHowever, the database must be tuned to run that query efficiently.

In reality, every database has its quirks, and often do Tweak your queries slightly to trick the optimizer into executing them efficiently. Also, sometimes you have to change your query because people are stupid.

jacquard I was tracking performance issues within my database. There was one query that took more than 30 minutes to produce less than 30 records. That didn’t seem right, so Jakard looked into the query.

select distinct * from
(
  select distinct Category, Name
    from CategoryStorageA
  union
  select distinct Category, Name
    from CategoryStorageB
)

For unknown reasons, different categories of products have been split into two tables. on both tables, Category is a primary key, but uniqueness between the two tables is not guaranteed.

From this information, it’s easy to see that whoever originally created this query didn’t understand what a primary key was, what the distinct operator did, or what a union actually was. .

You can also retrieve the same data more directly.

select Category, Name
	from CategoryStorageA
union
select Category, Name
	from CategoryStorageB

fact Category Primary keys ensure that there are no duplicate rows in individual queries.And that union This is a basic set operation. Also Guarantees uniqueness. By removing excess distinctAnd a wrapper query that definitely disabled the optimizer reduced execution time from 30 minutes to a fraction of a second.

Fortunately, this was an easy fix. The bad news is that he has given Jakard a reputation as a performance wizard who can fix poorly performing queries in minutes and turn them into blazingly fast ones. And the really bad news. Many other performance problems are similar misunderstandings of set theory and basic database operations. teeth It’s relatively easy to fix. So since we fixed this issue, Jakard’s reputation as a wizard has grown even more.