3 ms·

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 duplicati

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.

fuzzy2 3mo 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 3mo 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.
  - CodesInChaos 3mo 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.
    - 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 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. 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.
        to11mtm 3mo ago
        Agree that Compression is not a good option for this; you'd wind up needing to decompress the rows anyway (or spend a good amount of time on trying to use the compressed symbols to detect a dupe, which I feel like would be very dependent on picking the right algo or maybe even a custom one.) 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.
  - mrighele 3mo 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
fabian2k 3mo 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.
- exceptione 3mo 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 3mo 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 3mo 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.
  - skeeter2020 3mo ago
    Relational 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 ago
There is a name for this problem, Cartesian explosion: https://en.wikipedia.org/wiki/Cartesian_explosion https://en.wikipedia.org/wiki/Cartesian_explosion
- exceptione 3mo 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 3mo ago
    I hope it is implemented with multiple result sets and a single roundtrip
    - queuep 3mo ago
      For sure it is
      - exceptione 3mo ago
        EF 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...

fuzzy2 3mo 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 3mo 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.
- CodesInChaos 3mo 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.
  - 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 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. 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.
        to11mtm 3mo ago
        Agree that Compression is not a good option for this; you'd wind up needing to decompress the rows anyway (or spend a good amount of time on trying to use the compressed symbols to detect a dupe, which I feel like would be very dependent on picking the right algo or maybe even a custom one.) 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.
- mrighele 3mo 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

fabian2k 3mo 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.

exceptione 3mo 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 3mo 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 3mo 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.
- skeeter2020 3mo ago
  Relational 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 ago

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

exceptione 3mo 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 3mo ago
  I hope it is implemented with multiple result sets and a single roundtrip
  - queuep 3mo ago
    For sure it is
    - exceptione 3mo ago
      EF 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...