"When handling large CASE WHEN statements, it is better to create a dimension table or view, ideally sourced from the landed table where the original status column is populated."
Is this code for 'use a lookup table' or am I falling behind on the terminology? The modern term should be 'sum table' or something similar surely.
We did the views on view thing once when triggers, at least how we implemented them failed. This became a huge regret that we lived with for years and not-so affectionately called "view mountain". We finally slayed viewed mountain over the last 2 years and it feels so good.
When working with larger enterprise software, it is common to have large CASE WHEN statements translating application status codes into plain English. For example, status code 1 could mean the item is out of stock.
Why wouldn’t you store this information in a table and query it when you need it? What if you need to support other languages? With a table you can just add more columns for more languages!
The section of using functions on indexes could do with more explicit and deeper explanation. When you use the function on the index it becomes a full scan of the data instead as the query runner has to run the function on every row and column, effectively removing any benefit of the index.
Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.
I'm not sure I understand the part about set theory. If anything, a valid use of DISTINCT is if you want the result to be (closer to) a set, as otherwise (to your point, depending on the data model) you may get a bag instead.
So how do you "know" when you can safely omit DISTINCT for your shiny new query SELECT x FROM t ?
Oh you looked the schema for t and it said x has a PRIMARY or UNIQUE constraint?
Ah well two minutes after you looked at the schema Tom removed the UNIQUE constraint. Now your scratching your head when you get duplicates.
Sql is a bag language not a set language. The contract with relation t is that if the runtime can find there rel t and attribute x it will return it. You may end up with rows or not, and you may end up with duplicates or not, and the type of x may change between subsequent execution.
So if you want a set you need to say so using DISTINCT. At runtime the query planner will check the schema and if the attribute is UNIQUE or PRIMARY it will not have to do a deduplication.
If „select *“ breaks your code, then there‘s something wrong with your code. I think Rich Hickey talked about this. Providing more than is needed should never be a breaking change.
Certain languages, formats and tools do this correctly by default. For the others you need a source of truth that you generate from.
> three or four layers of subqueries, each one filtering or aggregating the results of the previous one, totaling over 5000 lines of code
In a better language, this would be a pipeline. Pipelines are conceptually simple but annoying to debug, compared to putting intermediate results in a variable or file. Are there any debuggers that let you look at intermediate results of pipelines without modifying the code?
I wrote some tooling to help debug sql queries with many CTEs. It parses the sql, finds all the CTEs, and prints the result of each CTE formatted as csv. If the .sql file changes on disk, it reruns the query and tells you which CTEs’ output changed. Saved me hours in debugging.
I don't know about anti patterns but what I like to do is putting 1=1 after each WHERE to align ANDs nicely and this is enough to create huge dramas in PR reviews.
I agree. Modern code models tend to do a great job advising in SQL, especially if you include the table definition and EXPLAIN output in the context. Alternatively, I've found that an EXPLAIN MCP tool works well.
Some of these things happen because people try to come up with a single clever query that does everything at once and returns a perfect spreadsheet.
Translating status codes into English or some other natural language? That's better done in the application, not the database. Maybe even leave it to the frontend if you have one. As a rule of thumb, any transformation that does not affect which rows are returned can be applied in another layer after those rows have been returned. Just because you know SQL doesn't mean you have to do everything in SQL.
Deeply nested subqueries? You might want to split that up into simpler queries. There's nothing shameful about throwing three stones to kill three birds, as long as you don't fall into the 1+N pattern. Whoever has to maintain your code will thank you for not trying to be too clever.
Also, a series of simple queries often run faster than a single large query, because there's a limit to how well the query planner can optimize an excessively complicated statement. With proper use of transactions, you shouldn't have to worry about the data changing under your feet as you make these queries.
I wrote a small tutorial (~9000 words in two parts) on how to design complicated queries so that they don't need DISTINCT and are basically correct by construction.
The biggest SQL antipattern is failing to recognize that SQL is actually a programming language.
Therefore you should create a consistent indentation style for SQL. See https://bentilly.blogspot.com/2011/02/sql-formatting-style.h... for mine. Second, you should try to group logical things together. This is why people should move subqueries into common table expressions. And finally, don't be afraid of commenting wisely.
These "anti-patterns" are just workarounds for bad language design of SQL (or lack of design actually). I'm working on a language that can run on SQL databases, so I hope it will do better with every one of these points.
If anyone wants to check out a half-done lang with lacking documentation, I'd be happy to read your feedback: https://lutra-lang.org
"SQL database" doesn't describe anything. Variations of SQL have implementations on relational and non-relational databases. SQL and relational often get used interchangeably but given your goal you might want to use the terms more precisely.
Experts including Codd recognized the problems with SQL since that language got traction. Some alternatives got proposed, perhaps most notably Tutorial D by Chris Date and Hugh Darwen. No SQL replacement goes anywhere because of the vast quantity of SQL code and supporting tools dating back decades. Chris Date wrote the textbook on databases, and at least one book going through the problems with SQL and various implementations of the relational model.
SQL perfectly illustrates what Strostrup meant by "There are only two kinds of languages: the ones people complain about and the ones nobody uses." In some sense I would welcome a better query language. On the other hand I attribute decades of job security and steady income to knowing SQL and dealing with its problems.
User Defined Functions (UDFs) are another option to consolidate the logic in one place.
> Using Functions on Indexed Columns
In other words, the query is not sargable [0]
> Overusing DISTINCT to “Fix” Duplicates
Orthogonal to author's point about dealing with fanout from joins, I'm a fan of using something like this for 'de-duping' records that aren't exact matches in order to conform the output to the table grain:
ROW_NUMBER() OVER (PARTITION BY <grain> ORDER BY <deterministic sort>) = 1
Some database engines have QUALIFY [1], which lends itself to a fairly clean query.
I can’t take any article like this seriously if it doesn’t lead with the #1 sql antipattern which kills performance all the time - doing things row-by-row instead of understanding that databases operate on relations, so you need to do operations over whole relations.
Very often I have seen this problem buried in code design and it always sucks. Sometimes an orm obscures this but the basic antipattern looks like
Select some stuff
For each row in stuff:
… do some important things …
Select a thing to do with this row
… maybe do some other things …
Early on in my career an old-hand sql guru said to me “any time you are doing sql in a loop, you are probably doing it wrong”.
The non-sucky version of the code above is
Select some stuff, joining on all the things you need for the rows because databases are great
For each row in stuff:
… do some important things …
… maybe do some other things …
I've built myself a few problems that I haven't fixed yet:
Many materialized views that rely on materialized views. When one at the bottom, or a table, needs a changed all views need to be dropped and recreated.
Using a warm standby for production. I love having a read only production database, but since it's not the primary, it always feels like it's on the losing end of the system. Recently upgraded to Postgres 18 and forgot that means I need to rm rf the standby and pg_basebackup to rebuild... That wasn't fun.
Why do you need a warm standby for production? Do you need >= 3 nines?
Our staging environment has its own instance that is rebuilt from prod, with pii removed, every day outside working hours (this normally takes about 15 minutes). It’s fantastic for testing migrations, and is easy to support compared with a warm standby.
I'm really curious, what communities use that word?
I've been working with SQL for 20+ years, and have literally never come across that word a single time in any documentation, tutorial, Stack Overflow answer, or here on HN. Working in Postgres, MySQL and SQLite.
Is it used at some particular company, or open source community, or with a particular database, or something?
I'm sceptical of that article, it's making guesses about the limitations of SQL query optimisers.
Consider the Simple example it presents. The article is in effect implying that no query optimiser would be able to figure out the equivalence of the two predicates.
(Let's ignore that the two predicates aren't actually equivalent; the first version may raise an exception if myIntColumn is negative, depending on the DBMS.)
44 comments
[ 3.3 ms ] story [ 78.4 ms ] threadIs this code for 'use a lookup table' or am I falling behind on the terminology? The modern term should be 'sum table' or something similar surely.
Why wouldn’t you store this information in a table and query it when you need it? What if you need to support other languages? With a table you can just add more columns for more languages!
query WHERE name = ‘abc’
create an indexed UPPER(name) column"
Should there be an "or" between these 2 points, or am I missing something? Why create an UPPER index column and not use it?
Unfortunately I learned this the hard way!
Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.
"given a BTreeMap<String, Vec<String>>, how do I do .keys() and .len()".
In fact, IIRC, using DISTINCT (usually bad for performance, btw) is an SQL advice by CJ Date in https://www.oreilly.com/library/view/sql-and-relational/9781...
Oh you looked the schema for t and it said x has a PRIMARY or UNIQUE constraint?
Ah well two minutes after you looked at the schema Tom removed the UNIQUE constraint. Now your scratching your head when you get duplicates.
Sql is a bag language not a set language. The contract with relation t is that if the runtime can find there rel t and attribute x it will return it. You may end up with rows or not, and you may end up with duplicates or not, and the type of x may change between subsequent execution.
So if you want a set you need to say so using DISTINCT. At runtime the query planner will check the schema and if the attribute is UNIQUE or PRIMARY it will not have to do a deduplication.
Certain languages, formats and tools do this correctly by default. For the others you need a source of truth that you generate from.
In a better language, this would be a pipeline. Pipelines are conceptually simple but annoying to debug, compared to putting intermediate results in a variable or file. Are there any debuggers that let you look at intermediate results of pipelines without modifying the code?
F# in the visual studio debugger does a pretty good job of this in recent versions.
Translating status codes into English or some other natural language? That's better done in the application, not the database. Maybe even leave it to the frontend if you have one. As a rule of thumb, any transformation that does not affect which rows are returned can be applied in another layer after those rows have been returned. Just because you know SQL doesn't mean you have to do everything in SQL.
Deeply nested subqueries? You might want to split that up into simpler queries. There's nothing shameful about throwing three stones to kill three birds, as long as you don't fall into the 1+N pattern. Whoever has to maintain your code will thank you for not trying to be too clever.
Also, a series of simple queries often run faster than a single large query, because there's a limit to how well the query planner can optimize an excessively complicated statement. With proper use of transactions, you shouldn't have to worry about the data changing under your feet as you make these queries.
I wrote a small tutorial (~9000 words in two parts) on how to design complicated queries so that they don't need DISTINCT and are basically correct by construction.
https://kb.databasedesignbook.com/posts/systematic-design-of...
Using != or NOT IN (...) is almost always going to be inefficient (but can be OK if other predicates have narrowed down the result set already).
Also, understand how your DB handles nulls. Are nulls and empty strings the same? Does null == null? Not all databases do this the same way.
Therefore you should create a consistent indentation style for SQL. See https://bentilly.blogspot.com/2011/02/sql-formatting-style.h... for mine. Second, you should try to group logical things together. This is why people should move subqueries into common table expressions. And finally, don't be afraid of commenting wisely.
If anyone wants to check out a half-done lang with lacking documentation, I'd be happy to read your feedback: https://lutra-lang.org
Experts including Codd recognized the problems with SQL since that language got traction. Some alternatives got proposed, perhaps most notably Tutorial D by Chris Date and Hugh Darwen. No SQL replacement goes anywhere because of the vast quantity of SQL code and supporting tools dating back decades. Chris Date wrote the textbook on databases, and at least one book going through the problems with SQL and various implementations of the relational model.
SQL perfectly illustrates what Strostrup meant by "There are only two kinds of languages: the ones people complain about and the ones nobody uses." In some sense I would welcome a better query language. On the other hand I attribute decades of job security and steady income to knowing SQL and dealing with its problems.
* Don't store UUIDs as strings.
* Don't use random UUID variants for your primary key (or don't use UUIDs for your primary key).
* Don't use a random column in your clustered index.
User Defined Functions (UDFs) are another option to consolidate the logic in one place.
> Using Functions on Indexed Columns
In other words, the query is not sargable [0]
> Overusing DISTINCT to “Fix” Duplicates
Orthogonal to author's point about dealing with fanout from joins, I'm a fan of using something like this for 'de-duping' records that aren't exact matches in order to conform the output to the table grain:
Some database engines have QUALIFY [1], which lends itself to a fairly clean query.[0] https://en.wikipedia.org/wiki/Sargable
[1] https://docs.aws.amazon.com/redshift/latest/dg/r_QUALIFY_cla...
The funny thing is it's actually several of those languages. :-)
was surprised to not see anything about dates/time.
Very often I have seen this problem buried in code design and it always sucks. Sometimes an orm obscures this but the basic antipattern looks like
Early on in my career an old-hand sql guru said to me “any time you are doing sql in a loop, you are probably doing it wrong”.The non-sucky version of the code above is
Many materialized views that rely on materialized views. When one at the bottom, or a table, needs a changed all views need to be dropped and recreated.
Using a warm standby for production. I love having a read only production database, but since it's not the primary, it always feels like it's on the losing end of the system. Recently upgraded to Postgres 18 and forgot that means I need to rm rf the standby and pg_basebackup to rebuild... That wasn't fun.
Our staging environment has its own instance that is rebuilt from prod, with pii removed, every day outside working hours (this normally takes about 15 minutes). It’s fantastic for testing migrations, and is easy to support compared with a warm standby.
https://en.wikipedia.org/wiki/Sargable
https://www.brentozar.com/blitzcache/non-sargable-predicates...
And Google explains "The term 'sargable' is a portmanteau of "Search ARGument ABLE," formed by combining the words from a SQL database context."
I've been working with SQL for 20+ years, and have literally never come across that word a single time in any documentation, tutorial, Stack Overflow answer, or here on HN. Working in Postgres, MySQL and SQLite.
Is it used at some particular company, or open source community, or with a particular database, or something?
Consider the Simple example it presents. The article is in effect implying that no query optimiser would be able to figure out the equivalence of the two predicates.
(Let's ignore that the two predicates aren't actually equivalent; the first version may raise an exception if myIntColumn is negative, depending on the DBMS.)