Is datareader quicker than dataset when populating

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?

Answer 1

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.

Answer 2

It's nice to have DataReader when you need for example show progress of loading data. In DataSet you can't do something in a middle of loading data.

On the other hand DataSet is all-in-one object. So DataSet is much slower. DataReader can give you additional boost in places in your code where data operation is very slow. In these places change it from DataSet to DataReader. DataReader also takes less space in memory.

Oh course it takes more time to code good DataReader, but it's worth it. For example when you play with images or music taken from database.

More on this topic in MSDN Magazine