Jereme Guenther (10/25/2007)
So what is the advantage of OUTPUT parameters over a standard result set? From my experience they are much more of a pain to deal with both in coding and in maintenance. I don't see how they could be faster from the SQL side since the sproc needs to finish executing one way or the other.The only advantage I can see is that .NET (or whatever language you are using) doesn't need to instantiate another object (like the datareader or dataset) for such a small amount of data.
Once I have gone down the path of OUTPUT parameters I have a couple extra places that code has to be updated every time I want to return an additional value. I have to pass in the new parameter, I have to add it to the sproc, I have to assign it in the sproc, and I have to retrieve it in .NET.
If I am not using OUTPUT parameters then I simply add a new value to the select list in the sproc and add one more line in .NET to retrieve it.
My final problem with OUTPUT parameters is that I can only return single values with them, so if I ever decide to change the data call to a two or three record result set I have to rewrite everything pertaining to the data retrieval call. So I can see in an area where speed to the last millisecond is absolutely essential they could be a really good thing, but other than that they seem like a bit of a pain.
Am I missing something here? What are your thoughts on this?
I think you've covered everything correctly. The one advantage I see is that in instances where you know that you're only ever going to return very small sets of data, say an ID value from a single row insert procedure, then the amount of work done by the server is reduced because sending the output parameter requires a little less processing than marshalling a result set. You also save a touch on network traffic because the output parameter is going to be smaller.
However, these are tiny, incremental, performance increases, realized after you've already squeezed everything you can out of the system. I wouldn't recommend implementing output parameters everywhere, but they do have a place and using them or not depends on the situation.
"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