Post reply

SSIS Lookup Tasks

  • March 31, 2008 at 10:14 am

    #124624

    I have inherited a SSIS project which uses the lookup task quite a number of times. I have noticed however in many cases there are hundreds of thousands of rows being loaded and in one case approx 20 million rows being loaded in to a lookup task. I was wondering is this good pratise and is there an advised maximum number of rows which should be loaded.

    Regards niall

  • March 31, 2008 at 10:57 am

    #796061

    There is not really a defined limit, but memory may be a problem with 20 million rows being cached in a lookup. You need to performance test it to determine if this is the best approach for your situation.

    You end up with a number of options. You could just turn on the memory restriction, you could bring all 20 million rows into a table and just use a T-SQL join, you could change the process to use a merge join, etc.

    Every situation is different. However, 20 million seems like a bit much so I would spend some time making sure you cannot do this better in another way.

  • April 1, 2008 at 7:25 am

    #796471

    hey micheal how coudl i know the performance ....is it the time it gets executed or is there any other aspects which i need to consider...plz let me know...

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • April 1, 2008 at 7:50 am

    #796491

    Typically, I would care about two things, the time it takes for the entire task to complete (pre-execute caching and actual execution) and the amount of system resources needed to get the job done (memory and processor time).

    If there is nothing else running on the ETL server at the time of this package being run (and you can predict that will be the case for awhile), memory and processor time used may not be of concern. That never seems to be the case for me though.

    Remember to plan for the future at least a little. If your packages take 95% of the memory available, you may have a problem if you end up with 10% more records a few months from now.

  • April 1, 2008 at 7:59 am

    #796499

    thkz got it but a small doubt agaian where can i see how much of the memory is used in the execution results or where can i see that ...

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • April 1, 2008 at 8:11 am

    #796512

    Thanks Michael. I have been doing some tests and the initial load of the cache seems to fail. Was developed in a testing environment which didnt have the volume of data production has.

  • April 1, 2008 at 8:34 am

    #796532

    I would start with just turning on the default memory limits in the lookup component.

  • April 1, 2008 at 8:43 am

    #796548

    gotchu thkz a lot

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply