Technology – When To Cache A Denodo View

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.

Denodo Reference Links

Best Practices to Maximize Performance III: Caching

Denodo E-books

Denodo Cookbook: Query Optimization

Related Blog Posts

Denodo View Performance Best Practice