真的很奇怪的DataReader性能问题

Question

我有一个SQL Server数据库并且使用ADO.NET ExecuteReader获取数据读取器。我的存储过程返回大约35000条记录。

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

我使用类似于下面的代码来获取我的项目。

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+毫秒）。我查看了数据但找不到任何异常之处。因此开始查看那些耗时很长的记录出现频率。

结果很有趣。虽然不完全一致，但它们非常接近。那些读取时间较长的记录如下...

记录编号: 29、26、26、27、27、29、30、28、27、27、30、30、26、27

因此看起来26至30条记录会在0至几毫秒内读取，然后会花费5秒钟，然后下一批26至30条记录会再次按预期读取。

我完全不知所措。我可以发帖更多的代码，但这并不是很复杂的代码。

编辑 我的字段中没有varchar(max)，甚至离varchar(max)也相差很远。我的最大字段是numeric(28,12)。

修改了存储过程后，我不再遇到问题。首先我将其修改为Select TOP 100，然后将其提高到Top 1000、10000和100000，我从未遇到过这个问题。然后我删除了TOP，现在我没有早期遇到的问题了。

- Jeff Reddy

---

我发现如果我修改存储过程只返回100个项目，它就可以正常运行。我将继续提高这个数量，看看是否存在一个数字，性能会显著降低。 - Jeff Reddy

我曾经遇到过完全相同的问题，使用的是MsSql 2008。在存储过程中添加Select top(1000)后，一切都可以无延迟地正常工作了。在此之前，每读取200次就会有一个缓慢的Read()。 - Bernhard

Answer 1

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

当结果发送回客户端时，SQL Server会将尽可能多的结果集行放入每个数据包中，从而最小化发送到客户端的数据包数量。

我怀疑您每个数据包收到26-30条记录。当您遍历记录时，新记录加载时会出现延迟。

- Reed Copsey

---

这听起来是可行的。但是为什么需要5秒钟才能检索30多条记录？ - user180326

@jdv-JandeVaan 没有头绪 - 除非是高延迟的网络连接，服务器压力过大或类似问题... - Reed Copsey

在这个服务器上不应该有任何延迟，除了单个 SQL Server 实例和单个数据库之外，没有任何东西在运行。 数据库的负载很轻。 在此问题期间，Web 服务器和 SQL Server 服务器的 CPU 和内存都是正常的。 - Jeff Reddy

我接受了你的答案，仅仅是因为我从中学到了一些东西，尽管我的问题没有得到解决。我确实让我的问题停止发生了，并且自那以后再也没有出现过。在它再次出现之前，我会将其搁置。 - Jeff Reddy

Answer 2

我曾经遇到过类似的问题。解决方法是将所有文本字段都转换为nvarchar(max)，然后使用.NET ExecuteReader返回结果的时间与在MS Studio中执行存储过程的时间相似。请注意，存储过程中没有包含事务，但.NET调用被包装在一个事务中。

- Nick