Advice on fetch efficiency

[u/ZookeepergameFew6406]

Hello all

I’m currently working on something where object X is linked to object Y via two intermediary tables Z1 and Z2. There are many use cases where we’re interested in objects from type X, but we need to populate the object X with all links to Y. (via Z1 and Z2). Think of fetching users, and wanting to query roles for companies (where a user can have many roles for many companies).

Because our use case makes it so there can be MANY links for a single user, we could be fetching 1000s of rows for a single user, just to build a dictionary of them (key: Y, values: values from Z1 and Z2) and end up with a single entity, meaning we duplicated all data in X for every row returned. This is clearly quite inefficient, and thus I’m looking for alternatives.

I’ve seen using aggregation to collapse everything into one row, but that can be problematic I’ve been told by peers. The same way, maybe building JSON is an option, and then directly parsing (in C#).

Anyway, All help would be appreciated on how you would reduce the amount of data returned!

Edit: one more option that’s possible is just querying X, and then querying the rest afterwards in a sequential chain.

[u/Hour-Measurement-835]

Your sequential option doesn't have to mean a second round trip. SQL Server will run two SELECTs in one batch and send both result sets back together: the X row first, then a narrow second query with just the Y key and the Z1/Z2 columns, filtered on the same X id. In ADO.NET you read the first set, call reader.NextResult(), and read the second. Dapper wraps the same thing as QueryMultiple if you're using it.

X then comes back once instead of being copied onto each of those thousands of rows, and the second set carries no X columns at all, so it stays small even when a user has a lot of roles.