Hacker News Reader

Comment by goto11 | Hacker News Reader

> SQL is messy because describing the underlying data relationships are messy.

No, the relational model is beautiful and consistent! SQL is messy because the syntax is not consistent and elegantly composable. It could have those properties and still present the same underlying data relationships.

See Linq in C# as an example for how a more composable query syntax can expose the same data model.

For example in Linq you can chain arbitrary many select/join/where/group by in arbitrary order. In SQL you need nested subqueries to achieve the same which is a much more convoluted syntax.

View on HN

Slightly tangential but I really like Linq syntax. The only problem I have with it is that it is really hard to debug if there is a logic error. I often see developers write linq find out the record set they get back is incorrect and then break up the linq query to a nested if clause to get what they want.

Yeah I feel like that happens whenever you have a mapping from one language to another. Eventually you learn the intricacies of the transformation. There are very few situations where the Linq I write does not translate into the query I expect any more. But that took time to learn.

The only criticism I have of Linq is that it is a more general language targeting different back-ends including plain old objects. If Linq did not allow things that do not make sense in SQL this would be a little less jarring.

I also wish it supported full outer joins, unions, and window functions.

I was amazed the first time I used the MongoLinq driver. It makes sense intuitively, but the idea that you can use the same Linq expression and it will be translated to either Sql or MongoQuery based on the provider you send it to was amazing.

I was able to hire a four or five contractors who never used Mongo just by requiring Entity Framework/Linq experience and less than 4 hours of training.

I've seen so many errors around orderby/limit. which most people want at the end, but linq lets them define it, everywhere. (for a good reason) but this is probably the biggest problem with linq, it's a chain of operators that are ORDER dependant!!

That's a quality of tooling issue. And in Visual Studio, for example, you can set breakpoints on individual subexpressions in the query.

>For example in Linq you can chain arbitrary many select/join/where/group by in arbitrary order. In SQL you need nested subqueries to achieve the same which is a much more convoluted syntax.

WITH statements alleviate some of this issue by allowing you to write subqueries in any order.

WITH (CTEs) make queries so much more readable and digestible. As a programmer who now does data and SQL, I latched on to these as soon as I found I could reduce repetition in a query with them.

For monster queries (thousands of lines of SQL) I find that temporary tables are also great. You can index them and, depending on your DB and its settings, they're usually held in RAM so they're super fast.

That is implementation specific. In some db's putting an index on a temp table does nothing. In later versions of the same db it does but only in particular cases. Make sure you read the docs around that.

CTEs do not get cached though, so they are actually quite bad for repetition without also using a temp table.

Depends on DBMS and version. Some are smart enough to pipeline/tie or materialize it internally.

Make sure you understand what optimization fences are and how they affect your performance. CTEs are nice to read but routinely destroy the performance.

[1] https://thoughtbot.com/blog/advanced-postgres-performance-ti...

As of Postgres 12 this has changed substantially. Instead of being materialized, CTEs are inlined and optimized with the rest of the query.

Exceptions:

1. If the results of the CTE are used more than once then it is materialized by default, though you can override this by adding "NOT MATERIALIZED" to the call.

2. Recursive and INSERT/UPDATE/DELETE CTEs are always materialized.

https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

>CTEs are nice to read but routinely destroy the performance.

This may be true on archaic versions of MySQL and Postgres but is not the case today, barring some esoteric edge cases (bugs) where the optimizer gets thrown out of whack. Once while doing data science consulting I rewrote a ~1000 line query in Aurora (MySQL flavored) which had a ~2.5s runtime, which was far too slow for the client's use-case.

After rewriting all the CTEs (there were many) into subqueries, there was a 2-3% increase in query speed, barely (on the order of under a tenth of a second). There was a very tiny improvement far smaller than the normal variance of the runtime.

Then I rebuilt the query and the joins, and was able to get the query to consistently run in the range of 0.8 - 1.2s. For my own purposes I then duplicated the query, re-implemented the CTEs, and did validate that indeed there is only a negligible increase in query time when using CTE.

Ecto.Query in the Elixir universe seems like it was inspired by linq

WITH's are great and definitely solves some of the issues with SQL. Some implementations unfortunately don't optimize them as subqueries, but that is not the fault of SQL.

> No, the relational model is beautiful and consistent! SQL is messy...

What is beautiful and consistent is the relational algebra. The relation model relies on this formalism to model data by making some rather strong assumptions about how tuples and relations represent things and how relational operations are used to process them. And these assumptions are precisely what propagates to SQL and what some authors (see references in the article) consider controversial and messy. Then the question is whether and how these controversies can be fixed (or whether they are bugs or features).

A radically different approach to fix these controversies is to introduce a different formalism and different data model (as opposed to fixing only syntax) which is based on using functions. In other words, instead of using sets and set operations, we use in addition functions and function operations [1]. Here you can find an implementation of this approach:

https://github.com/prostodata/prosto Prosto is a data processing toolkit radically changing how data is processed by using both sets and functions and being a major alternative to map-reduce, join-groupby and other set-oriented approaches

[1] Concept-oriented model: Modeling and processing data using functions: https://www.researchgate.net/publication/337336089_Concept-o... -> Read introduction (two pages) for why having only sets is not enough and why functions are important

I disagree, the relational model is very incongruous with the object model that nearly all LOB applications use. Which is where 99% of the usage issues lie.

That doesn't make the relation model bad, it just means there's an impedance mismatch.

FWIW, I find the relational model to be much nicer than typical object models. Even in Java, which I write every day at work, I've found it to be much clearer to use immutable objects representing records. Not just for interacting with the database, but just for handling information in general.

Of course, it would be extremely painful to do without Lombok's @Value, @Builder, and @With annotations.

I agree that the mismatch is not a direct failing of SQL or the relational model - but we continue to use it without adopting a better approach or developing a common abstraction layer. With all of the syntax growth, SQL could certainly have a join syntax that understands object composition and returns structured data.

> With all of the syntax growth, SQL could certainly have a join syntax that understands object composition and returns structured data.

I mean, many RDBMS's have support for JSON and/or XML, so you can sort of get it.

However, I wonder if we'd be better of as an industry if we dealt with our data in code in a more relation-y way than an object-y way.

It's the problem with the object model. Yes, I think ORMs are an anti-pattern, ActiveRecord-style especially.

And then you end up inventing your own ORM and “helpers” because eventually the results end up in objects.

I do hate most ORMs. The only oneS I like are Linq based ORMs. The “ORM” is actually well integrated into the language.

You might as well use something like Dapper.

But yes agreed about ActiveRecord.

You have piqued my curiosity, but what would a linq solution to this exercise look like?

Which particular exercise?

The multiple dept. heads case.

Reply on news.ycombinator.com