April 2, 2007 at 12:21 pm
When asked by a developer, "Should we call one proc with a couple of result sets or a couple of procs," my obvious answer was, "Call the one proc & return a couple of result sets."
Fast-forward.
They've written a procedure that returns 34 result sets. Now in addition to just the general insanity of pulling that much data at once, is there a concern specific to the fact that they're receiving this from a single procedure call? The one thing I'm aware of is that they need to process all the results on the ADO side by going through the NextResult command as fast as they can otherwise it will extend the time the read locks are held on the server. Anything else along these lines?
Oh, and I've been working on their definition of a "a couple" as compared to mine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2007 at 1:30 pm
You could have them insert their query results in declared tables ( declare @t table (...col defs...) ) first before returning any data to the client so there are no issues with locks.
April 3, 2007 at 5:19 am
That would eliminate any chance of holding locks, but it will put more of a load on tempdb since table variables are stored there.
I'm not really looking to radically change the process. I don't see an issue with ~3-6 result sets from a single call. It's the really large number that has me bothered.
Anyone else have any thoughts or suggestions in this area?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 3, 2007 at 7:51 am
April 3, 2007 at 9:21 am
True, and it's something I'm looking into, but it makes me even more nervous. As the article you link to states, if the app isn't processing this information as fast as the server is feeding it up, we could be filling up the buffers. Probably not an issue with a few (read ~3-6) result sets of moderate (read <10,000 rows) size. What about my issue, 36 seperate result sets. Wouldn't we almost inherently be looking at bigger issues?
Thanks for the assist.
Anyone have any other suggestions or comments?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply