Beyond that aspect ofNote: Util_HashBinary uses the question, there are some additional thoughts that might help this processmanaged SHA256 algorithm that areis built into .NET, and should not related to SQLCLRbe using the "bcrypt" library. You mentioned a few things:
Beyond that aspect of the question, there are some additional thoughts that might help this process:
Additional Thought #1
You mentioned a few things:
ALSO:Still, 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 ;-).
Additional Thought #2
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.
Additional Thought #3
UPDATE 2019-02-08 Further testing showed that one area that impacts performance (over this volume of executions) is input parameters: how many and what type(s).
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 thanThe Util_HashBinary SQLCLR function that is currently in my SQL# library has two input parameters: one VARBINARY (the value to hash), and one NVARCHAR (the algorithm to use). This is due to my mirroring the signature of the HASHBYTES function. However, I found that if I removed the NVARCHAR parameter and created a function that only did SHA256, then performance improved quite nicely. I assume that even switching the NVARCHAR parameter to INT would have helped, but I also assume that not even having the extra INT parameter is at least slightly faster.
Also, SqlBytes.Value might perform better than SqlBinary.Value.
I havecreated two new functions: Util_HashSHA256Binary and Util_HashSHA256Binary8k for this testing. These will be included in the next release of SQL# (no date set for that yet).
I also found that the testing methodology could be slightly improved, so I updated the test harness in the community wiki answer below to include:
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 ;-).
UPDATE 2019-02-10
Additional Thought #4
(I had made this suggestion early on in a comment but forgot to move it into my answer before the comment was deleted)
Depending Depending on where the bottleneck is, it might even help to use a combination of built-in HASHBYTES and a SQLCLR UDF to do the same hash. If built-in functions are constrained differently / separately from SQLCLR operations, then this approach might be able to accomplish more concurrently than either HASHBYTES or SQLCLR individually. It's definitely worth testing.
Additional Thought #5
The caching of the hashing algorithm object as suggested in David Browne's answer certainly seems interesting, so I tried it and found the following two points of interest:
For whatever reason, it does not seem to provide much, if any, performance improvement. I could have done something incorrectly, but here is what I tried:
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);
}
The ManagedThreadId value appears to be the same for all SQLCLR references in a particular query. I tested multiple references to the same function, as well as a reference to a different function, all 3 being given different input values, and returning different (but expected) return values. For both test functions, the output was a string that included the ManagedThreadId as well as a string representation of the hash result. The ManagedThreadId value was the same for all UDF references in the query, and across all rows. But, the hash result was the same for the same input string and different for different input strings.
While I didn't see any erroneous results in my testing, wouldn't this increase the chances of a race condition? If the key of the dictionary is the same for all SQLCLR objects called in a particular query, then they would be sharing the same value or object stored for that key, right? The point being, even thought it seemed to work here (to a degree, again there did not seem to be much performance gain, but functionally nothing broke), that doesn't give me confidence that this approach will work in other scenarios.
