are the caching messages in ssis for a lookup an indication of poor performance?
-
Hi, i am curious if i made the best choices for a lookup component in ssis. my head is saying 2.5 million lookups is too many.
my pkg makes available 2.5 million lookup records when it runs. thats probably a lot more than i need but i feel that my predicate asking for the last year's data from server 1 is important.
below i show all the displays in the catalog's dashboard for the lookup component. i see incrementing numbers till the 2.5 million shows. it looks like close to 50 seconds is what it takes for these 2.5 million lkps to be added to the buffer. and i suppose i could go about this a different way, perhaps landing all 2.5 on the server (server 2) where the dtsx runs, or on the server (server 3) where the data driving the lookups reside.
to start , can the community tell me why ssis is showing these incremental lookup cache numbers as it creates the lookup? is this a sign of something inefficient already happening?
-
it is a sign that your process likely should be changed as you said - high volumes like this are in most cases best dealt by pushing the lookup data to the source (so they can be joined in SQL) or by loading the other data onto a staging table (on the server where the lookup table resides) and then joining it there before sending it to final destination.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply