SQL - Calling "reader.NextResult" causes a long delay

Question

Our application executes a long, hairy stored procedure with multiple result sets. The users are experiencing long wait times for this query, so I set out to determine what is causing the delay.

I put a Stopwatch on executing and reading the data, and it takes 6-7 seconds each time. I timed the execution of the stored procedure, expecting that this would be taking all the time. It wasn't - it took 30ms or so.

So I put timers around each of the ~20 result sets. Each "block" took very little time ( < 10ms) except for one in the middle of the processing, which took 5-6 seconds. Upon further research, I discovered it was the "reader.NextResult()" call that took all the time. This long delay happens in the same spot each time.

If I just exec the stored procedure, it seems to run real snappy, so it doesn't APPEAR to be a problem with the query - but I don't know...

How do I interpret this? Is SQL shipping me the result sets as it gets them, and is the result set in question likely to be a problem area in my SQL query? Or is something else possibly causing the delay?

EDIT:

Thanks for the answer and the comments - I am using SQL Server and .NET

What I was most curious about was WHY my delay happens on the "NextResult()" call. Being new to SQL development, I assumed that a delay due to a long stored procedure execution would show up in my application while waiting for the "ExecuteReader()" call to return. It now seems that SQL will start returning data BEFORE the query is complete, and if there is a delay it will delay on the NextResult() call.

I started out thinking my delay was in the stored procedure. When the ExecuteReader() call came back quickly, I thought my delay was in my code's handling of the reader. When the delay ended up being on the NextResult() call, I was confused. I am now back to reviewing the stored procedure.

Thanks to those of you who took the time to review my problem and offered your help.

Answer 1

The answer will be dependent on what RDBMS you are using.

If its SQL Server and .NET then from my experience:

1. Check other open transactions on the same connection which is used to invoke the sproc. They may have row locks on the table one of your selects is executing against. You can try adding "MultipleActiveResultSets=false" to your SQL Server connection string and see if you get an improvement, or more likely an exception (and you can hunt down the problem from an exception). This can also be an effect from an unreset connection returned to the connection pool (something I've ran into since I've started to use MARS).

2. You may need to specify the NOLOCK (or READUNCOMMITTED, same thing) table hint in your SELECT query if dirty reads are acceptible.

   SELECT * FROM [table] WITH NOLOCK

Answer 2

When you execute a stored proc from a .Net command, the results will start streaming as soon as SQL has them ready.

This means that you may start seeing results in your .Net app before the entire stored proc has been executed.

Your bottleneck is probably in the stored procedure, run a sql server trace, and trace all the statements running inside the stored procedure (get the durations). You will be able to track down the exact statement in the proc that is slow and you also will be able to pick up on the params that are being passed to the proc so you can test this in Query Analyzer and look at the plan.

Another point that is missing from the question seems to be the amount of data you are moving, though unlikely, it may be that you have some really large chunks of data (like blobs) that are being sent and the time is being spent on the wire. You really need to expand the question a bit to help with the diagnosis.