When I first started building the Data Vault at Georgian, I couldn’t decide what column data type to use as my tables’ primary key.
I had heard that integer joins vastly outperform string joins, and was worried about degrading join performance as our data grew.
In the SQL databases of the operational world, this decision is pretty much made for you by giving you auto-incrementing int primary keys out of the box.
In the Data Warehousing world, however, whether you’re building a Kimball or Data Vault or something else, you need to make this choice explicitly.
You can generate an integer, a UUID string, or hash your columns into a single column, which comes with many benefits. As if that wasn’t complex enough, your hashed keys can be stored as strings or bytes, and each algorithm’s output may vary in length.
This brings up a question:
After some digging, I found some benchmarks for transactional databases, but that’s not what I was looking for. Logically speaking, integers must be faster than strings and byte-strings because there are generally fewer bytes to scan. But… by how much!?
Knowing the answer seemed very important because, in a data warehouse, a bad choice can get multiplied a billion-fold.
I finally buckled under the pressure of curiosity and decided to run a benchmark on BigQuery to answer this question for myself.
I decided to generate 5 million rows of random numbers and test joining them (without cache) on the following types of keys:
- sequential integer (1-5M)
- farm fingerprint hash integer
- MD5 as bytes
- MD5 as a string (hex-encoded)
- SHA1 as bytes
- SHA1 as a string (hex-encoded)
Here is the code I used to generate the tables I wanted to join:
/* GENERATE_ARRAY has a limit of 1M rows so I had to union a bunch of them together */ WITH keys_1 AS (SELECT * FROM UNNEST(GENERATE_ARRAY(1,1000000)) AS key), keys_2 AS (SELECT * FROM UNNEST(GENERATE_ARRAY(1000001,2000000)) AS key), keys_3 AS (SELECT * FROM UNNEST(GENERATE_ARRAY(2000001,3000000)) AS key), keys_4 AS (SELECT * FROM UNNEST(GENERATE_ARRAY(3000001,4000000)) AS key), keys_5 AS (SELECT * FROM UNNEST(GENERATE_ARRAY(4000001,5000000)) AS key), keys_union AS ( SELECT key FROM keys_1 UNION ALL SELECT key FROM keys_2 UNION ALL SELECT key FROM keys_3 UNION ALL SELECT key FROM keys_4 UNION ALL SELECT key FROM keys_5 ), keys_hashed AS ( SELECT key, MD5(CAST(key AS STRING)) as key_md5_bytes, TO_HEX(MD5(CAST(key AS STRING))) as key_md5_str, FARM_FINGERPRINT(CAST(key AS STRING)) AS key_farm, SHA1(CAST(key AS STRING)) AS key_sha_bytes, TO_HEX(SHA1(CAST(key AS STRING))) AS key_sha_str FROM keys_union ) SELECT *, rand() AS val FROM keys_hashed
And here is the code I used to test make join:
SELECT t1.val, t2.val FROM bq_benchmark.t1 JOIN bq_benchmark.t2 USING(<key column here>);
I ran the join query 30 times for each key type to use a Z-test to test for the difference between the mean query times and get reliable confidence intervals.
Some definitions you may find helpful when interpreting the results:
Lower Bound 90% Confidence Interval: There’s a 5% probability that the true mean query time is below this number.
Upper Bound 90% Confidence Interval: There’s a 5% probability that the true mean query time is Above this number.
Standard Deviation: A measure of how much deviation (on either side) from the mean query time we observed in our sample on average.
Standard Error of the Estimate of The Mean: How much the true mean query time deviates from the estimated mean query time of our sample.
90% CI (s)
|Mean Query |
90% CI (s)
|Std. Dev||Std. Error|
You may also care to have a comparative view of the above data. To keep things simple, I will only compare the difference in mean query times and ignore the confidence intervals of the differences (which I’ve made available in the excel download below).
|Base Type||Compared Type||Absolute Diff||Relative|
(1+ is Slower)
|MD5 Bytes||0.52 s||1.13ⅹ||100%|
|MD5 String||2.03 s||1.5ⅹ||100%|
|SHA1 String||1.59 s||1.39ⅹ||100%|
|Farm||MD5 Bytes||0.28 s||1.06ⅹ||98.96%|
|MD5 String||1.79 s||1.42ⅹ||100%|
|SHA1 Bytes||0.48 s||1.11ⅹ||100%|
|SHA1 String||1.35 s||1.31ⅹ||100%|
|MD5 Bytes||MD5 String||1.51 s||1.33ⅹ||100%|
|SHA1 Bytes||0.2 s||1.03ⅹ||91.93%|
|SHA1 String||1.07 s||1.23ⅹ||100%|
|SHA1 Bytes||MD5 String||1.31 s||1.27ⅹ||100%|
|SHA1 String||0.87 s||1.18ⅹ||100%|
|SHA1 String||MD5 String||0.44 s||1.08ⅹ||99.83|
Here is everything I took away from this experiment.
- Integers are about 1.2x faster than bytes and about 1.4x faster than strings.
- If you have access to FARM_FINGERPRINT and you’re only using BigQuery, go ahead and use that (you can always switch it up later)
- Otherwise, simply use MD5 as your hash function stored as bytes.
- If you choose to use a string, don’t use hex encoding as I did. Base64 encoding will result in smaller strings and thus faster query times than this (but not as fast as raw bytes)
I’ve made my entire experiment available for you to download in an Excel sheet. I’ve made it dead simple to use. Feel free to add your own data to it and experiment on the data warehouse of your choice!