确实很奇怪的DataReader性能问题 [英] Really odd DataReader performance issue

问题描述

我有一个SQL Server数据库，并且正在使用ADO.NET ExecuteReader来获取数据读取器。我的存储过程返回了大约35,000条记录。

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.

对ExecuteReader的调用大约需要3秒钟才能返回数据读取器。

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);
        }
    }
 } 

大多数读物是耗时0毫秒。但是，我间歇性地获得大约5.5秒（5000毫秒以上）的读取时间。我查看了数据，发现异常情况。我认为开始查看花费了这么长时间的记录的频率。

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

记录号：29，26,26,27,27,29,30,28 ，27,27,30,30,26,27

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

因此，看起来26到30条记录将在0到几毫秒内读取，然后花费5秒，然后接下来的26到30条记录将再次按预期读取。

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.

编辑
我的所有字段都不是varchar（max），甚至都不是。我最大的字段是数字（28,12）。

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

修改存储过程后，我不再遇到问题。我首先将其修改为Select TOP 100，然后将其提高到Top 1000，然后是10,000，然后是100,000。我从来没有这些问题。然后我移到了TOP，现在我没有以前遇到的问题了。

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.

推荐答案

SqlDataReader 缓冲发送给客户端的结果。有关详细信息，请参见此页面在MSDN上：

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

当将结果发送回客户端时，SQL Server将尽可能多的结果集行放入每个数据包中，从而最大程度地减少了发送给客户端的数据包数量。

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.

我怀疑您每个数据包有26-30条记录。遍历记录时，加载新记录会产生延迟。

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.

这篇关于确实很奇怪的DataReader性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！