So the target table is just read once and copied into memory and then accessed via the cache internal B*Tree index. Performance is okay as long as the amount of data is not too huge.
In the CPU monitor you can see that quite nicely, the first 15 seconds the Table Comparison transform reads the data from the comparison table into the cache - memory constantly increasing. And then it take 6 seconds generating the source data and do the comparison of each row with the cache index. The Elapsed time from each transform is 6 seconds each, the cache load time is part of the dataflow start and can be seen in the absolute time.
Since version 12.1 above execution looks slightly different. Now no longer the Table Comparison transform does read the cache by itself and during the start, it got a Reader and a cache thread on its own. The advantage of that approach is all the advance DI feature for reading can be used, like array fetches etc. As a consequence, the monitor log looks different. The dataflow now has two source threads, one is the Row_Gen the other the CacheComparisonTableReader. Bot start in parallel the moment the dataflow got started. The Row_Gen and its subsequent transforms will quickly process the first set of rows and pass them downstream until the TC transform. This transform will tell its upstream thread that it cannot consume rows yet, so each of the upstream objects runs until their output buffer is full with a few hundred rows and then pause. The TC reader continuous reading the data and building the cache until all the data is in, then TC will accept the first row from its upstream object, perform a lookup in its cache and send it to the downstream object.
In other words, now the elapsed time contains the cache build time as well and we need to compare the 6 seconds from before with the time 20.4 seconds minus the CacheComparisonTableReader time of 15.7 seconds which is 4.5 seconds - the time the Map_Operation took from receiving the first record from TC and having processed the very last.
The monitor log file shows that quite nicely, Row_Gen starts but has to pause until the cache is built.
Job_TableComparison_cached.zip (4.37 KB)