Using Azure Data Explorer caching options
In this post I’ll try to convince you to start enabling result caching when connecting from Power BI to Kusto in Direct Query.
There are two flavors of result cache in Kusto:
- Caching the entire result of a query for a limited time.
- Caching the part of a query that was sent to a slice of the table known as a shard (aka extent)
The first option may return somewhat stale results based on the setting.
The second option will return the latest results and will still benefit from caching partial results.
PBI itself maintains a cache of results but this is maintained only for the current session.
Kusto cache will help in situations where many users are using a set of dashboards with some overlap between the users.
How to tell Kusto to enable caching
When you create a new Kusto query you can insert the settings in the bottom of the dialog, under Additional Set Statements.
The setting “set query_results_cache_max_age = 5m” will keep the results for 5 minutes and will be returned to anyone sending the same query (see here)
The other option is enabled by the setting “set query_results_cache_per_shard”.
The first setting enables also the second.
The rest stays the same, you can enter a database name or leave it empty.
You can enter a table name or query or navigate to a table or a function after clicking OK.
What are the cases in which you’ll benefit from caching?
Shard level caching is effective when the table is relatively big and will occupy many extents (shards)
You can see the number of extents by running
.show table tablename extents | count
Let’s assume at 12:00PM, PBI generates a query that retrieves the last 6 hours of data in the table.
The query will have a where clause like:
Timestamp > datetime(2022-02-06 6:00:00.00) and Timestamp < datetime(2022-02-06 12:00:00.00)
When this query runs, the cache is created on each shard that provides data to the query.
At 12:30PM the same query is sent again by the same user or another user.
Many of the shards accessed are the same one as for the first user and they can return the data from the cache.
The second query is not identical because it contains Timestamp < datetime(2022-02-06 12:30:00.00). Still the cache will be used, and the query will consume less CPU and scan less data.
The query result cache will be used if the query is 100% identical for the time specified.
An example can be when many users open the report and see the same starting page.
Which Cache option is the best for your case?
Result cache is a good option if the same queries are used.
If the queries are the same except the timestamp but have a significant overlap, then shard level is the best option.
If for example you use auto-refresh that sends a query that shows the activity in the last hour, shard level caching could reduce the used resources significantly.
You can look at the generated queries and compare the CPU and scanned rows with and without the cache option and see the difference.
What are the limitations of caching?
There is a maximum allocated space on disk for the cache. When the cache is full, the oldest pieces of cache will disappear in LRU fashion.
If you use query results, there is a tradeoff between freshness and performance.
If you return large amounts of rows in the result set, it will not be maintained in cache so not to allow one piece of cache to hog the entire allocated space.
Small table with a small number of extents will not benefit from shard level cache because the latest extents will probably maintain new data that came after the last refresh of the cache and will need to be read.