SqlDataReader, a firehose cursor - no buffering?

Question

So I understand SqlDataReader is quite fast, you dont know how many rows you'll get and it only allocates memory for each row at a time. So if I have a million rows of small rows (say 4 to 32 bytes) each row read means a roundtrip to the server? No buffering takes place right?

If I used ODBC via SQL Native Client I could setup a row buffer appropriately large to fetch say 512 rows each roundtrip. When the consumer reads row 0 I do a fetch of 512 rows and then return row 0, the next 511 reads are memory accesses and the 513th read is a new roundtrip.

To me this should be faster (and wouldnt have to be forward only), however it depends on how the result set is materialized on the server also. In the latter case the result set would have to be finished before I can start fetching over the network, but may not be the case (?) for SqlDataReader and TDS which I believe it uses, a protocol I'm not familiar with. Any comments appreciated.

Answer 1

a DataReader hands out just 1 row at a time via its api, but it bulk fetches from the database, buffering up data so it doesn't havee to make a round trip to the db for each row.

Answer 2

No, SqlDataReader does buffer on the client side. It is definitely not doing a round trip to the server to fetch your rows.