Dimension lookup optimization

To update the dimension/fact, you must find out if a record already exists. Normally you do this by looking at the business key (natural key) in the dimension. If it is not found, it is a new record and it must be inserted. If it is found and has changed, the record must be updated (type 1) or a new record must be created (type 2). I have been experimenting a bit to speed up the lookup process. Below are my findings so far.

A lookup on an integer value is faster than on a varchar. In my data warehouse I treat all business keys as varchar fields, often also a combination of two fields. This prevents errors in the future if, for example, someone decides to add a letter to a product code. Example:
- DimElement: companycode + elementcode
- DimUitvoerCode: companycode + uitvoercode

We can choose to build the source key in the dimension directly and store it as one field, for example 100_101 (companycode, elementcode). We can also skip this and do the lookup on these two fields. I prefer to combine the two fields, with an underscore between them, so you can trace the key back to the source.

Another option is to generate a checksum/hash over the two fields. The advantage is that the lookup is done on just one field, and the name of the source key becomes generic. We then call every source key, for example, BronHash, and it always has the same type and the same length.

Checksum T-SQL function VS SSIS Component
Checksum is a 4-byte integer field, so a lookup on it is fast. Checksum does not work well to generate a checksum over certain columns, because duplicates can occur (proven). Generating a checksum over the business key is not reliable.

If I generate a CRC32 over the three columns that make up the business key, I get many duplicate checksums (for example on a table from an accounting program).

SELECT A, COUNT(*) AS Aantal
FROM
(SELECT BINARY_CHECKSUM(cmpcode,doccode,docnum) as A
FROM dbo.oas_dochead_REG as DH
) AS X
GROUP BY A
HAVING COUNT(*) > 1

SSIS Component
http://www.sqlis.com/post/Checksum-Transformation.aspx

The SSIS checksum component from the community supports several types of checksums.

CRC32
One of them is CRC32. I tested it, but it gives duplicates, so it is not reliable. This happens both when you generate a checksum over one column and over several columns.

Frameworkchecksum
The frameworkchecksum technique also gives duplicates. I tested it with 200,000 records and it generated two duplicates.

Original
After testing, the last one also gives eight duplicated checksums, so it is not suitable either.

Conclusion
I could of course use the MD5 hash function from T-SQL, but it is about 50% slower because it is a varchar field. The checksum is an integer field. In that case I might as well do the lookup on the source key, because that is even smaller.

Update:
In our data warehouse we use a Binary(20) field as LookupHash in the dimensions. The advantage is that the source key now has a generic name. When you fill the fact, you can now do the lookup for the surrogate key on the LookupHash field. Another advantage is that a binary field is easy to index.

In short: when you create your dimension, you always generate a LookupHash column of type binary(20). Then, in the JOIN, you also convert the key from the fact to the same binary(20) over the same columns. The result is now the same as in the dimension. Then you can get the surrogate key fast!