Returning an output parameter versus returning a single row (with only one field) from an sproc

Is it better to return a declared (output) parameter from an sproc, or a select statement such as "SELECT COUNT(*) FROM MyTable"?

Please answer this question in the context of the ADO .NET objects that allow the user to return only a scalar value from a stored procedure using ExecuteScalar. While I know either will return the same data to the client application, I was wondering which approachwould result in less server load and less netork traffic.

Thanks!
LVL 24
Jeff CertainAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael_DCommented:
Execution of SP is almost always preferable.
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
As a general rule of thumb, declaring explicitely input output parameters for single outputs allows for better memory management.  Hope this helps...
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Besides using stored procedures, allows for leveraging SQL Server caching capabilities as well as allows to be in 3-tier applicative model which fits MS applicative platforms (including DOT NET)
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Jeff CertainAuthor Commented:
Michael_D,

I think you missed the point of the question. I always use sprocs, unless the query string must be completely dynamic for some reason. My question is whether it is better to write the sproc like this (p-code):

create sproc
select Count(*) from myTable
go

or this:
create sproc
declare @result int
select @result = Count(*) from myTable
return @result
Bob LearnedCommented:
Jeff,

Are you talking in a generic sense or a specific one?  Do you have a specific stored procedure in mind?

I know that ExecuteNonQuery returns the number of rows affected, but ExecuteScalar doesn't.

There is the @ReturnValue method.

Reference:
Return Values: Getting Something Back from Your SQL Server Stored Procedures
http://www.sql-server-performance.com/tn_stored_procedure_returns.asp

Bob
Jeff CertainAuthor Commented:
Bob,

I'm speaking in the specifically general sense of any sproc that returns a single value that is discernable through a select query -- the sort you'd call ExecuteScalar to retrieve.

One example (in addition to the count example above) might be an update query that returns @@IDENTITY -- you can do this by SELECT @@IDENTITY or SELECT @result = @@IDENTITY; return @result or return @@identity.... which is more betterer?

Another example (in addition to the trivial count example above) might be
SELECT SUM(myField) FROM myTable WHERE someField=someValue
vs
SELECT @result=SUM(myField) FROM myTable WHERE someField=someValue

Hope this helps clarify the question.

Jeff
Bob LearnedCommented:
Jeff,

If you just need a count and not the resultset, then I feel that it would betterer if you just return the value, and not a result indicating the count (IMHO).

Bob
Jeff CertainAuthor Commented:
Bob,
So... you're voting for the output parameter vice the result set?
Bob LearnedCommented:
Also, the overhead difference between the two methods would be more significant depending on the number of simultaneous hits.  Not significant for 5-10 users, but 1 million might make a big difference.

Bob
Bob LearnedCommented:
Marshalling an integer vs a resultset?  Integer is my vote :)

Bob
martie_11Commented:
I asked one of the DBAs this question, here was the response:

"I would say that the Select is better.....With the web apps that I've built in the past, I have always returned a single row."

Not much insight is there : (

Regards.
Anthony PerkinsCommented:
As requested:

The answer to that used to be a no brainer :) Use an output parameter(s) if you are only returning discrete values.  But with the introduction of ADO.NET's ExecuteScalar MS has made it less obvious, when you are returning a single value.  I believe it becomes a personal preference.  I still prefer to return an output parameter, if only to reduce the network traffic.  I don't know this for sure (Profiler would probably answer this), but I suspect when you do a ExecuteScalar method a resultset is still generated and ADO.NET "resolves" it at the client level by retrieving just the first column from the first row.

So in summary, I have not read the definitive article on this and in any case the difference may just be nanoseconds.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff CertainAuthor Commented:
Thanks, acperkins... figured it was only fair that you get in on this too -- since you answered the post I hijacked to ask this the first time.. :)

So it looks like we have 3 for parameters -- acperkins, Bob, and Racimo... only one for the resultset -- the anonymous DBA....

Thanks all!
martie_11Commented:
When I asked the DBA 'Why?", here's what he replied:

"It's simpler for one, I don't think it has a major performance issue.  A stored proc may use the same memory, but CPU is about the same."

Again, not much insight there...
Anthony PerkinsCommented:
Bob,

>>I know that ExecuteNonQuery returns the number of rows affected, but ExecuteScalar doesn't.<<
Not entirely true.

ExecuteScalar can return the rows affected. But (and this is the major caveat) you have to have SET NOCOUNT OFF

>>Marshalling an integer vs a resultset?  Integer is my vote :)<<
I agree with you here, but what if ADO.NET is intelligent enough to convert what is ostensibly a resultset (using ExecuteScalar) to an output parameter.  I suspect only through looking at MS Profiler can we know the answer to that.

In any case, it is a good discussion, albeit I suspect somewhat academic as I cannot believe the difference can be noticeable.
Anthony PerkinsCommented:
>>the anonymous DBA....<<
And we all know what DBA stands for:  Don't Bother to Ask ... :)
martie_11Commented:
: )
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.