Return to Revisions

I'm not sure if parallelism will be any / significantly better with SQLCLR. However, it is really easy to test since there is a hash function in the Free version of the SQL# SQLCLR library (which I wrote) called Util_HashBinary. Supported algorithms are: MD5, SHA1, SHA256, SHA384, and SHA512.

It takes a VARBINARY(MAX) value as input, so you can either concatenate the string version of each field (as you are currently doing) and then convert to VARBINARY(MAX), or you can go directly to VARBINARY for each column and concatenate the converted values (this might be faster since you aren't dealing with strings or the extra conversion from string to VARBINARY). Below is an example showing both of these options. It also shows the HASHBYTES function so you can see that the values are the same between it and SQL#.Util_HashBinary.

Please note that the hash results when concatenating the VARBINARY values won't match the hash results when concatenating the NVARCHAR values. This is because the binary form of the INT value "1" is 0x00000001, while the UTF-16LE (i.e. NVARCHAR) form of the INT value of "1" (in binary form since that is what a hashing function will operate on) is 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;

You can test something more comparable to the non-LOB Spooky using:

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];

Beyond that aspect of the question, there are some additional thoughts that might help this process that are not related to SQLCLR. You mentioned a few things:

1. we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded.

and:

1. I cannot save off the value of the hash for the reporting table. It's a CCI which doesn't support triggers or computed columns

and:

1. the tables can be updated outside of the ETL process

It sounds like the data in this reporting table is stable for a period of time, and is only modified by this ETL process.

If nothing else modifies this table, then we really don't need a trigger or indexed view after all (I originally thought that you might).

Since you can't modify the schema of the reporting table, would it at least be possible to create a related table to contain the pre-calculated hash (and UTC time of when it was calculated)? This would allow you to have a pre-calculated value to compare against next time, leaving only the incoming value that requires calculating the hash of. This would reduce the number of calls to either HASHBYTES or SQL#.Util_HashBinary by half. You would simply join to this table of hashes during the import process.

You would also create a separate stored procedure that simply refreshes the hashes of this table. It just updates the hashes of any related row that has changed to be current, and updates the timestamp for those modified rows. This proc can/should be executed at the end of any other process that updates this table. It can also be scheduled to run 30 - 60 minutes prior to this ETL starting (depending on how long it takes to execute, and when any of these other processes might run). It can even be executed manually if you ever suspect there might be rows that are out of sync.

It was then noted that:

there are over 500 tables

That many tables does make it more difficult to have an extra table per each to contain the current hashes, but this is not impossible as it could be scripted since it would be a standard schema. The scripting would just need to account for source table name and discovery of source table PK column(s).

ALSO: Paul White, in a comment on this answer, mentioned:

One downside of replacing HASHBYTES with a CLR scalar function - it appears that CLR functions cannot use batch mode whereas HASHBYTES can. That might be important, performance-wise.

So that is something to consider, and clearly requires testing.

ALSO: regardless of SQLCLR vs built-in HASHBYTES, I would still recommend converting directly to VARBINARY as that should be faster. Concatenating strings is just not terribly efficient. And, that's in addition to converting non-string values into strings in the first place, which requires extra effort (I assume the amount of effort varies based on the base type: DATETIME requiring more than BIGINT), whereas converting to VARBINARY simply gives you the underlying value (in most cases).

Note: Util_HashBinary uses the managed SHA256 algorithm that is built into .NET, and should not be using the "bcrypt" library.

UPDATE 2019-02-08

I have been doing a lot of tinkering and testing and found some changes that definitely increase the performance when using .NET's SHA256Managed class. I have two new functions that incorporate these changes and have sent a beta copy of SQL# to Joe for testing since I am not ready to publish a new release at this time. It still might not match the scalability of the Spooky hash suggested by Paul White, but it should definitely be better than Util_HashBinary.

I have updated the test harness in the community wiki answer below to include:

1. pre-loading of the SQLCLR assemblies to ensure that the load time overhead doesn't skew the results.
2. a verification procedure to ensure that there are no collisions since a super-fast function isn't relevant if it doesn't work.

ALSO, regardless of which hash algorithm ultimately proves to be the most scalable, I still highly recommend finding at least a few tables (perhaps there are some that are MUCH larger than the rest of the 500 tables) and setting up a related table to capture current hashes so the "current" values can be known prior to the ETL process. Even the fastest function can't out-perform never having to call it in the first place ;-).