EF Core 11 makes your split queries faster

rrellem 8 days ago 28 commentsRead Article on steven-giesel.com

⚡ Community Insights

Discussion Sentiment

63% Positive

Analyzed from 844 words in the discussion.

Discussion (28 Comments)Read Original on HackerNews

CodesInChaos•about 4 hours ago

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.

gavinray•40 minutes ago

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...

homebrewer•about 3 hours ago

Is this not what you want? Seems like it's part of the SQL standard.

https://www.postgresql.org/docs/19/ddl-property-graphs.html

SkiFire13•about 2 hours ago

That 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?

exceptione•about 8 hours ago

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.

Please enlighten me.

azibi•about 7 hours ago

There is a name for this problem, Cartesian explosion: https://en.wikipedia.org/wiki/Cartesian_explosion

exceptione•about 6 hours ago

Correct. 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•about 6 hours ago

I hope it is implemented with multiple result sets and a single roundtrip

fabian2k•about 7 hours ago

Assume 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.

magicalhippo•32 minutes ago

Out main screen has at head level over 200 fields, most of them filled in for typical orders. The main table has several child tables, some which have several child tables again. The order line table has several, and especially one of them can typically have 5-7 rows per order line.

Many of our customers routinely dealt with orders that have 10k+ lines. So if you did it all in one go, you've now turned 200 head fields into 10 million that needs to be transmitted to the client and deduplicated there.

We have the root primary key on all child tables, so we fire off a handful of queries to load the complete data set for an order. Very fast as it's all indexed of course.

exceptione•about 6 hours ago

Databases 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•about 4 hours ago

My 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•about 3 hours ago

I 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.

fuzzy2•about 7 hours ago

If 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•about 7 hours ago

Couldn'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.

mrighele•about 3 hours ago

I think jsonb_agg could be a solution. Requires cpu for decoding but I think it is compensated by the reduction of on the wire traffic. Queries are different , especially if you have several one-to-many joins so you have to do it at the framework level. I wonder if it would be feasible a Postgres extension that does this automatically for you

CodesInChaos•about 4 hours ago

You'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.

tehlike•about 4 hours ago

We need EF Core / DLINQ or equivalents for pretty much all languages.

CodesInChaos•about 5 hours ago

What EF needs is support for using postgresql's `array_agg` when `Include`ing collections.

tehlike•about 4 hours ago

I 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!

preetham_rangu•about 10 hours ago

The 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.

glub103011•about 5 hours ago

I wish EF Core had first-class support for raw SQL, like Dapper.

CodesInChaos•about 4 hours ago

It has `context.Database.SqlQuery` and `ExecuteSql`.

mexicocitinluez•about 3 hours ago

It 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.