5 ms·

> SQL is messy because describing the underlying data relationships are messy. No, the relational model is beautiful and consistent! SQL is messy because the s

by goto11 6y ago

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

gboss 6y ago
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.
- nightski 6y ago
  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.
  - scarface74 6y ago
    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.
- merb 6y ago
  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!!
- int_19h 6y ago
  That's a quality of tooling issue. And in Visual Studio, for example, you can set breakpoints on individual subexpressions in the query.
marcinzm 6y ago
>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.
- jimsparkman 6y ago
  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.
  - 3pt14159 6y ago
    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.
    - sumtechguy 6y ago
      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.
  - achr2 6y ago
    CTEs do not get cached though, so they are actually quite bad for repetition without also using a temp table.
    - ComodoHacker 6y ago
      Depends on DBMS and version. Some are smart enough to pipeline/tie or materialize it internally.
  - meritt 6y ago
    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-tips#common-table-expressions-and-subqueries https://thoughtbot.com/blog/advanced-postgres-performance-ti...
achr2 6y ago
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.
- wtetzner 6y ago
  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.
  - achr2 6y ago
    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.
    - wtetzner 6y ago
      > 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.
- nine_k 6y ago
  It's the problem with the object model. Yes, I think ORMs are an anti-pattern, ActiveRecord-style especially.
  - scarface74 6y ago
mannykannot 6y ago
You have piqued my curiosity, but what would a linq solution to this exercise look like?
- int_19h 6y ago
  Which particular exercise?
  - mannykannot 6y ago
    The multiple dept. heads case.
prostodata 6y ago
> 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 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-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

by goto11 6y ago

gboss 6y ago

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.

nightski 6y ago
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.
- scarface74 6y ago
  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.
merb 6y ago
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!!
int_19h 6y ago
That's a quality of tooling issue. And in Visual Studio, for example, you can set breakpoints on individual subexpressions in the query.

marcinzm 6y ago

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

jimsparkman 6y ago
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.
- 3pt14159 6y ago
  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.
  - sumtechguy 6y ago
    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.
- achr2 6y ago
  CTEs do not get cached though, so they are actually quite bad for repetition without also using a temp table.
  - ComodoHacker 6y ago
    Depends on DBMS and version. Some are smart enough to pipeline/tie or materialize it internally.
- meritt 6y ago
  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-tips#common-table-expressions-and-subqueries https://thoughtbot.com/blog/advanced-postgres-performance-ti...

achr2 6y ago

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.

wtetzner 6y ago
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.
- achr2 6y ago
  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.
  - wtetzner 6y ago
    > 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.
nine_k 6y ago
It's the problem with the object model. Yes, I think ORMs are an anti-pattern, ActiveRecord-style especially.
- scarface74 6y ago

mannykannot 6y ago

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

int_19h 6y ago
Which particular exercise?
- mannykannot 6y ago
  The multiple dept. heads case.

prostodata 6y ago

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