We have a stored procedure that returns 3 different result sets, and the first two take approx. 1 sec while the 3rd takes 20+ sec.
When running the SP in SSMS all 3 result sets are displayed at once after an initial delay of ~20 seconds.
Likewise when calling from .NET there is no delay between each call to IDataReader#NextResult, but there is a delay on the first call to IDataReader#Read.
If we modify the SP to stop after the first 2 result sets then the data is available immediately.
We want to start processing the results of the 1st 2 sets immediatly and not wait for all 3 result sets to be ready.
Is there a way to force SQL to flush the first 2 results sets so they can be consumed immediately?
..for ssms…
--resultsets after 5secs
select 1;
select 2;
waitfor delay '00:00:05'; -- simulate a long running query
select 3;
go
--two resultsets and the last after 5secs
select 1;
select 2;
raiserror('', 0, 0) with nowait; -- flush results
waitfor delay '00:00:05'; -- simulate a long running query
select 3;