6 ms·
EF Core 11 makes your split queries faster
- preetham_rangu 3mo agoThe real win in EF Core 11 is pruning the reference-nav joins out of split child queries, that's been dead weight since AsSplitQuery existed.
- exceptione 3mo agoI 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. Please enlighten me.
- fuzzy2 3mo agoIf you join multiple/many tables, you could end up with a large volume of data. And yes, this is bit-for-bit duplication—on the network. The query result is (typically) a single table. This table will get serialized as-is, with all duplicate data.
- exceptione 3mo agoCouldn't we make references as `byte offsets in the result set` work to handle duplication? Real memory pointers wouldn't work over the network of course, but if the database driver would return results like this, the client could easily stitch these together. My hunch is that even if we implement references on a higher level than raw byte offsets it would still be more performant than just returning R1*R2 bytes for any R1<1:N>R2. --- EDIT: According to LLM friends you could achieve before wire de-duplication by using FOR JSON AUTO in SQL Server or jsonb_agg in PostgreSQL. Not sure how much overhead that incurs though.
- CodesInChaos 3mo agoYou'd still return a multiplicative amount of rows, even if those rows contained only a reference. `array_agg` in postgres avoids this, but EF does not support using it for collection navigations. One could envision a "Cartesian product" operation in the wire protocol, but I'm not convinced that's a good approach.
- exceptione 3mo ago> 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 https://github.com/npgsql/efcore.pg/issues/2633
- CodesInChaos 3mo agoI 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: https://github.com/npgsql/efcore.pg/issues/2633 https://github.com/npgsql/efcore.pg/issues/2633 That issue is only about supporting `array_agg` as a function on tuples, not as an implementation strategy for `Include`s of collections.
- exceptione 3mo ago> 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 BlogPost part. To contrast, suppose your format needs 3 bytes for pointers, you will use `1 * 1KiB + 500 * 3B` for the BlogPost part. 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.
- fabian2k 3mo agoAssume you fetch a single customer entity with their 100 order entities as includes. With single query this will join both tables and produce 100 rows that contain the order data but also each one contains the customer data redundantly. Now Imagine you had two includes there, that will multiply the number of rows again. AsSingleQuery is as dangerous as this makes it sound. This works surprisingly well if you know that the number of included entities is low, but only then. You can get much better queries here if you write a Select() and let EF Core translate that into SQL. That will probably do roughly want you are imagining here, usually with subqueries fetching the data from related entities.
- exceptione 3mo agoDatabases are incredibly smart when it comes to fetching related data, a single select is indeed better than splitting queries and doing multiple roundtrips. The problem however is in how results are returned over the wire. Duplicating rows is needless, but seems to be still the standard.
- moomin 3mo agoMy experience suggests that they _can_ be good, but this particular pattern they can be remarkably bad at. Source: I keep having to optimise this pattern.
- kenniskrag 3mo agoI think compression would reduce the problem not? I think if you swap the wire format to something like jsonb you would need to parse it again anyway and pay the cpu time.
- skeeter2020 3mo agoRelational DBs have been around a very long time, and I suspect the processing/transfer time was not the bottleneck, while data access was, so redundant data with the standard relational model wasn't the primary concern.
- magicalhippo 3mo ago
- azibi 3mo agoThere is a name for this problem, Cartesian explosion: https://en.wikipedia.org/wiki/Cartesian_explosion https://en.wikipedia.org/wiki/Cartesian_explosion
- exceptione 3mo agoCorrect. However, this imho does not need to be a problem when using references to data instead of data duplication when sending results over the wire.
- sbergot 3mo agoI hope it is implemented with multiple result sets and a single roundtrip
- queuep 3mo agoFor sure it is
- exceptione 3mo agoEF Core documentation disagrees though: https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries https://learn.microsoft.com/en-us/ef/core/querying/single-sp...
- CodesInChaos 3mo agoWhat EF needs is support for using postgresql's `array_agg` when `Include`ing collections.
- tehlike 3mo agoI believe hasura or postgraphile does this for graphql. It's a very niche feature, and kills some of the optimizations if results are further used for filtering, but yes!
- glub103011 3mo agoI wish EF Core had first-class support for raw SQL, like Dapper.
- CodesInChaos 3mo agoIt has `context.Database.SqlQuery` and `ExecuteSql`.
- mexicocitinluez 3mo agoIt does as of EF Core 8 or 9. One of the challenges for their team I'm assuming is making sure the release notes also get copied to the actual documentation. This was one case where I thought the same thing and only until reading the release notes realized it was already a feature.
- CodesInChaos 3mo agoI 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.
- homebrewer 3mo agoIs this not what you want? Seems like it's part of the SQL standard. https://www.postgresql.org/docs/19/ddl-property-graphs.html https://www.postgresql.org/docs/19/ddl-property-graphs.html
- SkiFire13 3mo agoThat only seems to change how the query is performed, not which kind of data can be returned. For example how would you use it to return a list of events and, for each event, the list of attendees in that event?
- gavinray 3mo agoMULTISET 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-expressions/multiset-value-constructor/ https://www.jooq.org/doc/latest/manual/sql-building/column-e...
- lukaseder 3mo agoInformix also supports MULTISET natively. Many others support ARRAY, which is equivalent for all practical purposes. jOOQ popularised MULTISET over ARRAY because the existing ARRAY support was less user friendly, mapping results to actual Java array types.
- efromvt 3mo ago
- tehlike 3mo agoWe need EF Core / DLINQ or equivalents for pretty much all languages.
- mrkeen 3mo agoI'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.
- tehlike 3mo agoFor querying (not dml), prql is a little close to this but not quite.
- kogir 3mo agoI’ve always solved this with Multiple Active Result Sets and stored procedures. 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.
- ComputerGuru 3mo agoIf 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.)
- bacelarvtr 3mo agolooking 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