SQL Server Performance Forum – Threads Archive
SQLDataAdapter
Hi<br /><br />I found article like this:<br /><b><i>"An Exclusive Interview with Author Fernando Guerrero<br />On How to Get the Most of ADO.NET and SQL Server"</i></b><br /><br /><br />Fernando wrote that avoid to use the SQLDataReader.<br /><i>"Try to use the ExecuteNonQuery method as much as possible, with SQLCommand objects, as this is the most efficient way to execute queries from ADO.NET. Use output parameters with SQLCommand objects if you need to retrieve just a few values, or a single data row, instead of using more expensive techniques, such as a <b>SQLDataAdapter</b>, a SQLDataReader, or a strongly typed DataSet."</i><br /><br />In other tips (ASP.NET/SQL) I saw that <b>SQLDataAdapter</b> is the fastest method<br />to retrieve a set of data on the page.<br /><br />So, where is the truth??? [<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />]<br /><br />Best regards<br />dgi
It is true that SQLDataAdapter object serves as a bridge between a DataSet object and a
SQL Server database and works quicker than any other methods. You can use SqlDataReader as an alternative solution to this problem. The SqlDataReader
provides more flexibility in handling store procedures that return multiple result sets. I beleive Author’s reference is not contradictory, in the cases where if you can get better performance without using SQLDataAdapter use SQLCommand objects and if not use the Adapter method. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks for replay <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />I made mistake in my previous post.<br />I meant SqlDataReader. I saw in "ASP.NET/SQL" section<br />that SqlDataReaderis the fastest method<br />to retrieve a set of data on the page, of course.<br />Sorry for my mistake.<br /><br />Best regards<br />dgi
SqlDataReader is the most efficient way to retrieve a *set* of data.
I think the author is saying is that when you dont need a full set (ie, result set), you can help improve performance by using ExecuteNonQuery, and retrieving the data back using lightweight output params instead of passing them back inside a fully fledged result set
]]>