September 18, 2009 at 6:32 am
I have a stored procedure that has four different select statements. The four result set needs to be returned to the client.
Is it better to have the four return on one trip, or individually. I read somewhere that the trip to the database to get data should be once for better performance, and that multiple trips to the database reduces performance. But I also think four result sets in the pipe would also slow performance.
Which should I use, or is there some other strategy I could use?
Thanks. Any help will be appreciated.
September 18, 2009 at 7:53 am
Assuming you're talking about a situation where you're running SELECT... SELECT... SELECT... SELECT... and you're not putting all kinds of IF logic in there, then yes, running all four at once is a better method. Just make sure that the app is prepared to deal with multiple result sets. It's not hard, at all, but the developers need to know that they have to walk the result sets.
It cuts down on round trips to the database, and it doesn't add load to the system that wasn't going to be there anyway.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 21, 2009 at 6:30 am
You are correct in both cases. Four massive frequently-returned result sets will hammer your network.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply