Optimizing PSP Cache

To maximize the cache hit rate (and hence, performance and scalability), partition the transform search specification into a high selectivity clause that is executed by the database and used as part of the PSP cache key (the Search Specification input argument) and a low selectivity clause that is executed by the PSP transform itself to further filter the query results (the In Memory Search Specification input argument). When you use the In Memory Search Specification input argument in combination with a Search Specification input argument, your search specification is, effectively, “[Search Specification] AND [In Memory Search Specification]”. The two search specification input arguments are divided by purpose as follows:

• Search Specification. Use this input argument to define highly selective search criteria executed by the database.

• In Memory Search Specification. Use this input argument to define low selectivity search criteria executed by the Siebel Server.

  Note: The Dynamic Look-Up transform does not support the In Memory Search Specification input argument. This transform dynamically generates its own search specification.

The order of search implementation is as follows: first the Search Specification input argument is applied to the database query. Next, the returned result set is further filtered in memory by applying the In Memory Search Specification input argument.

Example values for Search Specification and In Memory Search Specification are shown in the following table for the Pricer Simple Volume Discount step.

Input Argument	Value
Search Specification	[Volume Discount Id] = {Row.Volume Discount Id} AND [Volume Discount Method] = LookupValue('VOL_DISCNT_METHOD', 'SIMPLE')
In Memory Search Specification	[Minimum Quantity] <= {Row.Extended Quantity Requested} AND ([Maximum Quantity] >= {Row.Extended Quantity Requested} OR [Maximum Quantity] IS NULL) AND [Volume Discount Start Date] <= Timestamp() AND ([Volume Discount End Date] >= Timestamp() OR [Volume Discount End Date] IS NULL)

The example shown in the previous table results in one query for each volume discount that retrieves all result rows. All subsequent queries against that volume discount are served from the cache regardless of the values for [Extend Quantity Requested] or Timestamp().

Note: In Memory Search Specification execution does not use sophisticated database features such as indexes. Make sure the result set searched in memory is not too large. For example, loading an entire price list in one query is not likely to improve performance; search a subset of the price list.

For information about PSP Cache performance, see About PSP Cache Performance Statistics.