In this blog we look at how to optimise SSIS lookups with HASHBYTES. I was recently working on a load for a Dimension table which worked like the below.
The package follows a fairly standard routine. Data for the Dimension is transformed into the required format using SSIS, following this it is inserted into the target table. On the way an initial check is made to determine if the record is new ‘LKU – Check Exists’ and a secondary check to determine if the record has changed, which is subsequently used in an update routine.
Although the package was incrementally loading, it was having to sometimes check several million rows to determine if these had changed, as the table was quite wide I decided to implement the HASHBYTES() function in order to take a hash of the columns for comparison rather than compare each of the columns.
The initial results were quite promising with the package running in around half the time, however on a particularly big load I noticed the package failed as it had run out of memory for the second Lookup (full cache was used). I found this odd because the HASHBYTES function only returned a single value and I anticipated this would be smaller than the sum of the columns, however on a little deeper investigation I found that by default any row returned using the HASHBYTES function are all of a default size 8000 bytes.
A quick check of the SQL Documentation of the HASHBYTES function at http://msdn.microsoft.com/en-GB/library/ms174415.aspx states that the size of the data returned for the HASHBYTES function when used with the SHA2-256 algorithm is 32 bytes meaning that most of this space was being wasted.
Therefore changing the formula from 1 to 2 below significantly increases the efficiency of the lookup and also make the cache size required smaller by a factor of around 250!
1. HASHBYTES(‘SHA2_256’,[ProductName] + ‘|‘ + [CategoryName]) AS HashValue
2. CONVERT(VARBINARY(32),HASHBYTES(‘SHA2_256’,[ProductName] + ‘|‘ + [CategoryName])) AS NewHash
Upon doing this the size of each row was significantly reduced, the package is running faster than before and most importantly there are no memory issues 🙂
How has the travel and transport sector reacted to the Covid-19 crisis?
The Covid-19 pandemic shocked the world. It sent unprecedented ripples of change within numerous sectors
Sep
Databricks for Dummies: An Introduction to Databricks
Databricks is an organisation and industry-leading commercial cloud-based data engineering platform for processing and transforming
Sep
Prepare and pass DP-300 (Administrating relational databases on Microsoft Azure)
Azure Database Administration? An Azure Database Administrator is responsible for implementing, managing, troubleshooting and operating the data
Sep
Resolving merge conflicts in Azure DevOps via extension
Part of the role of source control is to ensure that any simultaneous updates to
Aug
What Adatis and Microsoft’s Cloud Solution Provider (CSP) Licencing can offer you
New licencing announcements from Microsoft: Microsoft has recently announced new updates are now available in
Aug
How To Build Accountable Project Teams
When projects go wrong, as they sometimes do, blame is often shifted to the project
Aug
Microsoft Inspire 2021- A 2-day announcement roundup
Microsoft Inspire 2021- A 2-day announcement roundup Recently Microsoft’s annual Inspire partner conference kicked
Jul
Rapidly Deploying Azure Policies In Terraform
As you may all know Terraform serves a great purpose in deploying resources and infrastructure
Jul