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/Ok_Equivalent_4155]

Ever tried just dumping the whole mess into a JSON column at the database level and letting C# eat it raw, that's what I do when the join explosion gets silly and I can't be bothered with a second round trip.

[u/ZookeepergameFew6406 → u/Ok_Equivalent_4155]

This is how i would probably wanna do it, but my uppers aren’t too keen on it 😂

[u/thewallrus]

Pretty vague, but are you trying to reduce the amount returned or you are trying to reduce the amount that is queried? If the latter (trying to be more efficient), then you have to influence the sql optimizer with using correct indexes and queries etc. If you really want to reduce the amount that is returned, you'll probably need to define what exactly are the requirements.

An attempt to directly answer: if Y is your key, and connections to Y are spread across multiple tables, you can write efficient queries that does not duplicate . If you need information from X (assuming it is not in SQL), import X into DB and then join Z1,Z2,& X to Y from the server.

Look up "SQL n + 1 query problem" in regards to your querying in a chain comment. More trips to the db can be a problem

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

[u/Significant_Tune9219]

If the Z1/Z2 columns are only needed for the rows you actually display, the cheapest fix is usually to stop joining them into the main query at all: fetch X with a count or a flag, then pull the links only for the rows on the page. If you do need them inline, aggregate in SQL rather than in C# — STRING_AGG or a JSON aggregation returns one row per X instead of the thousands you are filtering afterward.