I am currently working on an solution that consists of 10 Packages which utilize a fuzzy lookup to perform some matching functionality amongst other things. In some cases there are 3 fuzzy lookups in a package running in parallel. We have one reference table, containing 30Million records, that it used for the reference of all the fuzzy lookup.
We have created indexes for each fuzzy lookup component which has meant that there are more than 13 indexes on the same reference table. When the packages run they take forever as a large amount of time is spent on building the indexes in the pre-execute phase.
It appears to me to be very inefficient to have several indexes on the same reference table which in some cases are used to do a fuzzy lookup on the same fields. Does anybody have an alternative to this setup that they may have used or are aware.
Is there anyway to improve the performance of the lookup?
I have set it to reuse existing index but and set the warm cache to true. It seems that it allows for one index per component and that index cannot be used in another component. Which I find very irritating. For packages where we have 3 fuzzy lookups running in parallel it just takes to long. Really need another quicker way of doing this.