You can already return complex data structures using jsonagg and recursive queries. I can see wanting to make that easier and more intuitive to do. Current sql does not restrict you to just 2D table data structures though.
I do think the idea is pretty neat, although it’s pretty close to returning structured data like json.
A slight disclaimer that these people are smarter than me, and know better about what we are talking about, so I may be wrong here on some assumptions. But I do get a bit of feeling they are trying to solve a trivial problem, at least in their use case. Ultimately there are only so many lecturers, and so many man lectures at a given time. The total data amount wouldn’t be so much, and you can easily group by and sort on client side to achieve the original table which is show on a per lecturer basis. A little redundancy is in my opinion preferred over a query that returns 3 tables that then needs additional complicated work. I also find arguments about overlapping names to not be something the database should be handling, it falls on the data owners/manager instead. Academia is a wild west at times, but either this table is presentation only or a link to lecturer or lecture. And in the latter case, you’ll already throw in the ids so they can be used in an URL to some other site.
While this can have significant less bandwidth, it also risks falling as soon as more data is introduced, as you’re putting the large join operations on the client when you can get free optimizations from the SQL engine you use. I know not having duplicate data could be a thing for something where I work, where essentially we have hourly breakdowns but fetch at least the entire day for a single set of parameters. So that means 24x data for a surprisingly high amount of columns. When we only need 2 of them on the hourly level!
But in this case, the data doesn’t strictly need many joins as it has a lot of the information itself, along with there being too much data to join on the client side anyways for this to feel ideal. I feel you’ll increase the complexity a bit too much as well. A big advantage of sql is how easy it is to understand what you are getting.
Its somewhat of a solved problem, if the performance becomes a problem, since we can return nested data anyways. So we can already today technically return a row where the hour(I think, never tried a date before) and value columns have arrays instead of a single value. We just haven’t done it because it is not a big enough problem yet.
An interesting concept, and I do agree that the post-join cost is something that we can probably safely ignore. But as I was reading it I was curious if a better way to start conceptually approaching the solution is to consider an n+1 table approach where any tables referenced will be returned (filtered to relevant rows and optionally omitting extraneous columns) along with an additional table containing necessary key references and any of the computed aggregates etc… this might shift the select phrase to instead of defining all desired columns to only specifying additionally needed columns.
But… I do have some objections to this concept it seems to place an extremely heavy value on the initial schema that SQL does not and causes difficulties in some scenarios when a single table is joined against multiple times for different purposes. I think it’d become difficult for the front end to decipher and rebuild the relationships without very heavy lifting.
It’s a really interesting idea and flips the established return structure on its head in a way I don’t hate.
SQL returns subsets of all tables with only those tuples that would be part of the traditional (single-table) query result set
So it returns only the data that would be returned from the query, so the filtering is done.
I can see some uses of it. If you look at what something like Entity Framework does behind the scenes to return nested objects, you can see how something like this might help.
EF can have big problems with “Cartesian explosions” if an object has two lists of sub objects to return, it will get listA length x listB length items due to how the joins work. You can see how this leads to the explosion part of the name (with more objects or lists).
Their solution is a “split query” option, that does each sub table as a separate query, then seamlessly gives you the combined result.
If a change like this let’s you get those different table lists as distinct lists with the processing and round trip time of multiple requests then it could be a game changer.
You are not logged in. However you can subscribe from another Fediverse account, for example Lemmy or Mastodon. To do this, paste the following into the search field of your instance: !programming@programming.dev
Welcome to the main community in programming.dev! Feel free to post anything relating to programming here!
Cross posting is strongly encouraged in the instance. If you feel your post or another person’s post makes sense in another community cross post into it.
Hope you enjoy the instance!
Rules
Rules
Follow the programming.dev instance rules
Keep content related to programming in some way
If you’re posting long videos try to add in some form of tldr for those who don’t want to watch videos
You can already return complex data structures using jsonagg and recursive queries. I can see wanting to make that easier and more intuitive to do. Current sql does not restrict you to just 2D table data structures though.
I do think the idea is pretty neat, although it’s pretty close to returning structured data like json.
A slight disclaimer that these people are smarter than me, and know better about what we are talking about, so I may be wrong here on some assumptions. But I do get a bit of feeling they are trying to solve a trivial problem, at least in their use case. Ultimately there are only so many lecturers, and so many man lectures at a given time. The total data amount wouldn’t be so much, and you can easily group by and sort on client side to achieve the original table which is show on a per lecturer basis. A little redundancy is in my opinion preferred over a query that returns 3 tables that then needs additional complicated work. I also find arguments about overlapping names to not be something the database should be handling, it falls on the data owners/manager instead. Academia is a wild west at times, but either this table is presentation only or a link to lecturer or lecture. And in the latter case, you’ll already throw in the ids so they can be used in an URL to some other site.
While this can have significant less bandwidth, it also risks falling as soon as more data is introduced, as you’re putting the large join operations on the client when you can get free optimizations from the SQL engine you use. I know not having duplicate data could be a thing for something where I work, where essentially we have hourly breakdowns but fetch at least the entire day for a single set of parameters. So that means 24x data for a surprisingly high amount of columns. When we only need 2 of them on the hourly level! But in this case, the data doesn’t strictly need many joins as it has a lot of the information itself, along with there being too much data to join on the client side anyways for this to feel ideal. I feel you’ll increase the complexity a bit too much as well. A big advantage of sql is how easy it is to understand what you are getting.
Its somewhat of a solved problem, if the performance becomes a problem, since we can return nested data anyways. So we can already today technically return a row where the hour(I think, never tried a date before) and value columns have arrays instead of a single value. We just haven’t done it because it is not a big enough problem yet.
“an sequel”? Tell me I haven’t been saying it wrong this whole time 💀
There are four types of people:
Just run if they pronounce SQL as “Mongo”.
#5? (Me): pronounced S.Q.L. except for Microsoft’s product which is pronounced “sequel server”.
You forgot “squeal”
Plenty people say Ess Que Ell. And hence “an” would be correct to use.
An interesting concept, and I do agree that the post-join cost is something that we can probably safely ignore. But as I was reading it I was curious if a better way to start conceptually approaching the solution is to consider an n+1 table approach where any tables referenced will be returned (filtered to relevant rows and optionally omitting extraneous columns) along with an additional table containing necessary key references and any of the computed aggregates etc… this might shift the select phrase to instead of defining all desired columns to only specifying additionally needed columns.
But… I do have some objections to this concept it seems to place an extremely heavy value on the initial schema that SQL does not and causes difficulties in some scenarios when a single table is joined against multiple times for different purposes. I think it’d become difficult for the front end to decipher and rebuild the relationships without very heavy lifting.
It’s a really interesting idea and flips the established return structure on its head in a way I don’t hate.
Neat idea and solves e.g. the N+1 problem.
But doesn’t that just shift the DB logic (denormalization, filtering, aggregation) into the application code?
So it returns only the data that would be returned from the query, so the filtering is done.
I can see some uses of it. If you look at what something like Entity Framework does behind the scenes to return nested objects, you can see how something like this might help.
Yeah, the post on Reddit had some insightful comments as well.
I did not think of nested objects that may be returned by an entity framework before.
EF can have big problems with “Cartesian explosions” if an object has two lists of sub objects to return, it will get listA length x listB length items due to how the joins work. You can see how this leads to the explosion part of the name (with more objects or lists).
Their solution is a “split query” option, that does each sub table as a separate query, then seamlessly gives you the combined result.
If a change like this let’s you get those different table lists as distinct lists with the processing and round trip time of multiple requests then it could be a game changer.
(Source - my last week 🤣😭 + lots of EF docs)