Enabling reference data caching

Controlling the size of the cache

When a transaction issues a SELECT against a cacheable table, the cache manager saves the retrieved records as well as the SELECT WHERE clause. The WHERE-clause is used as a hash key to quickly determine the existence of cached records.

The cache manager stores four distinct components:

• The cached record (which the cache manager calls "OBJECT")
• The query WHERE clauses that returned one or zero database record (which the cache manager refers to as "SELECT")
• The query WHERE clauses that returned zero or more database records (which the cache manager calls "LIST") and
• The results of COUNT queries and their WHERE clause (which the cache manager calls "COUNT").
  Note: In case you are wondering, the use of the terms "SELECT", "LIST" and "COUNT" is historical and refers to the fact that the WHERE clauses were used by the selectWithWhere(), listWithWhere and the countWithWhere() database methods. Knowing these terms will help you set the cache limits later in this document.

Take for example the following queries against the cacheable YFS_ORGANIZATION table. The first query (using the selectWithWhere() method) returns one record (call this record ORG-3):

   select *
   from yfs_organization
   where organization_code = 'ORG-3'
   

At the end of the query, the cache manager stores the ORG-3 record into a Java™ Map which the cache manager refers to as OBJECT. Next, the cache manager stores the WHERE clause ("where organization_code = 'ORG-3'") in the SELECT Java Map (see diagram below). The SELECT Map associates the WHERE clause (which is a hash key) to the cached record.

The second query (using the listWithWhere() method) to the same table returns six records (ORG-1 and ORG-6):

   select *
   from yfs_organization
   where catalog_organization_code = 'ACME' 
   

At the end of that query, the cache manager stores the second WHERE clause into the LIST Java Map and the ORG-1, ORG-2, ORG-4, ORG-5 and ORG-6 records into OBJECT along with a structure that associates the six cached record to the WHERE clause. The cache manager does not add ORG-3 because it was already added to the OBJECT Map from the first query.

Finally, the WHERE clause and the count results from the third query (using the countWithWhere() method) is stored in the COUNT Map:

   select count(*) 
   from yfs_organization
   where catalog_organization_code = 'ACME'
   

The following diagram depicts a simplified version of the cache structure:

By default, the cache manager uses the following parameters from the dbclassCache.properties file to control how many COUNT results, SELECT WHERE clause, LIST WHERE clauses and OBJECT (cached records) can be stored for each table:

   sci.globalcache.count.size=10000
   sci.globalcache.select.size=10000
   sci.globalcache.list.size=10000
   sci.globalcache.object.size=10000
   

As a result, a cacheable table can at most store in the JVM:

• Up to 10,000 cached records (in the OBJECT Map)
• Up to 10,000 SELECT WHERE clauses
• Up to 10,000 LIST WHERE clauses and
• Up to 10,000 COUNT results and their WHERE clauses.

You can use the customer_overrides.properties file to override the settings. For example, you can increase the cache limit for the number of YFS_RESOURCE records to 30,000 with the following parameter:

   YFS_RESOURCE.objects=30000

The "Objects Cached" column in the System Management Console > Table Level Cache List provides the number of records cached for each table.

Please keep the following in mind if you change the default settings:

• Each cached record occupies space in the JVM heap. If you increase the number of records cached, you must ensure garbage collections are still effective and "healthy".
• Conversely, do not set the cache limit too low such that the Sterling Selling and Fulfillment Foundation cache has to continually flush the cached tables

The UI login process takes over 30 seconds if you set the cache limit for the YFS_RESOURCE and YFS_RESOURCE_PERMISSION tables too low (e.g., 1,000). These two tables have over 3 thousand records which are read as part of the UI login process. By setting a low cache limit (less than the number of records in these two tables), the Sterling Selling and Fulfillment Foundation cache must flush out earlier cache records when the cache fills up. As a result, the next login must read the records again.

To minimize the amount of cache management overhead, the caching mechanism implements a simple space management strategy - when the number of cached records for a table hits the limit specified above, the cache manager initializes (or refresh) that table's cache to being empty.