Performance Tuning Guide

Back Next

Data Object Cache Optimization

Cache performance depends on the performance of the cache database and the configuration of objects within mappings

, SQL data services, and web services

Consider the following solutions to increase cache performance:

Optimize the cache database.: Optimal performance for the cache depends on the speed and performance of the cache database and the cache size. Configure the cache size within the cache database.; Because the Data Object Cache Manager must maintain the old cache for a refresh operation, the cache must be large enough to store two sets of data. Use the following formula to estimate the required minimum cache size:
2 * average data object size * number of data objects; For example, you want to cache 20 logical data objects and 10 virtual tables. If your average object size is 15 MB, then the required cache size is 2 * 15 MB * (20 + 10) = 900 MB.; Cache tables are read-only. End users cannot update the cache tables with SQL commands.

Define primary keys and foreign keys for logical data objects.: When the Data Integration Service generates cache for logical data objects with keys, it creates indexes. The indexes can increase the performance of queries on the cache database.
Cache logical data objects that you join in a mapping.: When you join cached logical data objects, the Data Integration Service can push down the Joiner transformation logic to the cache database even when the source data originates from different databases.

Generate index cache based on columns in a logical data object or virtual table.: Configure the Data Integration Service to generate an index cache based on columns in logical data objects or virtual tables. The index can increase the performance of queries on the cache database.