31–40 of 40 posts
Collect the data in the stored procedure with temp/in-memory tables and return minimal, non-duplicated, related result sets.
Single round trip, still accumulates results in efficient bulk batches, and allows results to be processed by the client as they stream in.
We need EF Core / DLINQ or equivalents for pretty much all languages.
Joking (but not really) aside, EF seems to be the easiest way to shoot yourself in the foot, and write code which you think is transactional, but is not actually transactional, and would be transactional if expressed in pure SQL.
(I always upgrade projects by first upgrading the runtime and checking everything then separately (and maybe much later!) upgrading the framework.)
I think it's crazy that standard SQL has no clean way of handling nested data. It's might not fit elegantly into the relational model, but it's still a common business problem that should be addressed. At the bare minimum something like `array_agg` should be standardized. Alternative query languages like EdgeQL show what first class support for nested data (and navigations) could look like, while the data model is st…
Earlier quoted context omitted.
> You'd still return a multiplicative amount of rows, even if those rows contained only a reference Sure, but a pointer is still a massive win over records, and I think a further cartesian product wire protocol extension would not be worth the hassle. > `array_agg` in postgres avoids this That one is tracked here: https://github.com/npgsql/efcore.pg/issues/2633
I don't think pointers (beyond a simple "same as in previous row" marker) will be a huge improvement, since you still get a multiplicative number of rows. And it comes with the cost of keeping all that data in memory. This approach also competes with using cheap compression (e.g. LZ4). Some kind of "product" operator on the other hand reduces the cost to additive (just like `array_agg`). > That one is tracked here: h…
> I don't think pointers (beyond a simple "same as in previous row" marker) will be a huge improvement, since you still get a multiplicative number of rows
SELECT BlogPost bp LEFT JOIN Comment c WHERE bp.id=101 AND c.blogPostId=bp.id
Compression and decompression takes CPU-time, I have a hunch this brings in an unacceptable penalty. At least this route hasn't been chosen while it would be the easiest to implement.
We need EF Core / DLINQ or equivalents for pretty much all languages.
I'll go you one further. We need a standard higher-level DSL which abstracts over the various competing data access libraries in a portable, declarative way, such that 1) database engines can independently optimise themselves to better handle such a language, and 2) programmers can move between different companies and be expected to already know this language. I propose the name SQL for this. Joking (but not really)…
Earlier quoted context omitted.
I don't think pointers (beyond a simple "same as in previous row" marker) will be a huge improvement, since you still get a multiplicative number of rows. And it comes with the cost of keeping all that data in memory. This approach also competes with using cheap compression (e.g. LZ4). Some kind of "product" operator on the other hand reduces the cost to additive (just like `array_agg`). > That one is tracked here: h…
> I don't think pointers (beyond a simple "same as in previous row" marker) will be a huge improvement, since you still get a multiplicative number of rows A marker as "byte offset in response data" is very efficient. Consider this: SELECT BlogPost bp LEFT JOIN Comment c WHERE bp.id=101 AND c.blogPostId=bp.id If the BlogPost is 1KiB in size and has 500 comments, doing it naively will return `500 * 1KiB` for the BlogP…
As far as your idea, I think where a lot of the challenge lies in that it complicates a lot of the up-front protocol.
Some of these DB protocols are downright ugly [0], and need to have a very long tail for supporting systems built on older versions. And they can be very finicky thus people are afraid to touch both the DB side and the Driver side.
So, at best you'd need to have a special connect string and then work with driver teams and/or vendors to understand the new format, deal with all of the teething issues, and will have to continue to support the old protocol in your DB code for at least another decade (probably longer for any commercial product.)
[0] - TDS comes to mind, I'm betting TTC/OPI is not fun either.
I think it's crazy that standard SQL has no clean way of handling nested data. It's might not fit elegantly into the relational model, but it's still a common business problem that should be addressed. At the bare minimum something like `array_agg` should be standardized. Alternative query languages like EdgeQL show what first class support for nested data (and navigations) could look like, while the data model is st…
::= ARRAY_AGG [ ORDER BY ]
I hope this helps
I think it's crazy that standard SQL has no clean way of handling nested data. It's might not fit elegantly into the relational model, but it's still a common business problem that should be addressed. At the bare minimum something like `array_agg` should be standardized. Alternative query languages like EdgeQL show what first class support for nested data (and navigations) could look like, while the data model is st…
MULTISET is the SQL native way of doing this but it's only implemented in Oracle, and most people have never heard of it jOOQ emulates this feature for arbitrary databases and has the best explanatory article on the web about MULTISET imo https://www.jooq.org/doc/latest/manual/sql-building/column-e...