This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Adding more lookup decreasing the performance (Redshift lookup / SQL server Lookup)

kumar
New Contributor II

‎02-07-2018 05:11 AM

Hi Team,

     Need your help /solution to solve the problem.

When we trying to add more lookup’s in the pipeline performance is drastically decreasing . To load some 30K records with 3 lookup snap, it took around 30 minutes. Instead of lookup, if i use the join instead of lookup pipeline has completed within 3 minutes.

source → Redshift Lkp1 (Good performance) → Redshift Lkp2 (Slow) → Redshift Lkp3 (Very slow) → Target

Instead of Redshift , I tried with SQL server lookup and getting the same issue… Also, interchanged the lookup’s and tried it… That’s also not working…

Do you have any idea, what could be the reason for this performance issue?.

Thanks,

0 Kudos
Reply
1 REPLY 1

stephenknilans
Contributor

‎02-07-2018 02:18 PM

Well, query lookup, especially on databases, can be very high. Is there an option to cache it? Some ETL products run a query to get all candidates, and they use a binary tree to search it. It is not only a bit faster because it gets rid of so much network traffic and latency, but it also is faster because you have ONE moderate operation instead of maybe thousands or more ones that are smaller but still very expensive.

The join, works essentially the same way the binary tree method would, but requires changing your logic a little, etc…

Steve

0 Kudos
Reply