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

181–190 of 222 posts

Re: SQL Anti-Patterns

#181

Post by MaxBarraclough »

crazygringo wrote: post #141
MaxBarraclough wrote: post #129

Earlier quoted context omitted.

> This is literally what NULL is for. It's a special value precisely because missing data or a N/A field is so common. Kinda. You need null for outer joins, but you could have a relational DBMS that prohibits nullable columns in tables. Christopher Date thought that in properly normalised designs, tables should never use nullable columns. Codd disagreed. [0] > If you're suggesting mandatory additional has_address and…

> The way to do it without using a nullable column I mean, you could, but having separate tables for every optional field would be an organizational and usability nightmare. Queries would be longer and slower for no good reason. Not to mention a gigantic waste of space with all those repeated primary keys and their indexes. And you could have databases that prohibited NULL values, but we mostly don't, because they're…

Right, Date's idea didn't catch on. I'm not aware of any industry-strength RDBMS prohibiting storing null.

Agreed that queries would tend be longer as you'd need joins, although views could help, especially for read operations.

Regarding storage-efficiency and query speed, agreed that it could well hurt both, but it's going to depend. If a column holds null in almost all rows, we would expect it to be more space-efficient to use a separate table and a left outer join. Query speed could also improve for queries that don't reference the nullable column, as the 'main' table would be smaller in storage. (I'm assuming a rowstore here.)

Re: SQL Anti-Patterns

#182

Post by chuliomartinez »

dfe wrote: post #166
chuliomartinez wrote: post #165

SQL makes it very hard to express real world requirements. 1. No easy way to limit child records count in joins - find all orders with orderproduct.amount is greater than X. Obviously this will genereate duplicates for orders that have more than one such orderproduct. So you slap a distinct on it… but what if you need an aggregation? The possible fixes are highly non-trivial: subqueries, window functions, or vendor s…

Your own article points out that exists handles the first case. Exists is not actually implemented as a subquery, it is merely syntactically a subquery.

Sure exists make sense if you dont need columns from the child table. Exists is also far from basic sql.

Re: SQL Anti-Patterns

#183

Post by chuliomartinez »

procaryote wrote: post #168
chuliomartinez wrote: post #165

SQL makes it very hard to express real world requirements. 1. No easy way to limit child records count in joins - find all orders with orderproduct.amount is greater than X. Obviously this will genereate duplicates for orders that have more than one such orderproduct. So you slap a distinct on it… but what if you need an aggregation? The possible fixes are highly non-trivial: subqueries, window functions, or vendor s…

I don't really understand the problem in 1 in 2, looking at your article, from your first query it looks like person_relationship contains both (A,B) and (B,A) for all related people A and B; otherwise the left join won't work. If you also make people related to themselves and store (A,A) and (B,B) there your query becomes much simpler: SELECT other.id, other.name FROM person p JOIN person_relationship r ON r.from_pe…

Creative solution! Of course this creates more maintenance to ensure such records exist. I guess you kinda proved my point, you have to work around it.

Re: SQL Anti-Patterns

#184

Post by cowboylowrez »

crazygringo wrote: post #180
FuriouslyAdrift wrote: post #143

The single biggest thing that helped me speed up my queries and lower resource usage on the server was focusing on making my queries more sargable. https://en.wikipedia.org/wiki/Sargable https://www.brentozar.com/blitzcache/non-sargable-predicates...

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?

My guess is mssql as I've seen the term quite a bit with those guys.

Re: SQL Anti-Patterns

#185

Post by Guillaume86 »

dotancohen wrote: post #113
Guillaume86 wrote: post #93

Earlier quoted context omitted.

Only if your table is missing an unique index on that column, which it should have to enforce your assumption, so yeah LIMIT 1 is a code (or schema in the case) smell.

IDs are typically unique primary key. But in my experience, adding LIMIT 1 would on average halve the time taken to retrieve the record. I'll test again, really the last time I tested that was two decades ago.

You should use the DB explain or equivalent command to spit out the query plan, limit 1 shouldn't change anything in your case, if it's not the case you should file an issue, it's pretty much 101 of query optimization.

Re: SQL Anti-Patterns

#186

Post by mattnewport »

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?

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

Re: SQL Anti-Patterns

#187

Post by MaxBarraclough »

FuriouslyAdrift wrote: post #143

The single biggest thing that helped me speed up my queries and lower resource usage on the server was focusing on making my queries more sargable. https://en.wikipedia.org/wiki/Sargable https://www.brentozar.com/blitzcache/non-sargable-predicates...

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.)

Re: SQL Anti-Patterns

#188

Post by furyofantares »

Rexxar wrote: post #172
furyofantares wrote: post #117

Earlier quoted context omitted.

The most egregious one I saw, I was tracking down a bug and found code like this: bool x; ... if (x == true) { DoThing1(); } else if (x == false) { DoThing2(); } And of course neither branch was hit, because this is C, and the uninitialized x was neither 0 nor 1, but some other random value.

Maybe it was initially supposed to be a sort of "3-value boolean" (true/false/undefined) and not a standard bool. You can (rarely) meet this pattern in c++ if you use boost::tribool or in c# if you have a nullable bool. There is probably similar thing in other languages.

It was definitely just bad code.

Re: SQL Anti-Patterns

#189

Post by darkerside »

Arch-TK wrote: post #83

Forgot to add (all seen in production): * 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.

Can you share any details? A teammate wants to change primary identifiers to a GUID, but I'm not sure it's a good idea.

Re: SQL Anti-Patterns

#190

Post by readthenotes1 »

JadeNB wrote: post #22
readthenotes1 wrote: post #16

Earlier quoted context omitted.

If a pattern is a common problem (e.g., becoming accustomed to a spectacular view) and generally-useful solution to that problem (blocking the view so that effort is required to obtain it), then an anti-pattern is what? I think most people think an anti-pattern is an aberration in the "solution" section that creates more problems. So here, the anti-pattern is that people use a term so casually (e.g., DevOps) that no…

> If a pattern is a common problem (e.g., becoming accustomed to a spectacular view) and generally-useful solution to that problem (blocking the view so that effort is required to obtain it), then an anti-pattern is what? Strange choice of example! I'm not sure I agree that your example is a common problem, and I'm even less sure that the proposed solution to it is generally useful.

It's name is Zen View, and is one of the memorable patterns from Alexander's catalog of design patterns
Post reply on HN