I am working on a stored procedure that will perform time consuming query (> 30 seconds) inside the engine (arrays, temp tables, ...). The stored procedure return MULTIPLE result sets. The total output is large (> 100mb in some cases).
It is not possible to split the stored procedures into multiple stored procedure - there is lot of time consuming preprocessing that is used by multiple parts of the stored procedure.
The current implementation is returning the result via multiple result sets. This can be simulated by serializing the rows into strings (e.g., tab separated), and storing each generated row into a temp table. The client will be modified to split the result from the tab separated items.
My question: is there a way to emit partial results, instead of waiting for the whole stored procedure is completed? There is “return next”, but as much as I can tell nothing is returned to the caller / client until execution is completed.
I would solve that by returning a cursor for the complete large result set. Then the client can fetch as many rows at once as it wants to digest.
If processing takes long enough that you don't want all that to happen in a single database transaction, you can use a WITH HOLD cursor, but
that will bind resources on the server until you close the cursor
the whole result set will get materialized during the first commit