Is DataSet slower than DataReader due to...?

Question

Hello,

1)

A) DataSets can be 10+ times slower than DataReader at retrieving data from DB. I assume this is due to overhead of DataSets having to deal with relations etc.

B) But is the speed difference between DataSets and DataReader due to DataSets having to retrieve more data ( information about relations ... ) from DB, or due to application having to do more processing, or both?

C) I assume DataAdapter uses DataReader under the hood and thus the number of commands application needs to execute in order to retrieve 100 rows with DataAdapter is equal to or greater than number of commands app needs to execute if these 100 rows are retrieved directly by DataReader?!

2) Does DataReader retrieve one row at the time or one field ( of a particular row ) at the time?

Thank you

Answer 1

A few pointers on MSDN:

• Benchmarks
• DataSet vs. DataReader
• Working with DataReaders, DataSets, DataAdapters, and DataViews

Answer 2

Have a read of Why I Don't Use DataSets in My ASP.NET Applications by Scott Mitchell. It covers the salient points you are interested in, I think.

Answer 3

There are some different types of overhead that can occur when using a DataSet over a DataReader:

A DatSet contains DataTable objects, which contains DataRow object, that contain the data. There is a small overhead creating all the objects. Each DataRow treats all it's values as objects, so any value types are boxed which adds a bit of overhead for each field.

When you use a DataAdapter to populate a DataSet, it's easy to get a lot of data that you won't use. If you don't specify what fields you want, you get all the fields even if you won't use them all. If you don't filter the query, you get all the rows from the table. Even if you filter them later with a DataView on the DataTable, you still have fetched them from the database. With a DataReader you are closer to query that gets the data, so the connection to what you get in the result is more obvious.

If you fetch data into several DataTable objects in a DataSet and use relations to let the DataSet combine the data, you make the DataSet do work that you could have let the database do, which is more optimised for it.

If you use a DataSet well, the overhead is not that bad, rather 30% than 1000%.

You are correct to assume that a DataAdapter uses a DataReader. If you are careful how you use the DataAdapter, the database operations itself is the same as if you use the DataReader yourself.

A DataReader will fetch a record at a time from the underlying database driver, which in turn will fetch a buffer full of records at a time from the database. If the records are very large only one at a time might fit in the buffer, but usually there are tens of records in the buffer or even hundreds if they are really small.

Answer 4

A DataReader is akin to the old ADO notion of a forward-only recordset. A forward-only recordset does not need to maintain any kind of pointer information and as such is always faster than traditional DataSet instances. A DataReader, therefore, does not cache any data and needs an open connection to the database to obtain the underlying data.

This explains why you need to write the following code for a DataReader:

DataReader reader = GetANewDataReaderInstance();
while (reader.Read())
{
    ...
}

Now versus a DataSet, things change quite a lot. In essence you can pull in a subset of the database schema into a DataSet, things like table relations, delete and update rules and of course, automatically repopulating rows inside a dataset when you submit data to the data store. DataSets can also operate in a "disconnected" fashion, which means that you can obtain the data you need and the close the database connection.

Summary
All this functionality however does come at a cost: if you are looking to populate custom business objects as quickly as possible, go with DataReaders otherwise go with DataSets.

Answer 5

I prefer to use DataReader to populate Lists.

You can check out my Question I recently asekd about this with some good comments/answers:

Datasets

Answer 6

Be very careful about what you find in Internet searches. The article you posted says

A VB.NET application written with Release Candidate one of .NET

That's about seven years ago!

Be sure to see something a little more current:

• New DataSet Features in ADO.NET 2.0
• Asynchronous Command Execution in ADO.NET 2.0