Here’s a quick summary of practices about when to use cache when developing denotative views. These guidelines come from the usual documentation and practical experience and may help you decide whether to cache a view. These are general guidelines, and they should happen the conflict with any guidance you’ve gotten from the Denodo; Please use the advice provided by Denodo.
What is a table cache?
In denodo, a cache is a database table that contains a result set of a view at the point in time, which is stored in a JDBC database
Why Cache?
Cache in Denodo can be used for several purposes:
Enhancing Performance
Improving performance is the primary purpose of caching and can be overcome slow data sources, data sources with limited SQL functionality, and/or tuned long runner views.
Protecting data sources from costly queries
Caching can shield essential systems from excess load cause by query load from large, long-running queries and/or frequent queries during critical operation times.
Reusing complex data combinations and transformations
Caching views that consolidate data from multiple data sources, perform complex calculations, and apply complex derivations and business rules provide and optimize pre-enriched data set for consumption.
Cache View Modeling Best Practice
Add a primary key or a unique index
Adding a primary key or a unique index helps the optimizer define performance strategies and accurate cost estimates when the view joins to other views.
Add Cache indexes
Add Cache indexes based on understanding actual consumer usage of view (e.g., commonly used prompts, etc.)
Caching Tips and Cautions
Here are some considerations to keep in mind when making caching decisions.
Avoid Caching Intermediate Views
Where possible, avoiding caching of intermediate views allows the optimizer to make better decisions about data movement, pushdown, and branching. This allows denodo to perform great SQL simplification.
The volume of view to be cached
Where possible, avoid caching large views (e.g., views with a large number of rows/columns). Evaluate the cache size and make an appropriate decision.
