Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL Anti-Patterns

datamethods.substack.com

Post reply on HN
Print view View original

31–40 of 222 posts

Re: SQL Anti-Patterns

#31

Post by bts89 »

EvanAnderson wrote: post #14

> Overusing DISTINCT to “Fix” Duplicates 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.

That’s almost always my experience too.

Though fairly recently I learned that even with all the correct joins in place, sometimes adding a DISTINCT within a CTE can dramatically increase performance. I assume there’s some optimizations the query planner can make when it’s been guaranteed record uniqueness.

Re: SQL Anti-Patterns

#32

Post by onli »

tremon wrote: post #26
dgb23 wrote: post #19

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.

If you have select * in your code, there already is something wrong with your code, whether it breaks or not: the performance and possibly output of your code is now dependent on the table definition. I'm pretty sure Rich Hickey has also talked about the importance of avoiding non-local dependencies and effects in your code.

The performance and partly the output of the code is always dependent on the table definition. * instead of column names just removes an output limiter, which can be useful or can be irrelevant, depending on the context.

Though sure, known to negatively affect performance, I think in some database systems more than in others?

Re: SQL Anti-Patterns

#33

Post by tremon »

skybrian wrote: post #28

> 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?

This is not a pipeline in the control flow sense; the full query is compiled into a single processing statement, and the query compiler is free to remove and/or reorder any of the subqueries as it sees fit. The intermediate results during query execution (e.g. temp table spools) do not follow the structure of the original query, as CTEs and subqueries are not execution boundaries. It's more accurate to compare this to a C compiler that performs aggressive link-time optimization, including new rounds of copy elision, loop unrolling and dead code elimination.

If you want to build a pipeline and store each intermediate result, most tooling will make that easy for you. E.g. in dbt, just put each subquery in its separate file, and the processing engine will correctly schedule each subresult after the other. Just make sure you have enough storage available, it's not uncommon for intermediate results to be hundreds of times larger than the end result (e.g. when you perform a full table join in the first CTE, and do target filtering in another).

Re: SQL Anti-Patterns

#34

Post by wvbdmp »

EvanAnderson wrote: post #14

> Overusing DISTINCT to “Fix” Duplicates 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.

Eh, sometimes you need a quick fix and it’s just extremely concise and readable. I’ll take an INNER JOIN over EXISTS (nice but insanely verbose) or CROSS APPLY (nice but slow) almost every time. Obviously you have to know what you’re dealing with, and I’m mostly talking about reporting, not perf critical application code.

Distinct is also easily explained to users, who are probably familiar with Excel’s “remove duplicate rows”.

It can also be great for exploring unfamiliar databases. I ask applicants to find stuff in a database they would never see by scrolling, and you’d be surprised how many don’t find it.

Re: SQL Anti-Patterns

#36

Post by bandrami »

EvanAnderson wrote: post #14

> Overusing DISTINCT to “Fix” Duplicates 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.

IDK, "which ZIP codes do we have customers in?" seems like a reasonable thing to want to know

Re: SQL Anti-Patterns

#37

Post by mbb70 »

bandrami wrote: post #36
EvanAnderson wrote: post #14

> Overusing DISTINCT to “Fix” Duplicates 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.

IDK, "which ZIP codes do we have customers in?" seems like a reasonable thing to want to know

The very next ask will be "order the zipcodes by number of customers" at which point you'll be back to aggregations, which is where you should have started

Re: SQL Anti-Patterns

#38

Post by sigwinch28 »

EvanAnderson wrote: post #14

> Overusing DISTINCT to “Fix” Duplicates 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.

Or it’s simply an indicator of a schema that has not been excessively normalised (why create an addresses_cities table just to ensure no duplicate cities are ever written to the addresses table?)

Re: SQL Anti-Patterns

#39

Post by egeozcan »

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.
Post reply on HN