How to write terrible SQL

Whether you want to ruin a rival’s database latency or just win a “SQL horror” contest, here’s how you can become the best at being the worst. Part 1 of a series.

Use a non-sargable WHERE clause

Removing indexes is child’s play, and furthermore is probably something only your database administrator can do, so let’s start somewhere not so obvious: making the index useless! One easy way to do this is use a column you’re filtering on in an expression. For example:

‍

Original SQL:

‍

WHERE start_date BETWEEN DATE ‘2026-01-01’ AND DATE ‘2026-12-31’

‍

Pessimized SQL:

‍

WHERE YEAR(start_date) = 2026

‍

The second WHERE clause requires a full table scan to find the matching rows and, assuming the start_date column is indexed, can be orders of magnitude slower than the first version. What's going on here?

‍

A column index helps the database quickly locate rows where a column has a particular value, or a range of values. The original query matches on actual date values that might be found in the column. The pessimized query uses year values derived from the date values in the column. The year values are not stored in the index and can’t be used to locate rows that match the query. Every row of the table must be read to find the rows having dates in the right year.

‍

As a bonus, this works on Snowflake too, where you otherwise don’t have access to indexes.

Undoing the pessimization

How you can you fix non-sragable queries? The first and most obvious way is to rewrite them so they're sargable. We've already seen how to do that in one case by checking to see whether a date is BETWEEN two dates rather than whether the YEAR function returns a certain value on the column.

‍

Another approach is to rearrange the terms of a comparison algebraically so that the column name is alone on one side of the comparison. For example, if you're testing whether x * 3 < 100, move the math to the right side by dividing each side by 3 to get x < 33.3.

‍

Sometimes there's no way to avoid performing a calculation on a column. Consider the following query:

‍

SELECT store_id, SUM(sale_total)

WHERE MONTH(transaction_date) = 5

GROUP BY store_id;

‍

You can't really work around the MONTH function call. You could write this test as a long chain of BETWEEN comparisons (using OR or UNION ALL), one to select the desired month in each year, but you need one BETWEEN test for each year in the data, and each year you'd need to update the query. 

‍

The most performant way to handle this type of query is to add another column specifically for the calculated value, say transaction_month, and calculate it from transaction_date when you insert a row. If you ever update the transaction date, you'll need to update transaction_month then, too. With the transaction_month column in your table, you can index it, then write your queries so they test against it instead of MONTH(transaction_date).

‍

If you write queries like this often, the performance improvement may be worth the marginal storage cost. (Compute cost is negligible for the MONTH function, but might be a factor in other calculations.) Remember, it's not the fact that you're eliminating the function call that gets you most of the performance back; it's making the query sargable so the index can be used.

‍

If you can't add columns to the table, you can still employ this approach using a materialized view or a dynamic table. Both of these table-like objects can calculate the value you actually want to be able to search and store it in its own indexed column, providing a similar performance benefit for queries.

‍

• Use a materialized view when you need the data always to be up–to-date with the base table. Snowflake updates the materialized view whenever the underlying table changes.
• Use a dynamic table when it's OK for the data to be stale. For example, a sales report that only considers past months does not need any data from the current month, so the dynamic table it uses can be refreshed at the beginning of each month.

‍

Both of these approaches result in additional compute and storage cost. To minimize storage cost, store only a key and the calculated column(s) in the materialized view or dynamic table, then join the view or table back to the original table to get the calculated column. The join is faster than the full table scan required by a non-sargable query term.

‍

When all else fails, and the WHERE clause contains more than one term, structure the query so sargable terms are evaluated before non-sargable ones. This way, at least only the rows selected by the sargable terms need to be queried to evaluate the non-sargable terms. If you're combining terms with AND, the query optimizer will arrange the query in the most efficient order (use EXPLAIN to make sure). To force the order, if this is necessary for some reason, you can use a subquery or a CTE.

Conclusion

Sargability is one of the most important factors in query performance—and one of the easiest to overlook. A query that hides its columns inside functions or other expressions may still produce the correct result, yet fail to deliver the performance you need. The best queries make it easy for the optimizer to do the right thing; the next best make the necessary tradeoffs explicit. So when a query is slow, ask whether you may be making the database work harder than it should, and whether there's a better way.

‍