7

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...

bglee
  • 146
  • 5

1 Answers1

5

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.

LarsTech
  • 80,625
  • 14
  • 153
  • 225
  • Duly noted, but what if I don't have to check each row for some condition? In other words, what if I'm just interested in retrieving a whole subset of records for say populating a GridView? – bglee Oct 30 '12 at 15:33
  • 1
    @bglee You will have to site your source for why you think a DataReader is faster than a DataAdapter *in all circumstances*. They are just different tools for accessing data. DataAdapters are heavier objects because they can do a lot more things with the data, such as updating, deleting, etc. – LarsTech Oct 30 '12 at 15:44