Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Fuzzy Lookup Alternative
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

47 Posts

Posted - 08/20/2013 :  08:39:58  Show Profile  Reply with Quote
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?

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 08/21/2013 :  00:25:23  Show Profile  Reply with Quote
Did you set match index option as ReuseExistingIndex?
Also you could configure lookup to partially cache the indexes

SQL Server MVP
Go to Top of Page

Starting Member

47 Posts

Posted - 08/21/2013 :  06:56:10  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000