Is datareader quicker than dataset when populating a datatable?

Which would be quicker.

1) Looping a datareader and creating a custom rows and columns based populated datatable

2) Or creating a dataAdapter object and just (.Fill)ing a datatable.

Does the performance of a datareader still hold true upon dynamic creation of a datatable?

Solution 1:

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

Solution 2:

Assuming you actually want all the data coming back from the database, the time taken at the database and on the network is almost certain to dwarf the time taken in-process to populate data structures in memory.

Yes, in some cases you might get a small saving by using DataReader - and in particular if you want to stream the data it may be useful - but if you do actually need it all, I'd stick with the simplest code. If you believe that the DataSet population is causing a significant performance problem, profile it and then try to improve it.

Solution 3:

Your option #1 would be slower. However, there's a better way to convert a datareader to a datatable than adding custom rows by hand:

DataTable dt = new DataTable();

using (SqlConnection conn = GetOpenSqlConnection())
using (SqlCommand cmd = new SqlCommand("SQL Query here", conn)
using (IDataReader rdr = cmd.ExecuteReader())
{
    dt.Load(rdr);
}

I can't comment on the difference between this and using .Fill().