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?

Improve this question
8
  • 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 Commented Aug 5, 2014 at 11:08
  • If you want to fill a DatasSet/DataTable use DataAdapter.Fill(ds), otherwise use a SqlDataReader. Commented Aug 5, 2014 at 11:08
  • possible duplicate of stackoverflow.com/questions/12403810/… Commented Aug 5, 2014 at 11:10
  • 1
    Just reading all millions of rows is never a good choice, irrespective of reader/dataset method you choose. Commented Aug 5, 2014 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? Commented Aug 5, 2014 at 11:41

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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

Improve this answer

Comments

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

Improve this answer

1 Comment

you have to close it. its is must It is not true if you use the MultipleActiveResultSets key for your connection string.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.