1

I have a table with millions of rows of data, and I would like to know which is the best way to query my data - using .ExecuteReader() or using a Dataset.

Using SqlDataReader like this:

myReader = cmd.ExecuteReader();

And after fill a list with the result

Or using DataSet

using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
    a.Fill(ds);
}

Which are the best method?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dave
  • 7,028
  • 11
  • 35
  • 58
  • 1
    The real question is: Do you have a WHERE in place for your sql command text?. Reading millions of records should never be an option to be considered – Steve Aug 05 '14 at 11:08
  • If you want to fill a `DatasSet`/`DataTable` use `DataAdapter.Fill(ds)`, otherwise use a `SqlDataReader`. – Tim Schmelter Aug 05 '14 at 11:08
  • possible duplicate of http://stackoverflow.com/questions/12403810/where-to-use-execute-scalar-execute-reader-and-data-set – Rohit Aug 05 '14 at 11:10
  • 1
    Just reading all millions of rows is never a good choice, irrespective of reader/dataset method you choose. – Rahul Aug 05 '14 at 11:24
  • also, in addition to the "don't read millions of rows without a very good reason", I would say that DataTable / DataSet are very rarely the correct choice for a data model. Have you considered, say, *classes*? – Marc Gravell Aug 05 '14 at 11:41
  • just to do a loop, imagine thta I have million data in the table, I don't need to insert update... only select – Dave Aug 05 '14 at 12:03
  • Marc, for that reason I was asking. I usuaually prefer use classes, but in the company i am working now they use dataset – Dave Aug 05 '14 at 12:03
  • who are the most speed to fill the data? – Dave Aug 05 '14 at 12:28

3 Answers3

4

The two objects are to be used in contexts fundamentally different.

A DataReader instance returned by the ExecuteReader doesn't return anything until you loop over it using the Read() method. It is a connected object that has a pointer to a current record on the backend database. You read the content of the record using the various GetXXXXX methods provided by the reader or simply using the indexer. When you have done with the current record you orderly pass to the following one using the Read() method. No way to go back or jump to record N + 100.

A DataSet instead is a disconnected object. It uses internally a DataReader to fill its local memory buffer with all the records returned by the command text query. It is handy if you need to work randomly on the data returned or show them on video or print them. But of course, waiting to have millions of records returned by the internal reader could be time consuming and the consuming of the local memory probably will kill your process before the end.

So, which is the best? Neither, if you have millions of records in your table, you need to put in place an appropriate WHERE condition to reduce the amount of records returned. Said that, it depends on what you need to do with the returned data. To display them on a grid probably you could use a DataSet. Instead a DataReader is better if you need to execute operations on the records one by one.

Steve
  • 213,761
  • 22
  • 232
  • 286
  • Real nice said Stevee +1. To me, either way it will be performance hit if you simply have to read millions of rows without any filtering. – Rahul Aug 05 '14 at 11:23
1

The question is what you want to fill

  • If you want to fill a DataSet/DataTable use DataAdapter.Fill(ds)
  • If you want to fill a list/array use a DataReader and a loop

The DataAdapter also uses a DataReader behind the scenes, but it loops all records. You could add a different logic to loop only part of the resultset.

"I have a table with million of rows": You should almost never need to return so many records. So don't filter in memory but in the database.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
-1

Both Are good Methods. But if you use SqlDataReader than you have to close it. its is must. Otherwise you will not able to execute any other query until SqlDataReader is not closed.

yash
  • 812
  • 3
  • 12
  • 37
  • _you have to close it. its is must_ It is not true if you use the [MultipleActiveResultSets](http://stackoverflow.com/questions/510899/multipleactiveresultsets-true-or-multiple-connections) key for your connection string. – Steve Aug 06 '14 at 07:11