I keep reading that SqlDataReaders are much faster than SqlDataAdapters because of their fast-forward, read-only, one-row-at-a-time connected nature, and that they are specifically faster than SqlDataAdapters when to populate a DataTable object (SqlDataAdapter.Fill(dataTable)).
However, here and there somebody will mention "it probably won't make a difference what you use because SqlDataAdapter uses a data reader internally to fill its table." If this is true, how exactly can the adapter be so much slower if it's communicating with the database by using an internal data reader anyway?
I know I could set up some tests and profile the performance of each one, but what I'd really like is for someone to shed some light on the alleged performance discrepancies if we're essentially dealing with the same process either way.
I understand that you'd typically use a reader to create a list of strongly-typed POCOs unlike the data adapter that just fills a table. However, my question is strictly about the details of the performance difference between the two and not O/RM concerns...
Using a DataReader produces faster results than using a DataAdapter to return the same data. Because the DataAdapter actually uses a DataReader to retrieve data, this should not surprise us. But there are many other reasons as well.
A SqlDataAdapter is typically used to fill a DataSet or DataTable and so you will have access to the data after your connection has been closed (disconnected access). The SqlDataReader is a fast forward-only and connected cursor which tends to be generally quicker than filling a DataSet/DataTable.
DataReader provides faster performance, but has read-only and forward-only access.
SqlDataReader is the fastest way. Make sure you use the get by ordinal methods rather than get by column name. e.g. GetString(1);
If you are using a DataReader, you can react to some information when reading the first row and even disregard the rest of the reading.
If you are using a DataAdapter, you have to first load the entire table and then read the first row in order to react to that same information.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With