I don't understand the argument why `AsSplitQuery` could be more performant than a single round trip involving a multi join query. People mention data duplication and increased memory usage, but I would assume that `duplication` is just a matter of an extra pointer, not a bit-for-bit duplication of every reference to a single row.
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 still relational.
I guess technically it is in the SQL standard, but optional, as S098? I agree that SQL is sorely lacking here and I'm hoping that the OLAP side innovation (presto, bigquery, snowflake, duckdb all seem to do better) help push it forward.
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) 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.
If op is here: you can dramatically improve the robustness and soundness of your benchmark with one simple trick: you can run old versions of ASP.NET (Core) frameworks on newer .NET runtimes with no other changes; i.e. instead of benchmarking ASP.NET 10 + EF 10 on .NET 10 vs ASP.NET 11 + EF 11 on .NET 11, you can bench ASP.NET 10 + EF 10 on .NET 11 vs ASP.NET 11 + EF 11 on .NET 11
(I always upgrade projects by first upgrading the runtime and checking everything then separately (and maybe much later!) upgrading the framework.)
looking at the benchmark it doesn't seem to be much of a difference, could someone please explain to me why is this little gain in performance so much important? especially when it did increased the GC work? or I didn't understood the data right? ty in advance
13 comments
[ 6.3 ms ] story [ 64.2 ms ] threadPlease enlighten me.
Alternative query languages like EdgeQL show what first class support for nested data (and navigations) could look like, while the data model is still relational.
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...
<array aggregate function> ::= ARRAY_AGG <left paren> <value expression> [ ORDER BY <sort specification list> ] <right paren>
I hope this helps
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.
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.
(I always upgrade projects by first upgrading the runtime and checking everything then separately (and maybe much later!) upgrading the framework.)