c# - "cursor like"读取 CLR 过程/函数内部

Question

我必须对存储在 SQL Server 中(出于充分理由)的数据实现算法。该算法不太适合 SQL，因此我想将其实现为 CLR 函数或过程。这是我想要做的:

• 执行多个查询(通常是 20-50，但最多 100-200)，所有查询的形式都是 select a,b,... from some_table order by xyz。有一个适合该查询的索引，因此结果应该或多或少无需任何计算即可获得。

• 逐步使用结果。确切的步进取决于结果，因此不能完全预测。

• 通过遍历结果来聚合一些结果。我只会消耗结果的第一部分，但无法预测我需要多少。停止标准取决于算法内部的某个阈值。

我的想法是打开几个 SqlDataReader，但我有两个问题:

• 每个连接只能有一个 SqlDataReader，并且在 CLR 方法中我只有一个连接 - 据我所知。

• 我不知道如何告诉 SqlDataReader 如何分块读取数据。我找不到 SqlDataReader 应该如何运行的文档。据我了解，它正在准备整个结果集并将整个结果加载到内存中。即使我只会消耗其中的一小部分。

关于如何将其作为 CLR 方法解决的任何提示？或者是否有更适合我的问题的更底层的 SQL Server 接口(interface)？

更新:我应该更明确地说明两点:

1. 我说的是大数据集，因此一个查询可能会产生 1 mio 记录，但我的算法只会消耗前 100-200 个记录。但正如我之前所说:我事先并不知道确切的数字。

2. 我知道 SQL 可能不是那种算法的最佳选择。但由于其他限制，它必须是 SQL 服务器。所以我正在寻找可能的最佳解决方案。

Answer 1

SqlDataReader没有读取整个数据集，您将它与 Dataset 混淆了类(class)。它逐行读取，因为正在调用 .Read() 方法。如果客户端不使用结果集，服务器将暂停查询执行，因为它没有空间将输出写入(选定的行)。随着客户端消耗更多行(正在调用 SqlDataReader.Read)，执行将恢复。甚至还有一个特殊的命令行为标志 SequentialAccess指示 ADO.Net 不要将整行预加载到内存中，这对于以流方式访问大型 BLOB 列很有用(有关实际示例，请参见 Download and Upload images from SQL Server via ASP.Net MVC)。

当 MARS 时，您可以在单个连接上激活多个事件结果集 (SqlDataReader)活跃。但是，MARS 与 SQLCLR 上下文连接不兼容。

所以你可以创建一个 CLR streaming TVF在 CLR 中做一些你需要做的事情，但前提是你只有一个 SQL 查询源。多个查询将要求您放弃上下文连接并使用 isstead 一个完全成熟的连接，即。在环回中连接回同一实例，这将允许 MARS 并因此消耗多个结果集。但是环回有其自身的问题，因为它打破了上下文连接的事务边界。特别是对于环回连接，您的 TVF 将无法读取调用 TVF 的同一事务所做的更改，因为它是 不同 连接上的不同事务。