7 ms·

SQL does have a significant drawback w.r.t. how databases are used today (imo): a SELECT query can only return a single resultset of uniform tuples: if you want

SQL does have a significant drawback w.r.t. how databases are used today (imo): a SELECT query can only return a single resultset of uniform tuples: if you want to query a database for hetereogenous types with differing multiplicity (i.e. an object-graph) then you either have to use multiple SELECT queries for each object-class - or use JOINs which will result in the Cartesian Explosion problem[1] which also results in redundant output data due to the multiplicity mismatch - SQL JOINs also lack the ability to error-out early if the JOIN matches an unexpected number of rows. And there are often problems when using multiple SELECT queries in a batched statement: you can't re-use existing CTE queries. Not all client libraries support multiple result-sets. It's essentially impossible to return metadata associated with a resultset (T-SQL and TDS doesn't even support named result sets...), which means you can't opportunistically skip or omit a SELECT query in a batch because your client reader won't know how to parse/interpret an out-of-order resultset, and most importantly: you need to be careful w.r.t. transactions otherwise you'll run into concurrency issues if data changes between SELECT queries in the same batch () [1] https://learn.microsoft.com/en-us/ef/core/performance/efficient-querying#avoid-cartesian-explosion-when-loading-related-entities https://learn.microsoft.com/en-us/ef/core/performance/effici... and https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries https://learn.microsoft.com/en-us/ef/core/querying/single-sp...

gozzoo 3y ago
Isn't your comment judging a fish by its ability to climb a tree?
- post-it 3y ago
  If the task is to climb a tree, it's reasonable to not hire a fish.
  - eimrine 3y ago
    https://www.google.com/search?q=can+fish+climb+tree https://www.google.com/search?q=can+fish+climb+tree
    - post-it 3y ago
      Thank you eimrine, very cool!
- Psyladine 3y ago
  You're asking those whose object problem model is a FOR loop not finding set based operations intuitive, efficient or ever necessary?
gemstones 3y ago
Yup, this right here. This aspect of SQL is overwhelmingly why I insist on ORMs, too. Any efficiency gains you get by having a senior dev write raw SQL for a complex query are immediately negated by a junior turning what an ORM would write as a single query into three DB calls. All because SQL insists on a flat result set you have to turn into a nested collection yourself, without an ORM doing it for you with eager loading.
- nine_k 3y ago
  From my experience, ORMs in hands of junior.developers who happen to not yet know SQL are a disaster. However hard the ORMs may try, the code ends up making a ton of small queries instead of one efficient query, and fetching a ton of unused columns. The developers then end up doing joins manually in application code, some distance further from the place of the original queries. ORMs also tend to sneak "live" objects into unexpected places, triggering surprise DB accesses. Not that ORMs are completely useless. We just need to stop pretending that there can be a smooth and performant automatic mapping between relational tables living in a DBMS and Business Objects living in some idealized world without storage limitations, where any connection between them is like following a pointer in RAM. Most ORMs provide tools to write composable, reusable queries and parts thereof. These are the best parts.
  - drbawb 3y ago
    Every time I have used an ORM I end up supplementing or replacing it with a "query DSL" like jOOQ, Linq, Arel, Ecto, diesel, etc. I seem to have the opposite problem of OP: I don't often find myself wanting to hydrate some complex object graph, what I really want is some small fraction of what constitutes "an object": "get me the distinct values of this column, sorted by another column", or "get me a list of user IDs and e-mails that are subscribed to this topic", etc. Trivial to do in SQL, and much faster to do that sort of thing _in the database_, where the data is already _memory/cache resident._ ORMs, by design, bring unnecessary data over the wire for the sake of inflating parts of an object graph you don't care about 90% of the time. Most of that data will either be unused, or you are going to transform and then discard anyways. If you go out of your way to actually optimize out unused fields: now you're passing around objects with nulled-out references around your application, which is just a disaster waiting to happen. Having a query DSL that actually maps result sets to your language's type system is the only way I've found to actually write robust, performant, maintainable code. My result sets being "too big cartesian disasters" is just not a problem I have, because I don't think in objects. I ask the database for what I want to get the job done.
nicoburns 3y ago
This is no longer true in database that have JSON support (which is most of them these days). You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)
- masklinn 3y ago
  That further increase the complexity of the queries, and then you start hitting weird corner-cases like postgres's difficulty (inability?) to convert a JSON array of JSON text elements to an array of text.
  - tomnipotent 3y ago
    > further increase the complexity of the queries Small price to pay for improving RDBMS throughput and eking out more from limited hardware. There is no shortage of use cases where this just makes sense. Doing all of that work in a single SQL query also makes sure there's less buffer cache thrashing.
    - mattmanser 3y ago
      I think you're wrong, happy to be corrected though. As far as I can tell, if you change a subselect column into JSON, it's much more expensive in CPU and marginally more expensive in network bandwidth. 1. The data has to be serialised into JSON on the DB server, which costs CPU. 2. It then has to be deserialised on the application server (unless your backend is written in javascript and then your throughput problem is that you're using javascript instead of a better, compiled language) 3. The network bandwidth is actually larger as you've got all those extra {} in your result set, compared to the raw data in column format It might "look" bigger to a human as there's more columns, but the data is exactly the same. So by definition, youre doing extra CPU work of serialising/deserliazing JSON and adding all the object markers of extra characters like {} and "" and : means the payload is bigger too.
      - tomnipotent 3y ago
tlarkworthy 3y ago
I keep staring at the output of sqlalchemy's "select in" eager loader trying to figure out if has managed to pipline the follow up queries into a single DB round trip for the problem of tree shaped data. Still don't know, maybe someone does. https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#select-in-loading https://docs.sqlalchemy.org/en/14/orm/loading_relationships....
zapov 3y ago
Those are problems in MS Sql, certainly not in object relational DBs such as Postgres or Oracle. And its rather sad that instead of embracing that we ended up with Json as poor man replacement for such advanced usages. I guess non portability across DBs certainly doesnt help. I tried showing that 10 years ago: https://github.com/ngs-doo/revenj/ https://github.com/ngs-doo/revenj/ but it just resulted in confusion.
nurettin 3y ago
You don't really need multiple result sets, you can do lateral joins (cross apply in microsoftish) to bring in any unrelated data and distinct the result. And fancyql doesn't provide any alternatives, so it makes no sense to even bring this up.

gozzoo 3y ago

Isn't your comment judging a fish by its ability to climb a tree?

post-it 3y ago
If the task is to climb a tree, it's reasonable to not hire a fish.
- eimrine 3y ago
  https://www.google.com/search?q=can+fish+climb+tree https://www.google.com/search?q=can+fish+climb+tree
  - post-it 3y ago
    Thank you eimrine, very cool!
Psyladine 3y ago
You're asking those whose object problem model is a FOR loop not finding set based operations intuitive, efficient or ever necessary?

gemstones 3y ago

Yup, this right here. This aspect of SQL is overwhelmingly why I insist on ORMs, too. Any efficiency gains you get by having a senior dev write raw SQL for a complex query are immediately negated by a junior turning what an ORM would write as a single query into three DB calls. All because SQL insists on a flat result set you have to turn into a nested collection yourself, without an ORM doing it for you with eager loading.

nine_k 3y ago
From my experience, ORMs in hands of junior.developers who happen to not yet know SQL are a disaster. However hard the ORMs may try, the code ends up making a ton of small queries instead of one efficient query, and fetching a ton of unused columns. The developers then end up doing joins manually in application code, some distance further from the place of the original queries. ORMs also tend to sneak "live" objects into unexpected places, triggering surprise DB accesses. Not that ORMs are completely useless. We just need to stop pretending that there can be a smooth and performant automatic mapping between relational tables living in a DBMS and Business Objects living in some idealized world without storage limitations, where any connection between them is like following a pointer in RAM. Most ORMs provide tools to write composable, reusable queries and parts thereof. These are the best parts.
- drbawb 3y ago
  Every time I have used an ORM I end up supplementing or replacing it with a "query DSL" like jOOQ, Linq, Arel, Ecto, diesel, etc. I seem to have the opposite problem of OP: I don't often find myself wanting to hydrate some complex object graph, what I really want is some small fraction of what constitutes "an object": "get me the distinct values of this column, sorted by another column", or "get me a list of user IDs and e-mails that are subscribed to this topic", etc. Trivial to do in SQL, and much faster to do that sort of thing _in the database_, where the data is already _memory/cache resident._ ORMs, by design, bring unnecessary data over the wire for the sake of inflating parts of an object graph you don't care about 90% of the time. Most of that data will either be unused, or you are going to transform and then discard anyways. If you go out of your way to actually optimize out unused fields: now you're passing around objects with nulled-out references around your application, which is just a disaster waiting to happen. Having a query DSL that actually maps result sets to your language's type system is the only way I've found to actually write robust, performant, maintainable code. My result sets being "too big cartesian disasters" is just not a problem I have, because I don't think in objects. I ask the database for what I want to get the job done.

nicoburns 3y ago

This is no longer true in database that have JSON support (which is most of them these days). You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)

masklinn 3y ago
That further increase the complexity of the queries, and then you start hitting weird corner-cases like postgres's difficulty (inability?) to convert a JSON array of JSON text elements to an array of text.
- tomnipotent 3y ago
  > further increase the complexity of the queries Small price to pay for improving RDBMS throughput and eking out more from limited hardware. There is no shortage of use cases where this just makes sense. Doing all of that work in a single SQL query also makes sure there's less buffer cache thrashing.
  - mattmanser 3y ago
    I think you're wrong, happy to be corrected though. As far as I can tell, if you change a subselect column into JSON, it's much more expensive in CPU and marginally more expensive in network bandwidth. 1. The data has to be serialised into JSON on the DB server, which costs CPU. 2. It then has to be deserialised on the application server (unless your backend is written in javascript and then your throughput problem is that you're using javascript instead of a better, compiled language) 3. The network bandwidth is actually larger as you've got all those extra {} in your result set, compared to the raw data in column format It might "look" bigger to a human as there's more columns, but the data is exactly the same. So by definition, youre doing extra CPU work of serialising/deserliazing JSON and adding all the object markers of extra characters like {} and "" and : means the payload is bigger too.
    - tomnipotent 3y ago

tlarkworthy 3y ago

I keep staring at the output of sqlalchemy's "select in" eager loader trying to figure out if has managed to pipline the follow up queries into a single DB round trip for the problem of tree shaped data. Still don't know, maybe someone does. https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#select-in-loading https://docs.sqlalchemy.org/en/14/orm/loading_relationships....

zapov 3y ago

Those are problems in MS Sql, certainly not in object relational DBs such as Postgres or Oracle. And its rather sad that instead of embracing that we ended up with Json as poor man replacement for such advanced usages. I guess non portability across DBs certainly doesnt help. I tried showing that 10 years ago: https://github.com/ngs-doo/revenj/ https://github.com/ngs-doo/revenj/ but it just resulted in confusion.

nurettin 3y ago

You don't really need multiple result sets, you can do lateral joins (cross apply in microsoftish) to bring in any unrelated data and distinct the result. And fancyql doesn't provide any alternatives, so it makes no sense to even bring this up.