Tableau Performance Tips #6 : Avoid using NOW() for filtering or selecting against a fact datasource

Did you heard about database result cache ?

Some databases implement a result cache (or query cache depending the name), it is a cache for the results of some queries.

Oracle, Exasol, HANA implement a result/query cache.  MySQL & PostgreSQL had result cache but this feature had been removed… not maintener I suppose…Cloud databases like Snowflake, BigQuery, FireBolt , AWS Redshift do have result cache to boost repetitive queries.

Oracle result cache is for me the most advanced and the most accomplished. As Oracle can use Client or Server result cache, i must precise that i talk about server result cache here.

The (server) result cache size is limited to a portion of the database memory and results must meet requirements to be cached :

  • The size of the result must be under a threshold (number of rows or bytes)
  • The data under the result cache are quite stable
  • The query must not have special function like CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT (with non-constant variables), SYS_GUID, SYSDATE, and SYS_TIMESTAMP

These rules make the result cache particularly attractive for BI queries where data are often refresh once a day and queries are heavy to compute on high cardinality tables but results are small (aggregates).

The result cache is more efficient if you don’t have security on your data

To benefit from Result or Query Cache : stop using TODAY() or NOW() Tableau functions in your calculated fields that are based on your direct big database datasource.

As you already said in a previous tip the NOW() and TODAY() function can cause performance issue if they come from calculated field based on a « big » datasource.

To retrieve this kind of informations, you should create a dedicated datasource with only one row using a csv file or a custom SQL (like SELECT 1 as X FROM DUAL)

Single row mssql datasource using custom SQL
Now() from dual