posted on Tuesday, October 04, 2005 6:45 PM
by
SimonSabin
Range lookups in SSIS
My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Range lookups in SSIS
The new RSS feed is here
Recently someone posted to the SSIS Forums about performance of DTS and SSIS. They had found the performance of SSIS to be well below that of DTS and of other ETL tools. On investigation we found that the package was a fact load and that one of the tasks was a range lookup, i.e. given a set of ranges which one does a particular value fit. To achieve this the lookup component had been changed to perform a range lookup in the SQL. This is where the problem lies. In doing this only a partial cache is possible, meaning that only values that are retrieved cached, so a subsequent lookup with the same values results in a cache hit. However when the data is not very similar you end up with not many cache hits at all. As in the case where the values were monetary values.
... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Range lookups in SSIS