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:

  • Partial:

    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.

  • Full:

    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.

Cache examples

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):

SELECT * FROM client_with_bills
WHERE client_id = 'C077'

CODE

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:

    1. SELECT * FROM client_with_bills WHERE CLIENT_ID = "C066" will query the data source again.
    2. SELECT * FROM client_with_bills WHERE CLIENT_ID = "C077" AND CODE = "02" will query the cache.
    3. 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.

    In Full mode, you have to explicitly load the cache.(this action can be scheduled)
    SELECT * FROM <view> CONTEXT('cache_preload'='true')

    TIP

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!

Thank you!