Is datareader quicker than dataset when populating a datatable?

Question

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

Solution 4

I cant speak to filling a datatable per se but using a datareader is the most efficient reading method.

Solution 5

The datareader is faster. And if you are using 2.0+ you probablt don't even have to use a datatable. You can use a generic list of your object.

View more solutions

Comments

[Karthik Rao]

I've always wondered if that depended on what we do with the data. Since DataReader relies on the database server to buffer the information, so on a big result set,if our calculation is complex, like building a network graph, which gets harder with every new node, it would clog up the database.true?

[Yoopergeek]

Thanks! I was looking for how load a datatable from a datareader because I have a stored-proc that returns multiple tables, but I only need to 'fill' a datatable from one of the output tables.

[Palanikumar]

Your statement from the year 2008 helps me now on end of 2015 :) SqlDataAdapter and SqlDataReader takes 6.x mins to load DataTable, but Linq takes only 1.7 Seconds to load List (56460 Rows).

[Learning-Overthinker-Confused]

@PalaniKumar:Can you please tell me that how would you directly load stored procedure result with list using data reader??

[Palanikumar]

@Learning, I used EntityFramework to get storedprocedure as List of object. If you want to convert datareader to list then check here stackoverflow.com/questions/1464883/…