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

201–210 of 222 posts

Re: SQL Anti-Patterns

#201

Post by Sesse__ »

dfe wrote: post #199
Sesse__ wrote: post #78

Earlier quoted context omitted.

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.

What databases are those? If you convert to a semijoin, both strategies should be doable.

Note that these caveats do _not_ apply to IN, only NOT IN.

Re: SQL Anti-Patterns

#202

Post by fipar »

dotancohen wrote: post #194
fipar wrote: post #160

Earlier quoted context omitted.

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?

Nope, that does work as expected, unless a filesort is required, good point.

Re: SQL Anti-Patterns

#203

Post by crazygringo »

naasking wrote: post #196
crazygringo wrote: post #178

Earlier quoted context omitted.

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…

That seems like a logical nightmare to me. When thinking about which fields get returned, which fields do you index over, etc. It's a nice idea in theory, but would add an astronomical level of complexity in practice.

Re: SQL Anti-Patterns

#204

Post by crazygringo »

solumunus wrote: post #195
crazygringo wrote: post #178

Earlier quoted context omitted.

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 an…

I don't know -- the 40+ columns feels simple and maintainable.

Having a bunch of grouped optional values in other tables adds a ton of maintenance and query complexity.

The "clear constraints" belong in business logic, whether in triggers at the database level or before queries get executed at the application level.

Now, splitting up tables can produce performance optimizations. I'm not saying to never do it. But it's a tradeoff that increases complexity.

Re: SQL Anti-Patterns

#205

Post by magicalhippo »

solumunus wrote: post #195
crazygringo wrote: post #178

Earlier quoted context omitted.

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 an…

> I remember working on ERP systems with 40+ column tables, most of which were null.

Those are rookie numbers. Add a zero to that number and we're talking.

And for us, a good portion of the data, a considerable fraction of those fields will have data, and which fields will vary between customers.

All except some key fields are NULL-able since the user can save and resume their work.

Just to display our main screen would require 100-150 joins using a separate table per optional.

I'm pretty sure the database would not love that.

Re: SQL Anti-Patterns

#206

Post by MobiusHorizons »

DrewADesign wrote: post #73
MobiusHorizons wrote: post #52

Earlier quoted context omitted.

I use `WHERE true` for this. Very little cognitive load parsing that. And it makes AND conditions more copy pastable. Effectively the trailing comma of SQL where clauses

I absolutely cannot see how this would do what IDE formatting can’t, but admittedly the last time I wrote any significant amount of SQL directly was in a still-totally-relevant Perl 5 application. Could you give an example or link to a file in a public repository or whatever that would show this practice in context?

These were all adhoc analytics queries, not the sort of thing that get checked in.

It means you can copy paste any where condition because they are all of the form

   AND
otherwise one condition is of the form

  WHERE
So adding it to a query that already has a where clause is a bit more awkward.

I use this technique for some analytics queries that all rely on the same base table. It’s not uncommon to start with copying an old query and just adding or removing conditions and grouping/aggregating until I get the right data. Using this format also makes commenting out any condition trivial.

  WHERE true
  --    AND some_column = “some value”
      AND event = “SOME_EVENT_TYPE”
      AND EXISTS(SELECT * FROM UNNEST(array_column) as v WHERE v = “some value”)
I don’t see how you could achieve this result with just IDE formatting.

Re: SQL Anti-Patterns

#207

Post by naasking »

magicalhippo wrote: post #205
solumunus wrote: post #195

Earlier quoted context omitted.

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 an…

> I remember working on ERP systems with 40+ column tables, most of which were null. Those are rookie numbers. Add a zero to that number and we're talking. And for us, a good portion of the data, a considerable fraction of those fields will have data, and which fields will vary between customers. All except some key fields are NULL-able since the user can save and resume their work. Just to display our main screen wo…

400 columns all of which are nullable screams "dynamic field set" to me. Why have all of those as columns rather than something like:

    table Entity_Fields (ParentEntityId : int not null, FieldId : int not null, IntValue : int, TextValue : varchar(MAX), DateValue : datetime, ...)
Or per the OP's suggestion, a table per field type:

    table Entity_IntFields (ParentEntityId : int not null, FieldId : int not null, Value : int not null)
    table Entity_TextFields (ParentEntityId : int not null, FieldId : int not null, Value : varchar(MAX) not null)
    table Entity_DateFields (ParentEntityId : int not null, FieldId : int not null, Value : datetime not null)

Re: SQL Anti-Patterns

#208

Post by naasking »

crazygringo wrote: post #203
naasking wrote: post #196

Earlier quoted context omitted.

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

That seems like a logical nightmare to me. When thinking about which fields get returned, which fields do you index over, etc. It's a nice idea in theory, but would add an astronomical level of complexity in practice.

If the database supports algebraic data types, you wouldn't have to think about which fields get returned, it understand the needed semantics and handles that for you. Like I said, these are SQL limitations.

Re: SQL Anti-Patterns

#209

Post by magicalhippo »

naasking wrote: post #207
magicalhippo wrote: post #205

Earlier quoted context omitted.

> I remember working on ERP systems with 40+ column tables, most of which were null. Those are rookie numbers. Add a zero to that number and we're talking. And for us, a good portion of the data, a considerable fraction of those fields will have data, and which fields will vary between customers. All except some key fields are NULL-able since the user can save and resume their work. Just to display our main screen wo…

400 columns all of which are nullable screams "dynamic field set" to me. Why have all of those as columns rather than something like: table Entity_Fields (ParentEntityId : int not null, FieldId : int not null, IntValue : int, TextValue : varchar(MAX), DateValue : datetime, ...) Or per the OP's suggestion, a table per field type: table Entity_IntFields (ParentEntityId : int not null, FieldId : int not null, Value : in…

> 400 columns all of which are nullable screams "dynamic field set" to me

Could have been, but no. And doing it like you suggest would mean overview grids would have to do 50+ subqueries for each row, and loading a record would mean hundreds of queries.

And insertion performance would crater I assume, since the DB now needs to do hundreds of inserts per record rather than a single row. We do have some cases where we get 100k inserts per hour once a day or so. And this is just the main table, we have many child tables already, though they're not nearly as wide.

I think a more realistic split would have been to split the main table into maybe 10 tables or so at most. Still would result in a fair bit of subqueries for grids and such, but not that bad.

Re: SQL Anti-Patterns

#210

Post by MaxBarraclough »

naasking wrote: post #196
crazygringo wrote: post #178

Earlier quoted context omitted.

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…

I'm reminded of table inheritance in Postgres, it's not quite the same thing but it's close.

https://www.postgresql.org/docs/current/tutorial-inheritance...

Post reply on HN