Lookup - Preload vs Demand load vs No cache

[Former Member]

Hey ,

When to go for which cache ?. what internally happens ?.    Which gives more performace in which condition ?.

I have read the  Wiki , but i am not clear.  Please help me to understand.

[Former Member]

Caching lookup sources improves performance because the software avoids the expensive task of creating a database query or full file scan on each row.

NO_CACHE - Does not cache any values.

Cache Type	Description / Usage
NO_CACHE	Does not cache / store any values in DS memory
PRE_LOAD_CACHE	Preloads the result and compare column into memory before the lookup executes. This can be advantageous if you are comparing small amounts of data.
DEMAND_LOAD_CACHE	Loads the result column and compare column into memory as the function executes. Use this option when looking up highly repetitive values that are a small subset of the data and when missing values are unlikely. Demand-load caching of lookup values is helpful when the lookup result is the same value multiple times. Each time the software cannot find the value in the cache, it must make a new request to the database for that value. Even if the value is invalid, the software has no way of knowing if it is missing or just has not been cached yet. When there are many values and some values might be missing, demand-load caching is significantly less efficient than caching the entire source.

[Former Member]

Demand Load Cache - It is used when you have a large lookup table and need select sizable records out of it. For example - lookup over half a million records for an equally large input data.

Performance wise this could be slow as it starts with no cache. But when you have such large datasets, it is advisable to use join instead of lookup.

Preload cache - When your lookup data is comparatively lesser than the input source then you need to read the input into cache before loading. For example, say if you have to read 10000 records from a million data.

No Cache - not much memory usage as no of records being looked in the lookup table is less. For example, if you are looking only 100 records then you do not need any cache. Performance wise faster than the earlier two.

Hope this helps.

Arun

[Former Member]

Regarding the comment to use a join for large datasets, this needs more explanation.  A join can return multiple matching records, whereas a lookup will only return 1.  Joining tables from the same datastore might allow for pushdown of the join logic to the RDBMS, although if the tables are from different sources, the join might not give any performance benefits.

In Data Services 4.1 lookup logic can also be pushed down to the source.

Michael

[Former Member]

Hello Michael,

Can you please elaborate how we can push down the lookup logic to database? we have data services 4.2 and currently facing lot of issues related to performance.

[Former Member]

if you want to push down lookup to database then you can add join in stead of lookup & after applying the join please check the optimized query generated by BODS.

Check below link :

https://scn.sap.com/thread/3718684

If the query contains Insert into statement that means the generated SQL is pushdown SQL which will improve your performance.

ALso if you are going to use lookup & after lookup in the same data flow if you are doing some other operations like group by or orderby or any other then just use data tranfer which in will push down your lookup result to database and then perform order by & group by operation on database.

If you want to know more about it then provide your BODS job snapshot where you are having performance issue , I can look for it & help you out.

Thanks,

Swapnil

[Former Member]

Hi,

I am not sure if you already had a look at this , but as good start, there is a section about this that can help you:   "6.1.4.1 Using a Lookup function in a query"  in this guide "Performance Optimization Guide" . Also see "lookup" in  the DS4.0 reference Guide .

http://help.sap.com/eim.

Nawfal