EF Core 11 makes your split queries faster
steven-giesel.com
EF Core 11 makes your split queries faster
1–10 of 40 posts
Re: EF Core 11 makes your split queries faster
#2The 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.
Re: EF Core 11 makes your split queries faster
#3I 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.
Re: EF Core 11 makes your split queries faster
#4I 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.
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.
Re: EF Core 11 makes your split queries faster
#5I 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.
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.
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 R1R2.
---
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.
Re: EF Core 11 makes your split queries faster
#6I 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.
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.
Re: EF Core 11 makes your split queries faster
#7I 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.
There is a name for this problem, Cartesian explosion: https://en.wikipedia.org/wiki/Cartesian_explosion
Re: EF Core 11 makes your split queries faster
#8I 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.
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 tha…
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.
Re: EF Core 11 makes your split queries faster
#9I 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.
There is a name for this problem, Cartesian explosion: https://en.wikipedia.org/wiki/Cartesian_explosion
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.
Re: EF Core 11 makes your split queries faster
#10Earlier quoted context omitted.
There is a name for this problem, Cartesian explosion: https://en.wikipedia.org/wiki/Cartesian_explosion
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.
I hope it is implemented with multiple result sets and a single roundtrip