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.

I found that if I modified my stored procedure to only return 100 items, it ran fine. I'm going to continue to raise this amount to see if there is a number where performance degrades significantly. — Jeff Reddy
– Jeff Reddy, Commented Nov 16, 2011 at 20:22
I had exactly the same problem, with MsSql 2008. After adding Select top(1000) to the stored procedure, everything works without latency. Before I always had one slow Read() after every 200 reads. — Bernhard
– Bernhard, Commented Oct 25, 2018 at 12:48

Reed Copsey · Accepted Answer · 2011-11-16 20:23:00Z

3

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.

answered Nov 16, 2011 at 20:23

Reed Copsey

567k80 gold badges1.2k silver badges1.4k bronze badges

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

4 Comments

user180326 Over a year ago

This sounds feasible. But why should it take 5 seconds to retrieve 30ish records?

Reed Copsey Over a year ago

@jdv-JandeVaan No idea - unless it's a highly latent network connection, the server's under stress, or something like that...

Jeff Reddy Over a year ago

There shouldn't be any kind of latency and there isn't anything running on this server but a single instance of SQL Server with a single database. Traffic is very light on the database. Both web server and SQL-Server server have normal CPU and Memory during this issue.

Jeff Reddy Over a year ago

I accepted your answer, simply because I learned a bit from it, although my problem wasn't solved. I did get my problem to stop happening and it hasn't occurred again since. Until it rears it's ugly head again, I'm putting this to rest.

Nick · Accepted Answer · 2012-03-26 20:54:07Z

1

I had a similar problem. The answer was to cast all the text fields using nvarchar(max) and then .NET ExecuteReader returned within a similar period to an Exec of the sproc in MS Studio. Note that the sproc didn't contain transactions but the .NET call was wrapped in a transaction.

answered Mar 26, 2012 at 20:54

Nick

111 bronze badge

Collectives™ on Stack Overflow

Really odd DataReader performance issue

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related