SqlDataReader / DbDataReader implementation question

Question

Does anyone know how DbDataReaders actually work. We can use SqlDataReader as an example.

When you do the following

cmd.CommandText = "SELECT * FROM Customers";

var rdr = cmd.ExecuteReader();

while(rdr.Read())
{
  //Do something
}

Does the data reader have all of the rows in memory, or does it just grab one, and then when Read is called, does it go to the db and grab the next one? It seems just bringing one into memory would be bad performance, but bringing all of them would make it take a while on the call to ExecuteReader.

I know I'm the consumer of the object and it doesn't really matter how they implement it, but I'm just curious, and I think that I would probably spend a couple hours in Reflector to get an idea of what it's doing, so thought I'd ask someone that might know.

I'm just curious if anyone has an idea.

Answer 1

As stated here :

Using the DataReader can increase application performance both by retrieving data as soon as it is available, and (by default) storing only one row at a time in memory, reducing system overhead.

And as far as I know that's the way every reader works in the .NET framework.

Answer 2

Rhapsody is correct.

Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader

I ran a test using DataReaders vs DataAdaptors on an equal 10,000 record data set, and I found that the DataAdaptor was consistently 3-4 milliseconds faster than the DataReader, but the DataAdaptor will end up holding onto more memory.

When I ran the same test on equal 50,000 record data sets I saw a performance gain on the DataReader side to the tune of 50 milliseconds.

With that said, if you had a long running query or a huge result set, I think you may be better off with a DataReader since you get your results sooner and don't have to hold onto all of that data in memory. It is also important to keep in mind that a DataReader is forward only, so if you need to move around in your results set, then it is not the best choice.