Really odd DataReader performance issue

Question

I have a SQL Server database and I'm using ADO.NET ExecuteReader to get a datareader. My stored procedure returns around 35,000 records.

The call to ExecuteReader is taking roughly 3 seconds to return the datareader.

I'm using code very similar to this to get my items.

using(var conn = new SqlConnection(MySQLHelper.ConnectionString)) {
    conn.Open();
    var sqlCommand = SqlHelper.CreateCommand(conn, "spGetItems");
    using (var dr = sqlCommand.ExecuteReader()) {
        while(dr.read){
            var item = new Item{ID = dr.GetInt32(0), ItemName = dr.GetString(1)};
            items.Add(item);
        }
    }
 } 

A majority of the reads is taking 0 milliseconds. However, intermitantly I'm getting a Read that takes about 5.5 seconds (5000+ milliseconds). I've looked at the data and could find nothing out of the ordinary. I think started looking at the frequency of the records that were taking so long.

This was interesting. While not completely consistent, they were close. The records that were taking a long time to load were as follows...

Record #s: 29, 26,26,27,27,29,30,28,27,27,30,30,26,27

So it looks like 26 to 30 records would read in 0 to a few milliseconds, and then it would take 5 seconds, then the next 26 to 30 records would again read as expected.

I'm at a complete loss here. I can post more code, but there isn't much to it. It's pretty simple code.

EDIT None of my fields are varchar(max), or even close. My largest field is a numeric(28,12).

After modifying my stored procedure , I'm no longer having issues. I first modified it to Select TOP 100, then raised that to Top 1000, then 10,000 and then 100,000. I never had the issue with those. Then I removed to TOP and now I'm not having the issue I was earlier.

Answer 1

SqlDataReader buffers results sent to the client. See this page on MSDN for details:

When the results are sent back to the client, SQL Server puts as many result set rows as it can into each packet, minimizing the number of packets sent to the client.

I suspect that you're getting 26-30 records per packet. As you iterate through the records, you get a delay as new records are loaded.