Indexing Your Cache
TDV provides several options for you to index your cached data. Indexing your data can improve performance when certain columns are commonly used for filtering and viewing a subset of the data from the cache. Adding an index can slow the performance of cache refreshes, but can improve the performance of cache result retrieval.
Depending on what cache options you have chosen to implement, different indexing options are available to you:
• For a full refresh type of cache, you can specify scripts to run before and after a cache refresh. You could include logic in each of those scripts to drop and/or create indexes on your cached data.
• For a full refresh cache that uses the multi-table option, you can configure TDV to index your cached data automatically with each refresh. This selection requires that you provide indexing definitions on the Index tab for the table or view that you are caching.
• For pull-based incremental caching, you can add logic to the incremental caching scripts to define additional indexing behavior.
• For TDV caching options that don’t directly support indexing, you can create indexes directly on your cached data using the database tools available for your specific cache target. This solution might also require that you consider what processes might be necessary to ensure that the indexes are still valid after a cache refresh occurs.
Indexing-specific columns in the result set of a cached view yields performance benefits. TDV also provides the ability for you to define a script or procedure that can be used to initiate or update and index on your cached data.
You can filter data going to the cache table using the cachekey column. Because the cachekey column is a unique value for a given cache snapshot, when you write custom scripts to index the cache data, we recommend that you make the cachekey the first column of the index. This is especially true if you have many procedure cache variants and commonly used queries do not return a large subset of rows.
To index your cache if full-refresh multi-table caching is enabled
1. Refer to the instructions in:
2. Make sure that the Indexes tab has the index defined as you need.
To index your full refresh cache
1. Refer to the instructions in the following topic:
2. Add logic to manage and define the indexes.
3. Test your performance to ensure that performance goals are reached for cache indexes.
To index your pull-based incremental cache
1. Refer to the instructions in the following topic:
2. Add logic to manage and define the indexes.
3. Test your performance to ensure that performance goals are reached for cache indexes.
To index your cache data directly on the cache target
1. Refer to the instructions in the following topic:
2. Define the indexes using the database tools available for your cache target database type.
3. Determine what other scripts or process changes might be necessary to make sure that the index is still functional after a cache refresh.
4. Test your performance to ensure that performance goals are reached for cache indexes.