Output Parameters versus Recordsets in Stored Procedures

[Darter Dan]

I've read that stored procedures should use output parameters instead of
recordsets where possible for best efficiency. Unfortunately I need to
quantify this with some hard data and I'm not sure which counters to
use. Should I be looking at the SQL Server memory counters or something
else.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Darter Dan (da*******@darter.com) writes:

I've read that stored procedures should use output parameters instead of
recordsets where possible for best efficiency. Unfortunately I need to
quantify this with some hard data and I'm not sure which counters to
use. Should I be looking at the SQL Server memory counters or something
else.

There was an article on this in SQL Server Professional, quite a few years
ago. Their archive is on www.pinpub.com. But this was a test on 6.5, and
I have a vague recollection of the aame author arriving at conflicting
results for SQL2000. But I could be wrong.

Anyway, if you want to benchmark yourself, you should simply measure
execution time on the client side. You will have to make a lot of calls
to get significant results, I would suppose.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp