none
What is difference between FULL Cache and PARTIAL Cache in LOOKUP Transformation in SSIS? RRS feed

  • Question

  • How can I use FULL Cache or Partial Cache ?

    What is the Major difference between FULL Cache and PARTIAL Cache in LOOKUP Transformation in SSIS ?

    Thanks

    Friday, February 20, 2015 7:12 AM

Answers

  • To use Lookup Transformation, we provide data from source and reference data so that it will find matches based on condition and at then end it gives us Matched, Ummatched data.

    Full Catche: Now if the reference data is very huge we can use Full Catche. So that there are no trips to the database to get the data from SQL to SSIS. Entire data is catched before exuecuting Lookup Transformation Task

    Partial Catche:
    If a match is found, both the key and lookup values are added to the local cache on a just-in-time basis. If that same key enters the Lookup Component again, it can retrieve the matching value from the local cache instead of the reference table

    You can use miss - cache here with Partial Catche, It remembers values that it did not previously find in the reference table and thus will not incur the expense of looking for them again.

    Recommended Blogs:

    MSDN Blog: Lookup cache modes

    Simple-talk: Implementing Lookup Logic in SQL Server Integration Services


    -Vaibhav Chaudhari

    Friday, February 20, 2015 7:24 AM
  • Hi ,

    Full cache :
    Once the database is queried , during the pre-execute phase of the data flow. The entire reference set is pulled into memory.
    uses the most memory
    lookup operations will be very fast
    lookup will not swap memory out to disk, so your data flow will fail if you run out of memory.

    When to use this cache mode:

        When you're accessing a large portion of your reference set
        When you have a small reference table
        When your database is remote or under heavy load, and you want to reduce the number of queries sent to the server

    Partial Cache :
        Lookup cache starts off empty at the beginning of the data flow. When a new row comes in, the lookup transform checks its cache for the matching values. If no match is found, it queries the database. If the match is found at the database, the values are cached so they can be used the next time a matching row comes in.

        lookup operations would be slower, as you will most likely be hitting the database more often

    When to use this cache mode

        When you're processing a small number of rows and it's not worth the time to charge the full cache
        When you have a large reference table
        When your data flow is adding new rows to your reference table
        When you want to limit the size of your reference table by modifying query with parameters from the data flow

    http://blogs.msdn.com/b/mattm/archive/2008/10/18/lookup-cache-modes.aspx

    Thanks

        

    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem. http://techequation.com

    Friday, February 20, 2015 9:29 AM

All replies

  • To use Lookup Transformation, we provide data from source and reference data so that it will find matches based on condition and at then end it gives us Matched, Ummatched data.

    Full Catche: Now if the reference data is very huge we can use Full Catche. So that there are no trips to the database to get the data from SQL to SSIS. Entire data is catched before exuecuting Lookup Transformation Task

    Partial Catche:
    If a match is found, both the key and lookup values are added to the local cache on a just-in-time basis. If that same key enters the Lookup Component again, it can retrieve the matching value from the local cache instead of the reference table

    You can use miss - cache here with Partial Catche, It remembers values that it did not previously find in the reference table and thus will not incur the expense of looking for them again.

    Recommended Blogs:

    MSDN Blog: Lookup cache modes

    Simple-talk: Implementing Lookup Logic in SQL Server Integration Services


    -Vaibhav Chaudhari

    Friday, February 20, 2015 7:24 AM
  • Hi ,

    Full cache :
    Once the database is queried , during the pre-execute phase of the data flow. The entire reference set is pulled into memory.
    uses the most memory
    lookup operations will be very fast
    lookup will not swap memory out to disk, so your data flow will fail if you run out of memory.

    When to use this cache mode:

        When you're accessing a large portion of your reference set
        When you have a small reference table
        When your database is remote or under heavy load, and you want to reduce the number of queries sent to the server

    Partial Cache :
        Lookup cache starts off empty at the beginning of the data flow. When a new row comes in, the lookup transform checks its cache for the matching values. If no match is found, it queries the database. If the match is found at the database, the values are cached so they can be used the next time a matching row comes in.

        lookup operations would be slower, as you will most likely be hitting the database more often

    When to use this cache mode

        When you're processing a small number of rows and it's not worth the time to charge the full cache
        When you have a large reference table
        When your data flow is adding new rows to your reference table
        When you want to limit the size of your reference table by modifying query with parameters from the data flow

    http://blogs.msdn.com/b/mattm/archive/2008/10/18/lookup-cache-modes.aspx

    Thanks

        

    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem. http://techequation.com

    Friday, February 20, 2015 9:29 AM