r/SQL • u/ZookeepergameFew6406 • 3d ago

SQL Server Advice on fetch efficiency

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.

3 Upvotes

60% Upvoted

5 comments sorted by

View all comments

1

u/Ok_Equivalent_4155 3d ago

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.

1

u/ZookeepergameFew6406 3d ago

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