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

191–200 of 222 posts

Re: SQL Anti-Patterns

#191

Post by 1718627440 »

BuyMyBitcoins wrote: post #70
ryandv wrote: post #55

Earlier quoted context omitted.

Set theory... There are self-identifying "senior software engineers" that cannot understand what even an XOR is, even after you draw out the entire truth table, all four rows.

I am surprised at common it is for software engineers to not treat booleans properly. I can’t tell you how many times if seen ‘if(IsFoo(X) != false)’ It never used to bug me as a junior dev, but once a peer pointed this out it became impossible for me to ignore.

I think of comparisons as a type conversion to a boolean. You wouldn't convert a boolean, but I like it to convert other types like an integer, even when the language rules would already specify the same I'm writing.

Re: SQL Anti-Patterns

#192

Post by geysersam »

Sesse__ wrote: post #78
geysersam wrote: post #67

Earlier quoted context omitted.

> Using != or NOT IN (...) is almost always going to be inefficient. Why do you say that? My understanding is that as long as the RHS of NOT IN is constant (in the sense that it doesn't depend on the row) the condition is basically a hash table lookup, which is typically efficient if the lookup table is not massive. What's the more efficient alternative?

I'm going to assume here that we're talking about a subquery here (SELECT * FROM t1 WHERE x NOT IN ( SELECT x FROM t2 )). If you're just talking about a static list, then the basic problem is the amount of data you get back. :-) The biggest problem with NOT IN is that it has very surprising NULL behavior: Due to the way it's defined, if there is any NULL in the joined-on columns, then _all_ rows must pass. If the col…

That's useful, thanks!

Re: SQL Anti-Patterns

#193

Post by Arch-TK »

darkerside wrote: post #189
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.

UUIDs take up 36 bytes as strings so store them natively as 16 bytes instead if you can.

This is still 2x the space of an auto increment number.

This is overhead for every table, every index, and every relationship.

That might be acceptable in your case though, the case where it became unacceptable in my experience was in a MSSQL Express context. But it was an idiotic decision to use MSSQL to begin with in that scenario.

Regarding random clustered indexes. Broadly speaking you want your clustered index to be made up of some incremental unique set of fields.

I mean, technically there is not a massive issue, but the largest tables in your database will be the non-indexes (indexes are just tables) and you want your big, mainly append only, tables to be nicely compact so a bunch of space isn't taken up by half full pages.

But again, I should honestly have clarified that the problem was mainly an MSSQL Express problem where databases are limited to 10GiB.

You might honestly be fine, but do look for documentation on your specific database.

Re: SQL Anti-Patterns

#194

Post by dotancohen »

fipar wrote: post #160
dotancohen wrote: post #63

Earlier quoted context omitted.

I've been told similar nasty things for adding LIMIT 1 to queries that I expect to return at most a single result, such as querying for an ID. But on large tables (at least in sqlite, mysql, and maybe postgress too) the database will continue to search the entire table after the given record was found.

In mysql, the db will continue reading even if the limit condition has been met, and then anything beyond the limit will be discarded before returning the result.

Even without an ORDER BY clause?

Re: SQL Anti-Patterns

#195

Post by solumunus »

crazygringo wrote: post #178
naasking wrote: post #158

Earlier quoted context omitted.

> but having separate tables for every optional field would be an organizational and usability nightmare I think this indicates that declaring and managing state is too onerous in SQL.

Or maybe NULLs are actually a great solution here, and it's fine. The idea that having a separate table for every optional field is too unworkable isn't an issue with SQL. It's a fundamentally overcomplicated idea. It's like a programming language where every variable that could be null had to be put in its own file. It would be terrible design.

Over 90% of my optionals have their own tables and it’s the cleanest and most maintainable database I’ve ever worked with. I will always design databases this way going forward. That’s my experience.

I remember working on ERP systems with 40+ column tables, most of which were null. With no clear constraints on which options should or shouldn’t enable or make mandatory other options. This becomes incredibly obvious and natural when you group in additional tables.

My tables are incredibly concise and the cache loves this.

Re: SQL Anti-Patterns

#196

Post by naasking »

crazygringo wrote: post #178
naasking wrote: post #158

Earlier quoted context omitted.

> but having separate tables for every optional field would be an organizational and usability nightmare I think this indicates that declaring and managing state is too onerous in SQL.

Or maybe NULLs are actually a great solution here, and it's fine. The idea that having a separate table for every optional field is too unworkable isn't an issue with SQL. It's a fundamentally overcomplicated idea. It's like a programming language where every variable that could be null had to be put in its own file. It would be terrible design.

> The idea that having a separate table for every optional field is too unworkable isn't an issue with SQL.

It sure is. Consider a database language that innately supported algebraic data types. Instead of:

    table Contact { int Id; Nullable email; Nullable phoneNo; }
you have:

    type PhoneOrEmail = Phone | Email
    table Contact { int Id; PhoneOrEmail info; }
This completely clarifies the relationships between the nullable columns (can both be null, or only one null?), and the database storage layer would manage how to actually store this. This is a direct consequence of SQL's semantics and how it implements the relational calculus.

Re: SQL Anti-Patterns

#197

Post by FuriouslyAdrift »

MaxBarraclough wrote: post #187
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…

Brent Ozar isn't guessing about MS SQL Server... he knows the eval engine inside and out.

Re: SQL Anti-Patterns

#198

Post by FuriouslyAdrift »

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?

Oracle, DB2, SQl Server... I've seen in the more Enterprise targeted docs for Postgres.

Re: SQL Anti-Patterns

#199

Post by dfe »

Sesse__ wrote: post #78
geysersam wrote: post #67

Earlier quoted context omitted.

> Using != or NOT IN (...) is almost always going to be inefficient. Why do you say that? My understanding is that as long as the RHS of NOT IN is constant (in the sense that it doesn't depend on the row) the condition is basically a hash table lookup, which is typically efficient if the lookup table is not massive. What's the more efficient alternative?

I'm going to assume here that we're talking about a subquery here (SELECT * FROM t1 WHERE x NOT IN ( SELECT x FROM t2 )). If you're just talking about a static list, then the basic problem is the amount of data you get back. :-) The biggest problem with NOT IN is that it has very surprising NULL behavior: Due to the way it's defined, if there is any NULL in the joined-on columns, then _all_ rows must pass. If the col…

It's not just the nullability behavior. My experience with several databases is that IN is always (or almost always) executing the subquery then using its results to match the outer predicate. But EXISTS can work the other direction, matching the predicates from the outer query then passing those keys into the exists, allowing use of an FK index on the inner query's table.

Re: SQL Anti-Patterns

#200

Post by MaxBarraclough »

FuriouslyAdrift wrote: post #197
MaxBarraclough wrote: post #187

Earlier quoted context omitted.

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…

Brent Ozar isn't guessing about MS SQL Server... he knows the eval engine inside and out.

I should have been clearer, I meant the Wikipedia article.

Ozar's article is much better. It doesn't make sweeping assumptions about the limitations of all query optimisers, or basic oversights in contrasting supposedly equivalent predicates.

Post reply on HN