Tuning the Hive Engine for Big Data Management®

Back Next

Case Study: Lookup vs. Joiner Transformations

To illustrate how the lookup source data volume, number of columns projecting from the Lookup transformation, and the port precisions impact the lookup performance, the following two mappings were run by varying the lookup data volume and columns.

The following image shows the mapping that contains Lookup transformations:

The following image shows the mapping that contains detailed outer Joiner transformations:

Result

When the lookup data size grows beyond 2 GB, joiners outperform lookups.

The following two graphs show that as the lookup data size grows over 800 MB, lookups start to lose their competitive edge over the joiners. But more realistic lookup use cases will project only a subset of the columns from the lookup transformations.

Graph for 240 MB lookup data before optimization:

Graph for 830 MB lookup data before optimization:

The following two graphs show that configuring the right precision for ports and propagating only the desired ports positively impact the performance of lookups.

Graph for 830 MB data after optimization: