All,
Assuming I am passing a single row of data, is there any performance advantage to using a series of parameters or a single row data-set to return data from SQL Server?
e.g., the SQL "SELECT * FROM MyTable WHERE MyID = 1" would return, say...
MyID Field2 Field3
1 A B
Would I be better declaring two extra params (@Field2 output, @Field3 output) or just allowing the sproc to return the dataset?
This is probably more of an ADO question than a SQL Server question but I'm guessing anybody using SQL Server in earnest will probably be OK with ADO too :).
The real world scenario is that I want to return two sets of data from a single sproc: one single-row set representing a single object and one multi-row set representing a list of its children. There is no significant difference in coding complexity (using .NextRecordset or whatever it's called if I use two recordsets) so I was wondering if there is any other reason to choose one way over the other.
Please, I am after a full explanation of why one way would be better, or why there is no difference, so full points for a detailed answer only.
Cheers,
s46.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Personally, so do I!!!
But I was working on an app recently and only had one value to get back other than the RetVal. In that case, I decided that it was simpler to just add another parameter and it sort of went from there.
It's become more of a curiosity thing really - is there really any advantage to either approach or is it just down to whatever is easier / quicker / fits in best with company policy?
Anyway, cheers for your view,
s46.
AngelIII,
for information, a colleague suggested I look at the following link. In short, it recommends using parameters instead of recordsets in single-row returns to avoid the overhead of generating the recordset object. Also, the meta-data used to define the recordset can be a lot larger than the data itself (more relevant the smaller the amount of data returned, presumably) so it puts less load on the network, too.
http://www.devx.com/tips/T
Regards,
s46.
Yes, I know about that issue, which in fact was I was speaking about.
I never tested where the threshold was, doing it kind of a intuitive choice as to when use parameters and when not.
Actually, most cases the procedures that returned data where like "create a new row and return me only the auto-generated key back"
->return parameter
If I needed the entire row, in order to present the application tier the default values generated on the database level, then I returned the full row(s), as in most cases, there were dozens of columns then...
Ah, I see.
Well, I guess I'm probably going to stick to using params for single row returns (still not decided if it's more or less typing!), mainly because that's that way I've already been doing it and I may as well be consistent.
The recordset overhead issues hadn't occured to me, to be honest, so it's good to know that I'm doing things the more efficient way, whether by luck or management!
Cheers again.
s46.
Business Accounts
Answer for Membership
by: angelIIIPosted on 2005-07-18 at 06:33:04ID: 14465912
Actually, i prefer the way of returning multiple recordsets from a stored procedure over the return parameters.
I only use stored procedures with return parameters when I need the results in TSQL...
There might be some minor performance gain though when using the output parameters, but that's up to be tested with some sample applications...