.net - 将 SQL Server 中的数字列表作为单字节数组流式传输

Question

我正在寻找从 SQL Server 获取多头列表的最快方法。

据我所知，下面的代码是您通常可以运行的最快的代码，减慢它速度的是 dr.Read() 中发生的所有事情，以及在某种程度上对每一行调用 dr.GetInt64。

var ids = new long[count];
using (var dr = new SqlCommand(string.Format(@"SELECT TOP 10000 ID FROM Data", count),
    conn).ExecuteReader(CommandBehavior.SequentialAccess))    
{
    while (dr.Read())
    {
        ids[i++] = dr.GetInt64(0);                        
    }
}

查询花费的时间可以忽略不计，因此时间花在了数据读取器中的解析和类型验证上。对于 100,000 条记录，大约需要 25 毫秒，与迭代数组中的 100,000 项所需的 0.20 毫秒相比，这是非常慢的。

因为我只要求一个 long 列表，所以我想知道是否有可能将它们作为单字节数组获取。我所追求的是这样的:

var bytes = (byte[]) new SqlCommand("(I don't know)", conn).ExecuteScalar();                
Buffer.BlockCopy(bytes, 0, ids, 0, 10000);

这将大大减少解析时间。

谁能告诉我这种方法是否可行？

更新:

至少这些方法并不更快:

CLR 聚合

可以定义用 .NET 编写的自定义聚合函数。我试图做一个非常简单的什么也不做的(使用 SqlUserDefinedAggregate(Format.Native) 让它尽可能快)。这将查询时间增加到 60 毫秒，因此它永远不会更快。

查询是这样的:

SELECT dbo.ByteIt(ID) FROM (SELECT TOP 100000 ID FROM Data) T

varbinary(max) 的串联

可以使用纯 SQL 构建字节数组。那很慢。

DECLARE @n varbinary(max)
SET @n = 0;
SELECT TOP 10000 @n = @n + cast(id as varbinary(8)) FROM Data;
SELECT @n; 

为什么它可能永远不值得付出努力

我能想到的最快的原生聚合是 COUNT。

SELECT COUNT(ID) FROM (SELECT TOP 100000 ID FROM Data) T

这需要 10 毫秒，并且必须是考虑每个值的任何方法的绝对下限。我认为这种性能提升不值得付出努力。

遗憾的是，我认为我的问题的答案是“它可以完成，但不会更快。忍受 25 毫秒”。

Answer 1

您现在选择值的方式确实是最优的； Tabular Data Stream protocol针对将结果从 SQL Server 传回客户端进行了优化。最好的方法是使用您现在拥有的客户端工具(`SqlConnection' 等)。

这里有两种替代方法，但我不推荐它们，我会解释原因。

一如既往，YMMV 和你应该测试、测试、测试。

您可以调用 GetBytes method在 SqlDataReader class 上按顺序读取字节并将它们转换为 Int64 instances (可能通过 BitConverter.ToInt64 method )。

请注意，为了做到这一点，您必须通过 CommandBehavior.SequentialAccess到 call to Execute在 SqlCommand class 上以便从服务器流式传输大二进制值。

也就是说，您现在必须构建大型二进制数组以在字段中传回。这就是这种方法被挂断的地方，IMO。基本上，您必须执行一个数据透视表(不是文字数据透视表，而是类似的东西)获取一组 bigint 值并从中创建一个大的二进制值。这本质上不是一个集合操作，您可能必须在存储过程中使用一些循环代码来创建此值。

考虑到这一点，您从将大的二进制值流式传输到客户端所获得的任何 yield 似乎都会被您将要旋转的 CPU 周期所抵消(并且可能变得更糟)从 bigint 的列表中构造那个大的二进制值。

您可能可以通过使用 CLR stored procedure 来缓解其中的一些问题这将获取 bigint 的列表，然后为您创建字节数组，但在这一点上，它可能有点矫枉过正。

CLR 存储过程在执行过程操作时会更快(也就是说，它不是基于集合的，而 T-SQL 更适合)。读取的次数最初是相同的(毕竟，您必须获取数据来创建二进制字符串)但是您可能会耗尽内存(取决于您的集合的大小，因为您必须将它连接成一个值)以及 CPU(由于串联)，甚至在您将第一个字节发送回客户端之前，所有这些都需要时间。