Comment your SQL queries to track down performance hogs!

Here’s a tip…

Start your SQL queries with a comment that uniquely identifies the query or its source. For example, I put lots of SQL queries on wiki pages, so I include the Unique ID of the wiki page:

-- Page 19323
select id from customer
where ...

Then if the SQL Query runs wild, I can check the commands running on my server and the Page ID is visible at the start of the query. I can then track down the bad SQL!

Outer Joins are faster than INs

I had a situation where I had to count Trial Licenses based on whether the person is an existing Customer.

Think of it as two tables:

• license table
• customer table

For each license, I had to lookup the customer table, with counts of how many matched and didn’t match.

As a first pass, I did something like this:

select
  to_char(created, 'YYYY-MM') as month,
  sum(case when owner in (select owner from customer) then 1 else 0 end) as "customer count",
  sum(case when owner in (select owner from customer) then 0 else 1 end) as "non-customer count",
  count(*) as "total"
from license

Performance was bad because of the repeated calls to the customer table. I could have split it into two queries so that the sub-select went into the WHERE, but I’d have to stitch the results back together since I wanted a count of both customers and non-customers.

Then I tried it via a LEFT OUTER JOIN, something like this:

select
  to_char(created, 'YYYY-MM') as month,
  count(customers) as "customer count",
  count(*) - count(customers) as "non-customer count"
  count(*) as "total"
from license LEFT OUTER JOIN customer using owner

Performance improved dramatically! A JOIN works so much faster than sub-selects, at least in PostgreSQL.