Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sauce1979
Starting Member
47 Posts |
 Posted - 2013-08-20 : 08:39:58
|
| 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
52326 Posts |
Posted - 2013-08-21 : 00:25:23
|
| Did you set match index option as ReuseExistingIndex?Also you could configure lookup to partially cache the indexes------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
sauce1979
Starting Member
47 Posts |
Posted - 2013-08-21 : 06:56:10
|
| 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. |
|
|
|
|
|
|
Fuzzy Lookup Alternative