When do selects execute for multiple resultsets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know how multiple resultset stored procedures are executed when
using data readers? It appears that the sp is paused after each select until
the client requests the next resultset.

Also, does the command timeout apply to the intial query time or for the
time it takes to start returning the final resultset?
 
Sure. Somewhat simplified, SQL Server executes each query and stops when its
initial buffer is full (less than 10K). When your code asks for rows,
(DataReader.Read) the buffer is emptied and more rows are retrieved--as fast
as you can pull them over. As you fetch more rows with Read, more rows are
located based on the query's WHERE clause etc. When the first resultset is
complete, the server pauses again waiting for the DataReader.NextResult.
Once that returns True, the process is repeated until there are no more
resultsets.

The CommandTimeout is measured from the time you execute the query until the
time the first row is made available by SQL Server.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
"server pauses again waiting for the DataReader.NextResult"
Does this mean that the 2nd query in a batch is not executed by SQL Server
until all of the data from the first query is processed by the client, or
does it depend on the amount of data returned?

Also, does the timeout reset for each query or are there other timeouts that
come into play.

My Scenerio:
I have sp that returns many resultsets. The other night I started receiving
SqlExceptions indicating a timeout occurred while processing the 7th
resultset in the batch. The particular query was experiencing a resource
(table) contention issue with a batch process running at the same (wrong)
time.

Thanks

Mike
 
I have taken another look at this issue and the 2005 version of SQL Server
that I'm running does not behave as I would expect. Let me investigate this
further with the folks on campus. For now, try increasing the CommandTimeout
but consider that it might be better to execute these procedures serially or
in smaller parts.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Your post reminded me that I forgot the version details. This is production,
so we are still on 1.1 and sql 2000 sp3.
As usual, this is inherited code, however, I do plan to recommend that we
break the batch up.

Mike
 
Back
Top