Agile performance - Cache Modes
In the previous section, you activated the cache in one of your views. Now, it's time to learn more about how the Denodo Cache works. Denodo has the following cache modes:
the first time a query over the view is executed, the cache table will be populated with the tuples in the output from the datasource. At runtime, when a user queries the view, the Denodo server checks if the cache contains the data required to answer the query. If it does not have this data, Denodo will query the data source and populate the cache with that output.
When the Time To Live (TTL) of the data has passed, the cache system will invalidate the cached data of the view so the next query will hit the data source.
This mode supports the following options:
With explicit loads: if this option is selected, the cache has to be loaded explicitly.
Match exact queries only: if this option is selected, the cache stores the result of each query. Then, if the same query is executed, and the entries of this query in cache have not expired (TTL), the data returned to the client is retrieved from the cache.
The data of the view is always retrieved from the cache engine instead of from the source, this mode always requires explicit cache loads.
The main benefit of this mode over the partial cache is that complex operations (joins, unions, group by…) involving several views (even from different data sources) can be delegated to the cache database. Therefore, the performance of these operations is significantly improved.
Let's see how the cache works using the following example (make sure you have the tutorial database selected from the drop-down Database menu):
Run this query and check the Execution Trace with these different cache configurations:
- With the Cache Off
The data sources are always queried.
- With a Partial Cache
The data source will be queried the first time and subsequent queries will retrieve the data from the cache, if it is available.
To demonstrate, execute these queries:
SELECT * FROM client_with_bills WHERE CLIENT_ID = 'C066'will query the data source again.
SELECT * FROM client_with_bills WHERE CLIENT_ID = 'C077' AND CODE = '02'will query the cache.
SELECT * FROM client_with_bills WHERE CLIENT_ID = 'C066'queries the cache now.
- Partial Cache with Explicit loads
Denodo server will query the data source until the cache is told to be loaded with
CONTEXT('cache_preload' = 'true').
SELECT * FROM client_with_bills WHERE CLIENT_ID = 'C077'will query the data source.
SELECT * FROM client_with_bills WHERE CLIENT_ID = 'C077' CONTEXT('cache_preload' = 'true')will query the data source and load the cache.
SELECT * FROM client_with_bills WHERE CLIENT_ID = 'C077'will now query the cache.
- Partial cache with Match exact queries only
SELECT * FROM client_with_bills WHERE CLIENT_ID = 'C077'will query the cache.
SELECT * FROM client_with_bills WHERE CLIENT_ID = 'C077' AND CODE = '02'will query the data source and load the cache for this query.
SELECT * FROM client_with_bills WHERE CLIENT_ID = 'C077' AND CODE = '02'will query the cache now.
- Full Cache
All of the view's underlying data will be loaded into the cache, and all queries will only go to the cache.
Congratulations! You have completed the Denodo Basics Tutorial.
This is just the first step in understanding the Denodo Data Virtualization software. Now, you are prepared to go even further with the rest of our tutorials and become a Master Data Ninja!