In-memory Cache Vs Pageable Cache

[system]

Hi All,

Could anyone provide the info that when will we use In-memory Cache and Pageable Cache?

I am using SQL tranformation in a data flows which will bring huge data.

Which one is best to use in these two options. I am using 3.1.

Thanks in advance.

---

kiwi (BOB member since 2009-06-17)

[system]

This post should might with your question:

http://wiki.scn.sap.com/wiki/display/EIM/Caching+in+DI

Depending on the size of the data, an option might be checking the “collect statistics for monitoring” or “collect statistics for optimization” buttons at time of execution.

also briefly discussed here:

120×120 10.4 KB

Data Integrator provides the following types of caches that your data flow can use for all of the operations it contains: • In-memory Use in-memory cache when your data flow processes a small amount of data that fits in memory. • Pageable cache Use pageable cache when your data flow processes a very large amount of data that does not fit in memory. When memory-intensive operations (such as Group By and Order By) exceed available memory, Data Integrator uses pageable cache to complete the ope…

~ Search is your friend.

---

ww55d (BOB member since 2011-06-14)

[system]

@kiwi

I am using SQL tranformation in a data flows which will bring huge data.

The SQL transform shouldn’t cause huge memory issues. The SELECT statement within the transform is completely pushed down to the database and the result set is returned only 1,000 rows (by default) at a time. Now, what you do with the result set AFTER the transform is up to you and that’s where you can run into memory issues. If you route the data into a Query transform that performs a GROUP BY, ORDER BY or DISTINCT then the entire result set will have to be accumulated in memory.

---

eganjp (BOB member since 2007-09-12)