SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

sauce1979
Starting Member

46 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?

visakh16
Very Important crosS Applying yaK Herder

India
52325 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
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sauce1979
Starting Member

46 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000