使用SQL CLR标量函数模拟HASHBYTES的可伸缩方法是什么？

作为ETL流程的一部分，我们将比较暂存数据中的行与报表数据库之间的差异，以找出自从上次加载数据以来是否实际更改了任何列。

比较是基于唯一键的表和其他所有列的某种哈希值。我们当前将HASHBYTES与SHA2_256算法一起使用，并且发现如果许多并发工作线程都在调用HASHBYTES，它就无法在大型服务器上扩展。

以每秒哈希数衡量的吞吐量不会增加超过16个并发线程在96核心服务器上进行测试时。我通过将并发MAXDOP 8查询的数量从1更改为12进行测试。使用MAXDOP 1进行测试显示了相同的可伸缩性瓶颈。

作为一种变通办法，我想尝试SQL CLR解决方案。这是我尝试陈述的要求：

该函数必须能够参与并行查询
该函数必须是确定性的
该函数必须接受输入一串NVARCHAR或VARBINARY字符串（所有相关列都串联在一起）
该字符串的典型输入大小为100-20000个字符。 20000不是最大值
哈希冲突的可能性应该大致等于或优于MD5算法。 CHECKSUM对我们不起作用，因为有太多冲突。
该功能必须在大型服务器上很好地扩展（随着线程数量的增加，每个线程的吞吐量不应显着降低）

对于应用程序Reasons™，假设我无法保存报表的哈希值。这是一个不支持触发器或计算列的CCI（还有其他我也不想讨论的问题）。

使用SQL CLR函数模拟HASHBYTES的可扩展方式是什么？我的目标可以表示为在大型服务器上每秒获得尽可能多的哈希，因此性能也很重要。我对CLR感到很糟糕，所以我不知道该如何完成。如果它激励任何人回答，我计划在可能的情况下尽快为这个问题添加赏金。下面是一个示例查询，它非常粗略地说明了用例：

DROP TABLE IF EXISTS #CHANGED_IDS;

SELECT stg.ID INTO #CHANGED_IDS
FROM (
    SELECT ID,
    CAST( HASHBYTES ('SHA2_256', 
        CAST(FK1 AS NVARCHAR(19)) + 
        CAST(FK2 AS NVARCHAR(19)) + 
        CAST(FK3 AS NVARCHAR(19)) + 
        CAST(FK4 AS NVARCHAR(19)) + 
        CAST(FK5 AS NVARCHAR(19)) + 
        CAST(FK6 AS NVARCHAR(19)) + 
        CAST(FK7 AS NVARCHAR(19)) + 
        CAST(FK8 AS NVARCHAR(19)) + 
        CAST(FK9 AS NVARCHAR(19)) + 
        CAST(FK10 AS NVARCHAR(19)) + 
        CAST(FK11 AS NVARCHAR(19)) + 
        CAST(FK12 AS NVARCHAR(19)) + 
        CAST(FK13 AS NVARCHAR(19)) + 
        CAST(FK14 AS NVARCHAR(19)) + 
        CAST(FK15 AS NVARCHAR(19)) + 
        CAST(STR1 AS NVARCHAR(500)) +
        CAST(STR2 AS NVARCHAR(500)) +
        CAST(STR3 AS NVARCHAR(500)) +
        CAST(STR4 AS NVARCHAR(500)) +
        CAST(STR5 AS NVARCHAR(500)) +
        CAST(COMP1 AS NVARCHAR(1)) + 
        CAST(COMP2 AS NVARCHAR(1)) + 
        CAST(COMP3 AS NVARCHAR(1)) + 
        CAST(COMP4 AS NVARCHAR(1)) + 
        CAST(COMP5 AS NVARCHAR(1)))
     AS BINARY(32)) HASH1
    FROM HB_TBL WITH (TABLOCK)
) stg
INNER JOIN (
    SELECT ID,
    CAST(HASHBYTES ('SHA2_256', 
        CAST(FK1 AS NVARCHAR(19)) + 
        CAST(FK2 AS NVARCHAR(19)) + 
        CAST(FK3 AS NVARCHAR(19)) + 
        CAST(FK4 AS NVARCHAR(19)) + 
        CAST(FK5 AS NVARCHAR(19)) + 
        CAST(FK6 AS NVARCHAR(19)) + 
        CAST(FK7 AS NVARCHAR(19)) + 
        CAST(FK8 AS NVARCHAR(19)) + 
        CAST(FK9 AS NVARCHAR(19)) + 
        CAST(FK10 AS NVARCHAR(19)) + 
        CAST(FK11 AS NVARCHAR(19)) + 
        CAST(FK12 AS NVARCHAR(19)) + 
        CAST(FK13 AS NVARCHAR(19)) + 
        CAST(FK14 AS NVARCHAR(19)) + 
        CAST(FK15 AS NVARCHAR(19)) + 
        CAST(STR1 AS NVARCHAR(500)) +
        CAST(STR2 AS NVARCHAR(500)) +
        CAST(STR3 AS NVARCHAR(500)) +
        CAST(STR4 AS NVARCHAR(500)) +
        CAST(STR5 AS NVARCHAR(500)) +
        CAST(COMP1 AS NVARCHAR(1)) + 
        CAST(COMP2 AS NVARCHAR(1)) + 
        CAST(COMP3 AS NVARCHAR(1)) + 
        CAST(COMP4 AS NVARCHAR(1)) + 
        CAST(COMP5 AS NVARCHAR(1)) )
 AS BINARY(32)) HASH1
    FROM HB_TBL_2 WITH (TABLOCK)
) rpt ON rpt.ID = stg.ID
WHERE rpt.HASH1 <> stg.HASH1
OPTION (MAXDOP 8);

为简化起见，我可能会使用类似于以下内容的基准测试。我将在星期一以HASHBYTES发布结果：

CREATE TABLE dbo.HASH_ME (
    ID BIGINT NOT NULL,
    FK1 BIGINT NOT NULL,
    FK2 BIGINT NOT NULL,
    FK3 BIGINT NOT NULL,
    FK4 BIGINT NOT NULL,
    FK5 BIGINT NOT NULL,
    FK6 BIGINT NOT NULL,
    FK7 BIGINT NOT NULL,
    FK8 BIGINT NOT NULL,
    FK9 BIGINT NOT NULL,
    FK10 BIGINT NOT NULL,
    FK11 BIGINT NOT NULL,
    FK12 BIGINT NOT NULL,
    FK13 BIGINT NOT NULL,
    FK14 BIGINT NOT NULL,
    FK15 BIGINT NOT NULL,
    STR1 NVARCHAR(500) NOT NULL,
    STR2 NVARCHAR(500) NOT NULL,
    STR3 NVARCHAR(500) NOT NULL,
    STR4 NVARCHAR(500) NOT NULL,
    STR5 NVARCHAR(2000) NOT NULL,
    COMP1 TINYINT NOT NULL,
    COMP2 TINYINT NOT NULL,
    COMP3 TINYINT NOT NULL,
    COMP4 TINYINT NOT NULL,
    COMP5 TINYINT NOT NULL
);

INSERT INTO dbo.HASH_ME WITH (TABLOCK)
SELECT RN,
RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 1000),
0,1,0,1,0
FROM (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

SELECT MAX(HASHBYTES('SHA2_256',
CAST(N'' AS NVARCHAR(MAX)) + N'|' +
CAST(FK1 AS NVARCHAR(19)) + N'|' +
CAST(FK2 AS NVARCHAR(19)) + N'|' +
CAST(FK3 AS NVARCHAR(19)) + N'|' +
CAST(FK4 AS NVARCHAR(19)) + N'|' +
CAST(FK5 AS NVARCHAR(19)) + N'|' +
CAST(FK6 AS NVARCHAR(19)) + N'|' +
CAST(FK7 AS NVARCHAR(19)) + N'|' +
CAST(FK8 AS NVARCHAR(19)) + N'|' +
CAST(FK9 AS NVARCHAR(19)) + N'|' +
CAST(FK10 AS NVARCHAR(19)) + N'|' +
CAST(FK11 AS NVARCHAR(19)) + N'|' +
CAST(FK12 AS NVARCHAR(19)) + N'|' +
CAST(FK13 AS NVARCHAR(19)) + N'|' +
CAST(FK14 AS NVARCHAR(19)) + N'|' +
CAST(FK15 AS NVARCHAR(19)) + N'|' +
CAST(STR1 AS NVARCHAR(500)) + N'|' +
CAST(STR2 AS NVARCHAR(500)) + N'|' +
CAST(STR3 AS NVARCHAR(500)) + N'|' +
CAST(STR4 AS NVARCHAR(500)) + N'|' +
CAST(STR5 AS NVARCHAR(2000)) + N'|' +
CAST(COMP1 AS NVARCHAR(1)) + N'|' +
CAST(COMP2 AS NVARCHAR(1)) + N'|' +
CAST(COMP3 AS NVARCHAR(1)) + N'|' +
CAST(COMP4 AS NVARCHAR(1)) + N'|' +
CAST(COMP5 AS NVARCHAR(1)) )
)
FROM dbo.HASH_ME
OPTION (MAXDOP 1);

#1 楼

由于您只是在寻找更改，因此不需要加密哈希函数。
您可以从Brandon Dahler许可的开源Data.HashFunction库中选择一种较快的非加密哈希，并获得OSI批准的MIT许可证。 SpookyHash是一个受欢迎的选择。
示例实现
源代码

 using Microsoft.SqlServer.Server;
using System.Data.HashFunction.SpookyHash;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
            DataAccess = DataAccessKind.None,
            SystemDataAccess = SystemDataAccessKind.None,
            IsDeterministic = true,
            IsPrecise = true
        )
    ]
    public static byte[] SpookyHash
        (
            [SqlFacet (MaxSize = 8000)]
            SqlBinary Input
        )
    {
        ISpookyHashV2 sh = SpookyHashV2Factory.Instance.Create();
        return sh.ComputeHash(Input.Value).Hash;
    }

    [SqlFunction
        (
            DataAccess = DataAccessKind.None,
            IsDeterministic = true,
            IsPrecise = true,
            SystemDataAccess = SystemDataAccessKind.None
        )
    ]
    public static byte[] SpookyHashLOB
        (
            [SqlFacet (MaxSize = -1)]
            SqlBinary Input
        )
    {
        ISpookyHashV2 sh = SpookyHashV2Factory.Instance.Create();
        return sh.ComputeHash(Input.Value).Hash;
    }
}

源提供两个功能，一个，用于输入8000字节或更少的字节，以及LOB版本。非LOB版本应该要快得多。
您可能可以将LOB二进制文件包装在COMPRESS中，以使其在8000字节的限制内，如果这样做对于性能而言是值得的。另外，您可以将LOB细分为8000个字节以下的段，或者仅保留HASHBYTES用于LOB情况（因为较长的输入可以更好地缩放）。
预构建的代码
您显然可以抓住该软件包供您自己使用并编译所有内容，但我构建了以下程序集以使快速测试变得更加容易：
https://gist.github.com/SQLKiwi/365b265b476bf86754457fc9514b2300
T-SQL函数

CREATE FUNCTION dbo.SpookyHash
(
    @Input varbinary(8000)
)
RETURNS binary(16)
WITH 
    RETURNS NULL ON NULL INPUT, 
    EXECUTE AS OWNER
AS EXTERNAL NAME Spooky.UserDefinedFunctions.SpookyHash;
GO
CREATE FUNCTION dbo.SpookyHashLOB
(
    @Input varbinary(max)
)
RETURNS binary(16)
WITH 
    RETURNS NULL ON NULL INPUT, 
    EXECUTE AS OWNER
AS EXTERNAL NAME Spooky.UserDefinedFunctions.SpookyHashLOB;
GO

用法
给出问题中的示例数据的示例用法：

SELECT
    HT1.ID
FROM dbo.HB_TBL AS HT1
JOIN dbo.HB_TBL_2 AS HT2
    ON HT2.ID = HT1.ID
    AND dbo.SpookyHash
    (
        CONVERT(binary(8), HT2.FK1) + 0x7C +
        CONVERT(binary(8), HT2.FK2) + 0x7C +
        CONVERT(binary(8), HT2.FK3) + 0x7C +
        CONVERT(binary(8), HT2.FK4) + 0x7C +
        CONVERT(binary(8), HT2.FK5) + 0x7C +
        CONVERT(binary(8), HT2.FK6) + 0x7C +
        CONVERT(binary(8), HT2.FK7) + 0x7C +
        CONVERT(binary(8), HT2.FK8) + 0x7C +
        CONVERT(binary(8), HT2.FK9) + 0x7C +
        CONVERT(binary(8), HT2.FK10) + 0x7C +
        CONVERT(binary(8), HT2.FK11) + 0x7C +
        CONVERT(binary(8), HT2.FK12) + 0x7C +
        CONVERT(binary(8), HT2.FK13) + 0x7C +
        CONVERT(binary(8), HT2.FK14) + 0x7C +
        CONVERT(binary(8), HT2.FK15) + 0x7C +
        CONVERT(varbinary(1000), HT2.STR1) + 0x7C +
        CONVERT(varbinary(1000), HT2.STR2) + 0x7C +
        CONVERT(varbinary(1000), HT2.STR3) + 0x7C +
        CONVERT(varbinary(1000), HT2.STR4) + 0x7C +
        CONVERT(varbinary(1000), HT2.STR5) + 0x7C +
        CONVERT(binary(1), HT2.COMP1) + 0x7C +
        CONVERT(binary(1), HT2.COMP2) + 0x7C +
        CONVERT(binary(1), HT2.COMP3) + 0x7C +
        CONVERT(binary(1), HT2.COMP4) + 0x7C +
        CONVERT(binary(1), HT2.COMP5)
    )
    <> dbo.SpookyHash
    (
        CONVERT(binary(8), HT1.FK1) + 0x7C +
        CONVERT(binary(8), HT1.FK2) + 0x7C +
        CONVERT(binary(8), HT1.FK3) + 0x7C +
        CONVERT(binary(8), HT1.FK4) + 0x7C +
        CONVERT(binary(8), HT1.FK5) + 0x7C +
        CONVERT(binary(8), HT1.FK6) + 0x7C +
        CONVERT(binary(8), HT1.FK7) + 0x7C +
        CONVERT(binary(8), HT1.FK8) + 0x7C +
        CONVERT(binary(8), HT1.FK9) + 0x7C +
        CONVERT(binary(8), HT1.FK10) + 0x7C +
        CONVERT(binary(8), HT1.FK11) + 0x7C +
        CONVERT(binary(8), HT1.FK12) + 0x7C +
        CONVERT(binary(8), HT1.FK13) + 0x7C +
        CONVERT(binary(8), HT1.FK14) + 0x7C +
        CONVERT(binary(8), HT1.FK15) + 0x7C +
        CONVERT(varbinary(1000), HT1.STR1) + 0x7C +
        CONVERT(varbinary(1000), HT1.STR2) + 0x7C +
        CONVERT(varbinary(1000), HT1.STR3) + 0x7C +
        CONVERT(varbinary(1000), HT1.STR4) + 0x7C +
        CONVERT(varbinary(1000), HT1.STR5) + 0x7C +
        CONVERT(binary(1), HT1.COMP1) + 0x7C +
        CONVERT(binary(1), HT1.COMP2) + 0x7C +
        CONVERT(binary(1), HT1.COMP3) + 0x7C +
        CONVERT(binary(1), HT1.COMP4) + 0x7C +
        CONVERT(binary(1), HT1.COMP5)
    );

使用LOB版本时，第一个参数应强制转换或转换为varbinary(max)。
执行计划

安全怪异
Data.HashFunction库使用SQL Server认为UNSAFE的许多CLR语言功能。可以编写与SAFE状态兼容的基本Spooky Hash。我基于乔恩·汉纳（Jon Hanna）的SpookilySharp编写的示例如下：
https://gist.github.com/SQLKiwi/7a5bb26b0bee56f6d28a1d26669ce8f2

#2 楼

我不确定使用SQLCLR并行性是否会更好/更好。但是，测试真的很容易，因为SQL＃SQLCLR库的免费版本（我写过）中有一个称为Util_HashBinary的哈希函数。支持的算法为：MD5，SHA1，SHA256，SHA384和SHA512。

它使用VARBINARY(MAX)值作为输入，因此您可以连接每个字段的字符串版本（如您当前所做的那样），并且然后转换为VARBINARY(MAX)，或者您可以直接转到每一列的VARBINARY并连接转换后的值（这可能会更快，因为您不需要处理字符串或从string到VARBINARY的额外转换）。下面是显示这两个选项的示例。它还显示了HASHBYTES函数，因此您可以看到它与SQL＃.Util_HashBinary的值相同。

请注意，串联VARBINARY值时的哈希结果与串联NVARCHAR值时的哈希结果不匹配。这是因为INT值“ 1”的二进制形式是0x00000001，而NVARCHAR值“ 1”的UTF-16LE（即INT）形式（二进制形式，因为哈希函数将对其进行操作）是0x3100。

SELECT so.[object_id],
       SQL#.Util_HashBinary(N'SHA256',
                            CONVERT(VARBINARY(MAX),
                                    CONCAT(so.[name], so.[schema_id], so.[create_date])
                                   )
                           ) AS [SQLCLR-ConcatStrings],
       HASHBYTES(N'SHA2_256',
                 CONVERT(VARBINARY(MAX),
                         CONCAT(so.[name], so.[schema_id], so.[create_date])
                        )
                ) AS [BuiltIn-ConcatStrings]
FROM sys.objects so;


SELECT so.[object_id],
       SQL#.Util_HashBinary(N'SHA256',
                            CONVERT(VARBINARY(500), so.[name]) + 
                            CONVERT(VARBINARY(500), so.[schema_id]) +
                            CONVERT(VARBINARY(500), so.[create_date])
                           ) AS [SQLCLR-ConcatVarBinaries],
       HASHBYTES(N'SHA2_256',
                 CONVERT(VARBINARY(500), so.[name]) + 
                 CONVERT(VARBINARY(500), so.[schema_id]) +
                 CONVERT(VARBINARY(500), so.[create_date])
                ) AS [BuiltIn-ConcatVarBinaries]
FROM sys.objects so;

您可以使用以下方法测试与非LOB Spooky相当的东西：

CREATE FUNCTION [SQL#].[Util_HashBinary8k]
(@Algorithm [nvarchar](50), @BaseData [varbinary](8000))
RETURNS [varbinary](8000) 
WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [SQL#].[UTILITY].[HashBinary];

注意：Util_HashBinary使用内置于.NET中的托管SHA256算法，并且不应使用“ bcrypt”库。

除了这个问题之外，还有一些其他想法可能会帮助您解决此问题。过程：

其他思想＃1（至少计算一些哈希值）

您提到了几件事：

我们将临时表中的行与报表数据库进行比较，以找出自从上次加载数据以来是否实际更改了任何列。

并且： br />

我无法保存报表的哈希值。这是不支持触发器或计算列的CCI。

和：

表可以在ETL流程之外进行更新
/>

听起来此报告表中的数据在一段时间内是稳定的，并且只能通过此ETL流程进行修改。

如果没有其他修改，则此表，那么我们毕竟根本不需要触发器或索引视图（我原本以为可以这样做）。

由于您无法修改报告表的架构，因此至少可以创建一个相关表来包含预先计算的哈希值（以及计算它的UTC时间）？这样，您便可以将预先计算的值与下一次进行比较，而只留下需要计算其哈希值的传入值。这将使HASHBYTES或SQL#.Util_HashBinary的呼叫数量减少一半。您只需在导入过程中加入该哈希表即可。

您还将创建一个单独的存储过程，该存储过程仅刷新该表的哈希值。它只是更新已更改为当前的任何相关行的哈希，并更新那些修改后的行的时间戳。可以/应该在更新此表的任何其他过程结束时执行此proc。还可以安排它在此ETL开始之前30到60分钟运行（取决于执行所需的时间以及这些其他进程中的任何一个何时运行）。如果您怀疑行可能不同步，甚至可以手动执行。

然后指出：

有500多个表

这么多的表确实使每个表都有一个额外的表来包含当前哈希值变得更加困难，但这并不是不可能的，因为可以将其编写为脚本，因为它是标准架构。该脚本只需要考虑源表名称和源表PK列的发现。

无论哪种哈希算法最终被证明是最具扩展性的，我仍然强烈建议您查找至少有几个表（也许有些表比500个表中的其余表大得多），并建立了一个相关表来捕获当前哈希值，以便在ETL处理之前就可以知道“当前”值。即使最快的功能也永远无法胜任，永远不必首先调用它；-)。

附加思想＃2（VARBINARY代替NVARCHAR）

无论如何SQLCLR与内置HASHBYTES的比较，我仍然建议直接转换为VARBINARY，因为这样做应该更快。串联字符串并不是十分有效。而且，除了首先将非字符串值转换为字符串外，这还需要额外的工作（我假设工作量根据基本类型而有所不同：DATETIME需要的数量大于BIGINT），而转换为VARBINARY只会给您

实际上，测试与其他测试所使用的相同数据集并使用HASHBYTES(N'SHA2_256',...)，在一分钟内计算出的总哈希增加了23.415％。而且，增加的原因仅在于使用VARBINARY而不是NVARCHAR！ 😸（有关详细信息，请参见社区Wiki答案）

其他思想＃3（请注意输入参数）

进一步的测试表明，有一个领域会影响性能（超出此范围）执行次数）是输入参数：多少个和什么类型。

我的SQL＃库中当前存在的Util_HashBinary SQLCLR函数具有两个输入参数：一个VARBINARY（要哈希的值）和一个NVARCHAR（要使用的算法）。这是由于我镜像了HASHBYTES函数的签名。但是，我发现，如果删除了NVARCHAR参数并创建了仅执行SHA256的函数，则性能会得到很好的改善。我假设即使将NVARCHAR参数切换为INT也会有所帮助，但我还假设甚至没有额外的INT参数也至少要快一点。

SqlBytes.Value的性能可能比SqlBinary.Value更好。 br />
我创建了两个新函数：Util_HashSHA256Binary和Util_HashSHA256Binary8k进行此测试。这些将包含在SQL＃的下一版本中（尚未设置日期）。

我还发现测试方法可能会略有改进，因此我在社区Wiki中更新了测试工具。下面的答案包括：

预加载SQLCLR程序集，以确保加载时间开销不会扭曲结果。
验证过程以检查冲突。如果找到任何内容，它将显示唯一/不同的行数和总行数。这样就可以确定冲突次数（如果有）是否超出给定用例的限制。一些用例可能只允许少量冲突，而另一些用例可能不需要。如果无法检测到所需精度水平的变化，则超快速功能将无用。例如，使用OP提供的测试工具，我将行数增加到100k行（最初是10k），发现CHECKSUM记录了超过9k的碰撞，即9％（yike）。

其他思想＃4（一起HASHBYTES + SQLCLR？）

根据瓶颈所在的位置，甚至可以结合使用内置的HASHBYTES和SQLCLR UDF来执行相同的哈希。如果内置函数与SQLCLR操作的约束不同/分开，则与单独使用HASHBYTES或SQLCLR相比，此方法可能能够同时执行更多操作。绝对值得测试。

其他想法5（哈希对象缓存吗？）

大卫·布朗的答案中建议的哈希算法对象的缓存肯定看起来很有趣，所以我进行了尝试，发现了以下两个有趣的点：

无论出于何种原因，它似乎都无法提供很多（如果有的话）性能改进。我本来可以做错什么，但是这是我尝试的方法：

static readonly ConcurrentDictionary<int, SHA256Managed> hashers =
    new ConcurrentDictionary<int, SHA256Managed>();

[return: SqlFacet(MaxSize = 100)]
[SqlFunction(IsDeterministic = true)]
public static SqlBinary FastHash([SqlFacet(MaxSize = 1000)] SqlBytes Input)
{
    SHA256Managed sh = hashers.GetOrAdd(Thread.CurrentThread.ManagedThreadId,
                                        i => new SHA256Managed());

    return sh.ComputeHash(Input.Value);
}

对于特定的所有SQLCLR引用，ManagedThreadId值似乎相同查询。我测试了对同一个函数的多个引用，以及对一个不同函数的引用，所有这3个都被赋予了不同的输入值，并返回了不同的（但期望的）返回值。对于这两个测试函数，输出均为包含ManagedThreadId的字符串以及哈希结果的字符串表示形式。对于查询中的所有UDF引用以及所有行，ManagedThreadId值均相同。但是，对于相同的输入字符串，散列结果是相同的，对于不同的输入字符串，散列结果是不同的。

虽然我在测试中没有看到任何错误的结果，但这不会增加出现错误的可能性。比赛条件？如果在特定查询中调用的所有SQLCLR对象的字典键均相同，则它们将共享为该键存储的相同值或对象，对吗？关键是，甚至认为它似乎可以在这里工作（在某种程度上，似乎又没有太大的性能提升，但是功能上没有什么坏处），这让我没有信心这种方法可以在其他情况下使用。 br />

#3 楼

这不是传统的答案，但我认为发布迄今为止提到的某些技术的基准会有所帮助。我正在使用SQL Server 2017 CU9在96核心服务器上进行测试。

许多可伸缩性问题是由争用某些全局状态的并发线程引起的。例如，考虑经典的PFS页面争用。如果太多的工作线程需要修改内存中的同一页，则会发生这种情况。随着代码变得更有效率，它可能会更快地请求锁存器。这增加了争论。简而言之，高效的代码更有可能导致可伸缩性问题，因为全局状态的竞争更为严峻。较慢的代码不太可能导致可伸缩性问题，因为不会频繁访问全局状态。

HASHBYTES可伸缩性部分取决于输入字符串的长度。我的理论是为什么会发生这种情况，因为在调用HASHBYTES函数时需要访问某些全局状态。易于观察的全局状态是，在某些版本的SQL Server上，每个调用都需要分配一个内存页。较难观察到的是存在某种操作系统争用。结果，如果代码调用HASHBYTES的频率降低，则争用将减少。降低HASHBYTES调用率的一种方法是增加每个调用所需的哈希工作量。散列工作部分基于输入字符串的长度。为了重现我在应用程序中看到的可伸缩性问题，我需要更改演示数据。合理的最坏情况是带有21个BIGINT列的表。该表的定义包含在底部的代码中。为了减少Local Factors™，我使用在较小表上运行的并发MAXDOP 1查询。我的快速基准测试代码位于底部。

请注意，这些函数返回不同的哈希长度。 MD5和SpookyHash都是128位哈希，SHA256是256位哈希。

结果（NVARCHAR与VARBINARY的转换和串联）

为了查看是否要转换和串联VARBINARY比NVARCHAR更有效/更高效，是从NVARCHAR存储过程创建的RUN_HASHBYTES_SHA2_256版本。相同的模板（请参见下面的“基准代码”部分中的“第5步”）。唯一的区别是：

存储过程名称以_NVC结尾

BINARY(8)函数的CAST更改为NVARCHAR(15)

0x7C更改为N'|'

结果：

CAST(FK1 AS NVARCHAR(15)) + N'|' +

代替：

CAST(FK1 AS BINARY(8)) + 0x7C +

仅查看平均值，我们可以计算出切换至VARBINARY的好处：

╔════════════════╦══════════╦══════════════╗
║    Datatype    ║  Test #  ║ Total Hashes ║
╠════════════════╬══════════╬══════════════╣
║ NVARCHAR       ║        1 ║     10200000 ║
║ NVARCHAR       ║        2 ║     10300000 ║
║ NVARCHAR       ║  AVERAGE ║ * 10250000 * ║
║ -------------- ║ -------- ║ ------------ ║
║ VARBINARY      ║        1 ║     12500000 ║
║ VARBINARY      ║        2 ║     12800000 ║
║ VARBINARY      ║  AVERAGE ║ * 12650000 * ║
╚════════════════╩══════════╩══════════════╝

返回结果：

SELECT (12650000 - 10250000) AS [IncreaseAmount],
       ROUND(((126500000 - 10250000) / 10250000) * 100.0, 3) AS [IncreasePercentage]

结果（哈希算法和实现）
< br下表包含1分钟内执行的哈希数。例如，将CHECKSUM与84个并发查询一起使用会导致在时间用完之前执行超过20亿次哈希处理。

IncreaseAmount:    2400000.0
IncreasePercentage:   23.415

如果您希望看到以项为单位的相同数字每线程每秒的工作量：

╔════════════════════╦════════════╦════════════╦════════════╗
║      Function      ║ 12 threads ║ 48 threads ║ 84 threads ║
╠════════════════════╬════════════╬════════════╬════════════╣
║ CHECKSUM           ║  281250000 ║ 1122440000 ║ 2040100000 ║
║ HASHBYTES MD5      ║   75940000 ║  106190000 ║  112750000 ║
║ HASHBYTES SHA2_256 ║   80210000 ║  117080000 ║  124790000 ║
║ CLR Spooky         ║  131250000 ║  505700000 ║  786150000 ║
║ CLR SpookyLOB      ║   17420000 ║   27160000 ║   31380000 ║
║ SQL# MD5           ║   17080000 ║   26450000 ║   29080000 ║
║ SQL# SHA2_256      ║   18370000 ║   28860000 ║   32590000 ║
║ SQL# MD5 8k        ║   24440000 ║   30560000 ║   32550000 ║
║ SQL# SHA2_256 8k   ║   87240000 ║  159310000 ║  155760000 ║
╚════════════════════╩════════════╩════════════╩════════════╝

关于所有方法的一些快速思考：

CHECKSUM ：预期的很好的可伸缩性

HASHBYTES：可伸缩性问题包括每个调用一个内存分配和OS中花费的大量CPU。

Spooky：令人惊讶的良好的可伸缩性

Spooky LOB：自旋锁SOS_SELIST_SIZED_SLOCK旋转失控。我怀疑这是通过CLR函数传递LOB的普遍问题，但我不确定

Util_HashBinary：看起来好像被同一个自旋锁击中了。到目前为止，我还没有对此进行研究，因为我可能对此无能为力：

Util_HashBinary 8k：非常令人惊讶的结果，不确定这里发生了什么

在较小的服务器上测试了最终结果：

╔════════════════════╦════════════════════════════╦════════════════════════════╦════════════════════════════╗
║      Function      ║ 12 threads per core-second ║ 48 threads per core-second ║ 84 threads per core-second ║
╠════════════════════╬════════════════════════════╬════════════════════════════╬════════════════════════════╣
║ CHECKSUM           ║                     390625 ║                     389736 ║                     404782 ║
║ HASHBYTES MD5      ║                     105472 ║                      36872 ║                      22371 ║
║ HASHBYTES SHA2_256 ║                     111403 ║                      40653 ║                      24760 ║
║ CLR Spooky         ║                     182292 ║                     175590 ║                     155982 ║
║ CLR SpookyLOB      ║                      24194 ║                       9431 ║                       6226 ║
║ SQL# MD5           ║                      23722 ║                       9184 ║                       5770 ║
║ SQL# SHA2_256      ║                      25514 ║                      10021 ║                       6466 ║
║ SQL# MD5 8k        ║                      33944 ║                      10611 ║                       6458 ║
║ SQL# SHA2_256 8k   ║                     121167 ║                      55316 ║                      30905 ║
╚════════════════════╩════════════════════════════╩════════════════════════════╩════════════════════════════╝

标记代码

设置1：表和数据

╔═════════════════════════╦════════════════════════╦════════════════════════╗
║     Hash Algorithm      ║ Hashes over 11 threads ║ Hashes over 44 threads ║
╠═════════════════════════╬════════════════════════╬════════════════════════╣
║ HASHBYTES SHA2_256      ║               85220000 ║              167050000 ║
║ SpookyHash              ║              101200000 ║              239530000 ║
║ Util_HashSHA256Binary8k ║               90590000 ║              217170000 ║
║ SpookyHashLOB           ║               23490000 ║               38370000 ║
║ Util_HashSHA256Binary   ║               23430000 ║               36590000 ║
╚═════════════════════════╩════════════════════════╩════════════════════════╝

设置2：主执行程序

DROP TABLE IF EXISTS dbo.HASH_SMALL;

CREATE TABLE dbo.HASH_SMALL (
    ID BIGINT NOT NULL,
    FK1 BIGINT NOT NULL,
    FK2 BIGINT NOT NULL,
    FK3 BIGINT NOT NULL,
    FK4 BIGINT NOT NULL,
    FK5 BIGINT NOT NULL,
    FK6 BIGINT NOT NULL,
    FK7 BIGINT NOT NULL,
    FK8 BIGINT NOT NULL,
    FK9 BIGINT NOT NULL,
    FK10 BIGINT NOT NULL,
    FK11 BIGINT NOT NULL,
    FK12 BIGINT NOT NULL,
    FK13 BIGINT NOT NULL,
    FK14 BIGINT NOT NULL,
    FK15 BIGINT NOT NULL,
    FK16 BIGINT NOT NULL,
    FK17 BIGINT NOT NULL,
    FK18 BIGINT NOT NULL,
    FK19 BIGINT NOT NULL,
    FK20 BIGINT NOT NULL
);

INSERT INTO dbo.HASH_SMALL WITH (TABLOCK)
SELECT RN,
4000000 - RN, 4000000 - RN
,200000000 - RN, 200000000 - RN
, RN % 500000 , RN % 500000 , RN % 500000
, RN % 500000 , RN % 500000 , RN % 500000 
, 100000 - RN % 100000, RN % 100000
, 100000 - RN % 100000, RN % 100000
, 100000 - RN % 100000, RN % 100000
, 100000 - RN % 100000, RN % 100000
, 100000 - RN % 100000, RN % 100000
FROM (
    SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);


DROP TABLE IF EXISTS dbo.LOG_HASHES;
CREATE TABLE dbo.LOG_HASHES (
LOG_TIME DATETIME,
HASH_ALGORITHM INT,
SESSION_ID INT,
NUM_HASHES BIGINT
);

设置3 ：碰撞检测程序

GO
CREATE OR ALTER PROCEDURE dbo.RUN_HASHES_FOR_ONE_MINUTE (@HashAlgorithm INT)
AS
BEGIN
DECLARE @target_end_time DATETIME = DATEADD(MINUTE, 1, GETDATE()),
        @query_execution_count INT = 0;

SET NOCOUNT ON;

DECLARE @ProcName NVARCHAR(261); -- schema_name + proc_name + '[].[]'

DECLARE @RowCount INT;
SELECT @RowCount = SUM(prtn.[row_count])
FROM   sys.dm_db_partition_stats prtn
WHERE  prtn.[object_id] = OBJECT_ID(N'dbo.HASH_SMALL')
AND    prtn.[index_id] < 2;


-- Load assembly if not loaded to prevent load time from skewing results
DECLARE @OptionalInitSQL NVARCHAR(MAX);
SET @OptionalInitSQL = CASE @HashAlgorithm
       WHEN 1 THEN N'SELECT @Dummy = dbo.SpookyHash(0x1234);'
       WHEN 2 THEN N'' -- HASHBYTES
       WHEN 3 THEN N'' -- HASHBYTES
       WHEN 4 THEN N'' -- CHECKSUM
       WHEN 5 THEN N'SELECT @Dummy = dbo.SpookyHashLOB(0x1234);'
       WHEN 6 THEN N'SELECT @Dummy = SQL#.Util_HashBinary(N''MD5'', 0x1234);'
       WHEN 7 THEN N'SELECT @Dummy = SQL#.Util_HashBinary(N''SHA256'', 0x1234);'
       WHEN 8 THEN N'SELECT @Dummy = SQL#.Util_HashBinary8k(N''MD5'', 0x1234);'
       WHEN 9 THEN N'SELECT @Dummy = SQL#.Util_HashBinary8k(N''SHA256'', 0x1234);'
/* -- BETA / non-public code
       WHEN 10 THEN N'SELECT @Dummy = SQL#.Util_HashSHA256Binary8k(0x1234);'
       WHEN 11 THEN N'SELECT @Dummy = SQL#.Util_HashSHA256Binary(0x1234);'
*/
   END;


IF (RTRIM(@OptionalInitSQL) <> N'')
BEGIN
    SET @OptionalInitSQL = N'
SET NOCOUNT ON;
DECLARE @Dummy VARBINARY(100);
' + @OptionalInitSQL;

    RAISERROR(N'** Executing optional initialization code:', 10, 1) WITH NOWAIT;
    RAISERROR(@OptionalInitSQL, 10, 1) WITH NOWAIT;
    EXEC (@OptionalInitSQL);
    RAISERROR(N'-------------------------------------------', 10, 1) WITH NOWAIT;
END;


SET @ProcName = CASE @HashAlgorithm
                    WHEN 1 THEN N'dbo.RUN_SpookyHash'
                    WHEN 2 THEN N'dbo.RUN_HASHBYTES_MD5'
                    WHEN 3 THEN N'dbo.RUN_HASHBYTES_SHA2_256'
                    WHEN 4 THEN N'dbo.RUN_CHECKSUM'
                    WHEN 5 THEN N'dbo.RUN_SpookyHashLOB'
                    WHEN 6 THEN N'dbo.RUN_SR_MD5'
                    WHEN 7 THEN N'dbo.RUN_SR_SHA256'
                    WHEN 8 THEN N'dbo.RUN_SR_MD5_8k'
                    WHEN 9 THEN N'dbo.RUN_SR_SHA256_8k'
/* -- BETA / non-public code
                    WHEN 10 THEN N'dbo.RUN_SR_SHA256_new'
                    WHEN 11 THEN N'dbo.RUN_SR_SHA256LOB_new'
*/
                    WHEN 13 THEN N'dbo.RUN_HASHBYTES_SHA2_256_NVC'
                END;

RAISERROR(N'** Executing proc: %s', 10, 1, @ProcName) WITH NOWAIT;

WHILE GETDATE() < @target_end_time
BEGIN
    EXEC @ProcName;

    SET @query_execution_count = @query_execution_count + 1;
END;

INSERT INTO dbo.LOG_HASHES
VALUES (GETDATE(), @HashAlgorithm, @@SPID, @RowCount * @query_execution_count);

END;
GO

设置4：清理（删除所有测试程序）

GO
CREATE OR ALTER PROCEDURE dbo.VERIFY_NO_COLLISIONS (@HashAlgorithm INT)
AS
SET NOCOUNT ON;

DECLARE @RowCount INT;
SELECT @RowCount = SUM(prtn.[row_count])
FROM   sys.dm_db_partition_stats prtn
WHERE  prtn.[object_id] = OBJECT_ID(N'dbo.HASH_SMALL')
AND    prtn.[index_id] < 2;


DECLARE @CollisionTestRows INT;
DECLARE @CollisionTestSQL NVARCHAR(MAX);
SET @CollisionTestSQL = N'
SELECT @RowsOut = COUNT(DISTINCT '
+ CASE @HashAlgorithm
       WHEN 1 THEN N'dbo.SpookyHash('
       WHEN 2 THEN N'HASHBYTES(''MD5'','
       WHEN 3 THEN N'HASHBYTES(''SHA2_256'','
       WHEN 4 THEN N'CHECKSUM('
       WHEN 5 THEN N'dbo.SpookyHashLOB('
       WHEN 6 THEN N'SQL#.Util_HashBinary(N''MD5'','
       WHEN 7 THEN N'SQL#.Util_HashBinary(N''SHA256'','
       WHEN 8 THEN N'SQL#.[Util_HashBinary8k](N''MD5'','
       WHEN 9 THEN N'SQL#.[Util_HashBinary8k](N''SHA256'','
--/* -- BETA / non-public code
       WHEN 10 THEN N'SQL#.[Util_HashSHA256Binary8k]('
       WHEN 11 THEN N'SQL#.[Util_HashSHA256Binary]('
--*/
   END
+ N'
    CAST(FK1 AS BINARY(8)) + 0x7C +
    CAST(FK2 AS BINARY(8)) + 0x7C +
    CAST(FK3 AS BINARY(8)) + 0x7C +
    CAST(FK4 AS BINARY(8)) + 0x7C +
    CAST(FK5 AS BINARY(8)) + 0x7C +
    CAST(FK6 AS BINARY(8)) + 0x7C +
    CAST(FK7 AS BINARY(8)) + 0x7C +
    CAST(FK8 AS BINARY(8)) + 0x7C +
    CAST(FK9 AS BINARY(8)) + 0x7C +
    CAST(FK10 AS BINARY(8)) + 0x7C +
    CAST(FK11 AS BINARY(8)) + 0x7C +
    CAST(FK12 AS BINARY(8)) + 0x7C +
    CAST(FK13 AS BINARY(8)) + 0x7C +
    CAST(FK14 AS BINARY(8)) + 0x7C +
    CAST(FK15 AS BINARY(8)) + 0x7C +
    CAST(FK16 AS BINARY(8)) + 0x7C +
    CAST(FK17 AS BINARY(8)) + 0x7C +
    CAST(FK18 AS BINARY(8)) + 0x7C +
    CAST(FK19 AS BINARY(8)) + 0x7C +
    CAST(FK20 AS BINARY(8))  ))
FROM dbo.HASH_SMALL;';

PRINT @CollisionTestSQL;

EXEC sp_executesql
  @CollisionTestSQL,
  N'@RowsOut INT OUTPUT',
  @RowsOut = @CollisionTestRows OUTPUT;


IF (@CollisionTestRows <> @RowCount)
BEGIN
    RAISERROR('Collisions for algorithm: %d!!!  %d unique rows out of %d.',
    16, 1, @HashAlgorithm, @CollisionTestRows, @RowCount);
END;
GO

设置5 ：生成测试程序

DECLARE @SQL NVARCHAR(MAX) = N'';
SELECT @SQL += N'DROP PROCEDURE [dbo].' + QUOTENAME(sp.[name])
            + N';' + NCHAR(13) + NCHAR(10)
FROM  sys.objects sp
WHERE sp.[name] LIKE N'RUN[_]%'
AND   sp.[type_desc] = N'SQL_STORED_PROCEDURE'
AND   sp.[name] <> N'RUN_HASHES_FOR_ONE_MINUTE'

PRINT @SQL;

EXEC (@SQL);

测试1：检查是否冲突

SET NOCOUNT ON;

DECLARE @TestProcsToCreate TABLE
(
  ProcName sysname NOT NULL,
  CodeToExec NVARCHAR(261) NOT NULL
);
DECLARE @ProcName sysname,
        @CodeToExec NVARCHAR(261);

INSERT INTO @TestProcsToCreate VALUES
  (N'SpookyHash', N'dbo.SpookyHash('),
  (N'HASHBYTES_MD5', N'HASHBYTES(''MD5'','),
  (N'HASHBYTES_SHA2_256', N'HASHBYTES(''SHA2_256'','),
  (N'CHECKSUM', N'CHECKSUM('),
  (N'SpookyHashLOB', N'dbo.SpookyHashLOB('),
  (N'SR_MD5', N'SQL#.Util_HashBinary(N''MD5'','),
  (N'SR_SHA256', N'SQL#.Util_HashBinary(N''SHA256'','),
  (N'SR_MD5_8k', N'SQL#.[Util_HashBinary8k](N''MD5'','),
  (N'SR_SHA256_8k', N'SQL#.[Util_HashBinary8k](N''SHA256'',')
--/* -- BETA / non-public code
  , (N'SR_SHA256_new', N'SQL#.[Util_HashSHA256Binary8k]('),
  (N'SR_SHA256LOB_new', N'SQL#.[Util_HashSHA256Binary](');
--*/
DECLARE @ProcTemplate NVARCHAR(MAX),
        @ProcToCreate NVARCHAR(MAX);

SET @ProcTemplate = N'
CREATE OR ALTER PROCEDURE dbo.RUN_{{ProcName}}
AS
BEGIN
DECLARE @dummy INT;
SET NOCOUNT ON;

SELECT @dummy = COUNT({{CodeToExec}}
    CAST(FK1 AS BINARY(8)) + 0x7C +
    CAST(FK2 AS BINARY(8)) + 0x7C +
    CAST(FK3 AS BINARY(8)) + 0x7C +
    CAST(FK4 AS BINARY(8)) + 0x7C +
    CAST(FK5 AS BINARY(8)) + 0x7C +
    CAST(FK6 AS BINARY(8)) + 0x7C +
    CAST(FK7 AS BINARY(8)) + 0x7C +
    CAST(FK8 AS BINARY(8)) + 0x7C +
    CAST(FK9 AS BINARY(8)) + 0x7C +
    CAST(FK10 AS BINARY(8)) + 0x7C +
    CAST(FK11 AS BINARY(8)) + 0x7C +
    CAST(FK12 AS BINARY(8)) + 0x7C +
    CAST(FK13 AS BINARY(8)) + 0x7C +
    CAST(FK14 AS BINARY(8)) + 0x7C +
    CAST(FK15 AS BINARY(8)) + 0x7C +
    CAST(FK16 AS BINARY(8)) + 0x7C +
    CAST(FK17 AS BINARY(8)) + 0x7C +
    CAST(FK18 AS BINARY(8)) + 0x7C +
    CAST(FK19 AS BINARY(8)) + 0x7C +
    CAST(FK20 AS BINARY(8)) 
    )
    )
    FROM dbo.HASH_SMALL
    OPTION (MAXDOP 1);

END;
';

DECLARE CreateProcsCurs CURSOR READ_ONLY FORWARD_ONLY LOCAL FAST_FORWARD
FOR SELECT [ProcName], [CodeToExec]
    FROM @TestProcsToCreate;

OPEN [CreateProcsCurs];

FETCH NEXT
FROM  [CreateProcsCurs]
INTO  @ProcName, @CodeToExec;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- First: create VARBINARY version
    SET @ProcToCreate = REPLACE(REPLACE(@ProcTemplate,
                                        N'{{ProcName}}',
                                        @ProcName),
                                N'{{CodeToExec}}',
                                @CodeToExec);

    EXEC (@ProcToCreate);

    -- Second: create NVARCHAR version (optional: built-ins only)
    IF (CHARINDEX(N'.', @CodeToExec) = 0)
    BEGIN
        SET @ProcToCreate = REPLACE(REPLACE(REPLACE(@ProcToCreate,
                                                    N'dbo.RUN_' + @ProcName,
                                                    N'dbo.RUN_' + @ProcName + N'_NVC'),
                                            N'BINARY(8)',
                                            N'NVARCHAR(15)'),
                                    N'0x7C',
                                    N'N''|''');

        EXEC (@ProcToCreate);
    END;

    FETCH NEXT
    FROM  [CreateProcsCurs]
    INTO  @ProcName, @CodeToExec;
END;

CLOSE [CreateProcsCurs];
DEALLOCATE [CreateProcsCurs];

测试2：运行性能测试

EXEC dbo.VERIFY_NO_COLLISIONS 1;
EXEC dbo.VERIFY_NO_COLLISIONS 2;
EXEC dbo.VERIFY_NO_COLLISIONS 3;
EXEC dbo.VERIFY_NO_COLLISIONS 4;
EXEC dbo.VERIFY_NO_COLLISIONS 5;
EXEC dbo.VERIFY_NO_COLLISIONS 6;
EXEC dbo.VERIFY_NO_COLLISIONS 7;
EXEC dbo.VERIFY_NO_COLLISIONS 8;
EXEC dbo.VERIFY_NO_COLLISIONS 9;
EXEC dbo.VERIFY_NO_COLLISIONS 10;
EXEC dbo.VERIFY_NO_COLLISIONS 11;

要解决的验证问题

尽管着重于单个SQLCLR UDF的性能测试，但前面讨论的两个问题并未解决。纳入测试，但理想情况下应进行调查n以确定哪种方法满足所有要求。每个查询将对该函数执行两次（对于导入行一次，对于当前行一次）。到目前为止，这些测试在测试查询中仅引用了UDF一次。这个因素可能不会改变选项的排名，但是为了以防万一，不应忽略它。

在此后被删除的评论中，Paul White提到：

用CLR标量函数替换HASHBYTES的一个缺点-似乎CLR函数不能使用批处理模式，而HASHBYTES可以。从性能角度来看，这可能很重要。

因此，这是要考虑的事情，显然需要进行测试。如果SQLCLR选项没有提供优于内置HASHBYTES的任何好处，则将所罗门建议的捕获现有哈希（至少对于最大的表）捕获到相关表中的建议增加了分量。

#4 楼

通过池化和缓存在函数调用中创建的任何对象，您可能可以提高所有.NET方法的性能以及可伸缩性。上面的Paul White的代码的EG：

static readonly ConcurrentDictionary<int,ISpookyHashV2> hashers = new ConcurrentDictonary<ISpookyHashV2>()
public static byte[] SpookyHash([SqlFacet (MaxSize = 8000)] SqlBinary Input)
{
    ISpookyHashV2 sh = hashers.GetOrAdd(Thread.CurrentThread.ManagedThreadId, i => SpookyHashV2Factory.Instance.Create());

    return sh.ComputeHash(Input.Value).Hash;
}

SQL CLR不鼓励并尝试防止使用静态/共享变量，但是如果将它们标记为，它将允许您使用共享变量只读。当然，这毫无意义，因为您只需分配一个可变类型的单个实例即可，例如ConcurrentDictionary。

有趣的是...如果一次又一次地使用同一实例，该线程是否安全？我知道托管哈希有一个Clear（）方法，但我对Spooky的关注程度还不高。

–所罗门·鲁兹基
19年2月8日在23:28

正如@SolomonRutzky所知，我一直不喜欢将可变的东西放在静态只读中的想法。由于ManagedThreadId的缘故，它似乎很安全，但无论如何它还是给了我eeby-jeebies。性能/并发性的提高对于我选择这样做必须非常重要。主人确实试图防止人们用静电做愚蠢的事情，但这是路障，而不是绝对的障碍。我更喜欢平坦的轨道🙂

–保罗·怀特♦
19年2月8日在23:34

@PaulWhite和David。我可能做错了什么，也可能是SHA256Managed和SpookyHashV2之间的区别，但是我尝试了一下，并没有看到很多性能改进。我还注意到，特定查询中的所有SQLCLR引用的ManagedThreadId值都相同。我测试了对同一个函数的多个引用，以及对一个不同函数的引用，所有这3个都被赋予了不同的输入值，并返回了不同的（但期望的）返回值。这不会增加比赛条件的机会吗？公平地说，在测试中我没有看到任何东西。

–所罗门·鲁兹基
19年2月10日在19:13

编程黑洞网

使用SQL CLR标量函数模拟HASHBYTES的可伸缩方法是什么？

#1 楼

#2 楼

#3 楼

#4 楼

评论