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!
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!
Michael_D
Execution of SP is almost always preferable.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
ASKER
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
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
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
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
ASKER
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
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
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
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
ASKER
Bob,
So... you're voting for the output parameter vice the result set?
So... you're voting for the output parameter vice the result set?
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
So it looks like we have 3 for parameters -- acperkins, Bob, and Racimo... only one for the resultset -- the anonymous DBA....
Thanks all!
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...
"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...
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.
>>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.
>>the anonymous DBA....<<
And we all know what DBA stands for: Don't Bother to Ask ... :)
And we all know what DBA stands for: Don't Bother to Ask ... :)
: )