[kogir]

I’ve always solved this with Multiple Active Result Sets and stored procedures.

Collect the data in the stored procedure with temp/in-memory tables and return minimal, non-duplicated, related result sets.

Single round trip, still accumulates results in efficient bulk batches, and allows results to be processed by the client as they stream in.