1 Row datatable or output parameters? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

1 Row datatable or output parameters?

I was wondering if anyone could shed some light on what would be faster? I have a query that will always return 1 row which will be on average about 5k in size. On one hand I use ado.net and the SqlDataReader to retrieve this one record and get the relevant fields that way. The other option is to use output parameters, I’ve read that this is usually faster in ado but I haven’t read anything about ado.net. Also I’m not convinced that the overhead of having to declare all the parameter objects, add them to the sqlcommand and then set them all in the stored procedure will out perform returning the 1 record with about 8 fields. Anyone know how Sql-Server performs on this, and again on the ado.net side of things.. (eventually I will performance test this!). Thanks
Shaun World Domination Through Superior Software
I just returned for the SQL PASS seminars in Seattle, and there was a session I attended just on this subject. I have not done this testing myself, but the speaker has done some testing and here is a summary of what he said well help boost overall ado.net performance: –DataReaders are always faster than using DataSets
–Always refer to the columns by ordinal reference
–Inline code is much faster than using controls
–Don’t use databinding expression in templates, instead use ItemDataBound instead I hope this helps a little. I haven’t had much opportunity to spend with ado.net yet and I am still mastering it.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Thanks, I’m aware of the first 3 but the 4th was something interesting.
On the subject, I found out that executing the stored proc in an ado.net Command object with the ExecuteScalar method is optimised for returning a single value and can be used to return a single row so it looks like this is also an alternative. (Oh and of course, always use the SqlClient library in .net instead of the OleDb one if you’re certain you’ll be sticking to Sql-Server) Cheers
Shaun World Domination Through Superior Software
The reference to output parameters being faster the SqlDataReader applies to the client side, so this is really important for 3-tier architecture, with an application server that pools client requests basically, there is a lot overhead to setting up rowset handling on the client side i am not absolutely certain, but i do not believe SQL Server itself cares as to whether data is sent back via a row set or output parameters
]]>