What's Better: Dataset or Datareader

What's better: DataSet or DataReader?

That is essentially: "which is better: a bucket or a hose?"

A DataSet is the bucket here; it allows you to carry around a disconnected set of data and work with it - but you will incur the cost of carrying the bucket (so best to keep it to a size you are comfortable with).

A data-reader is the hose: it provides one-way/once-only access to data as it flies past you; you don't have to carry all of the available water at once, but it needs to be connected to the tap/database.

And in the same way that you can fill a bucket with a hose, you can fill the DataSet with the data-reader.

The point I'm trying to make is that they do different things...

I don't personally use DataSet very often - but some people love them. I do, however, make use of data-readers for BLOB access etc.

what can be best - Dataset or datareader?

If you will have a large amount of people reading the data, then you should use the DataReader paradigm. This way you can quickly get in and out with the trouble of schema inference. I would also recommend that once you pull the data from the server that you cache it. Even if it is only cached for 1 second, that will improve the number of connections from the database that will retrieve the same data. Otherwise, you could quickly saturate your connection pool if you are not careful as well as some possible locking.

Is datareader quicker than dataset when populating a datatable?

The DataAdapter uses a DataReader under the hood so your experience would likely be the same.

The benefit of the DataAdapter is you cut out a lot of code that would need maintenance.

This debate is a bit of a religious issue so definitely look around and decide what works best for your situation:

• http://blogs.msdn.com/memilavi/archive/2008/02/18/datareader-vs-dataset-the-real-question.aspx
• http://xcskiwinn.org/community/blogs/panmanphil/archive/2004/09/21/281.aspx
• http://weblogs.asp.net/joelevi/archive/2008/02/12/asp-net-datareader-vs-dataset.aspx
• http://weblogs.asp.net/dreilly/archive/2003/09/27/29411.aspx

Is DataSet slower than DataReader due to...?

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.

Why is DataTable faster than DataReader

I see three issues:

1. the way you use a DataReader negates it's big single-item-in-memory advantage by converting it to list,
2. you're running the benchmark in an environment that differs significantly from production in a way that favors the DataTable, and
3. you're spending time converting DataReader record to Artifact objects that is not duplicated in the DataTable code.

The main advantage of a DataReader is that you don't have to load everything into memory at once. This should be a huge advantage for DataReader in web apps, where memory, rather than cpu, is often the bottleneck, but by adding each row to a generic list you've negated this. That also means that even after you change your code to only use one record at a time, the difference might not show up on your benchmarks because you're running them on a system with lot of free memory, which will favor the DataTable. Also, the DataReader version is spending time parsing the results into Artifact objects that the DataTable has not done yet.

To fix the DataReader usage issue, change List<ArtifactString> to IEnumerable<ArtifactString> everywhere, and in your DataReader DAL change this line:

artifactList.Add(artifact);

to this:

yield return artifact;

This means you also need to add code that iterates over the results to your DataReader test harness to keep things fair.

I'm not sure how to adjust the benchmark to create a more typical scenario that is fair to both DataTable and DataReader, except to build two versions of your page, and serve up each version for an hour under a similar production-level load so that we have real memory pressure... do some real A/B testing. Also, make sure you cover converting the DataTable rows to Artifacts... and if the argument is that you need to do this for a DataReader, but not for a DataTable, that is just plain wrong.

Where to use dataset and datareader. What is recommended

If you execute just select on your queries, you can use ExecuteDataReader

in order to increase your performance.

Link MSDN about performance report : http://msdn.microsoft.com/en-us/library/ms978388.aspx

ADO.NET data table vs. data reader

Yes, the data reader is definitely the most efficient - but you do not want to keep a connection open for a long period of time!

• use the DataReader to read your data into an entity object; open the connection, read the data, close the connection
• do whatever you need to do with your business object
• store the changes back, e.g. by using an ad-hoc SQL query, a stored procedure, or whatever else you want; again: open the connection, write back the changes, close the connection

This is probably the most efficient you can get - it's a bit of work, some boring code, and all, but it's about as fast as it can be.

If you're more interested in developer productivity than raw speed, why not use some kind of an ORM to do all this boring, annoying mapping back and forth? Saves you a lot of coding and messy stuff to maintain!

what is the difference between data adapter and data reader?

Please see DataReader, DataAdapter & DataSet - When to use? :

ADO.NET provides two central Data
Access Components. The excellent thing
is that, they are common across all
Databases, be it SQL Server or other
competitive databases. Its only the
namespace to be used, that differs,
while using a Database other than SQL
Server.

---

Related Topics