As you can see, I have shown you two different ways of handling lookup no match in SSIS. In first design alternative, lookup no match records are send to derived column transform to insert default value for non matching column and a union all transformation union all data . In second approach , lookup no matching are ignored and a derived column replace NULL value of that column by a default value.
which one is fast and why it is fast?? .As per the experts, second approach is 21% faster than first approach. Quite a significant performance improvement right?But why?
SSIS dataflow works in buffer-oriented architecture.it take a data chunk into memory and process that data in memory.As this data flows through the pipeline, SSIS attempts to reuse data from prior buffers as much as possible when additional operations are performed.At the same time, there are 3 main category of components in data flow. This categorization is done using how these components use buffer architecture.
- Row Transformations– Row transformations have the advantage of reusing existing buffers and do not require data to be copied to a new buffer to complete the transformation.Examples:Derived Column, Data Conversion, Multicast, and Lookup.
- Partially blocking transformations-With partially blocking transformations, the output of the transformation is copied into a new buffer and a new thread may be introduced into the data flow. Example:Merge, Merge Join, and Union All
- Blocking transformations-when a blocking transformation is encountered in the data flow, a new buffer is created for its output and a new thread is introduced into the data flow.Example:Aggregate and Sort
This partial blocking and blocking transformation makes a huge impact in performance wise.So when we avoid using partial and blocking transformation as in second approach, it gives more performance gain.Not only that second first approach makes one execution path whereas first one has two execution paths.About execution path , I will talk in another blog post.