Query caching
Hex improves performance, and reduces the load on your database, by using cached results of previous SQL queries when the same query has been run recently.
- Users will need Can Edit permissions.
Using cached query results
Any time a SQL query is run against a data warehouse in a Hex project, Hex checks if the same query has been run recently. If it has, Hex pulls results from its cache, rather than sending the query to your warehouse.
At any time, users can override the cached results. By default, results from the cache will be used if the same query has been run within the last 60 minutes. Admins can adjust this default for their workspace, while editors can further configure their cache settings per project.
In Notebook view
In Notebook view, when the results of a SQL query uses cached results, it will be indicated on the cell like so:
You can force the database query to be re-executed against the database via the Run without cached results button.
You can also force all cells that run SQL to re-execute the query against the database via the Run all without cached results button when rerunning a project.
In Published Apps
When running a published app, Hex may use cached SQL results for cells that execute SQL against a database. Users can adjust whether or not published apps pull from cache, as well as set the max age of cached results, in a project's run settings. When an app run uses cached SQL results, the results will either be marked as up to date or stale, depending on what's been configured in the run settings. Clicking on the freshness indicator will show the age of the cached results:
Note that when multiple cached SQL results are used in an app, this number represents the maximum age of the results.
An app can be rerun without cached results via the 3-dot menu in the upper right of the published app:
Adjusting cache settings
By default, Hex will use cached results in both logic sessions and published apps if the same query has been run within the last 60 minutes.
Admins can adjust this default for their workspace, with separate timeouts for developing logic and published apps. To adjust the default timeouts, visit Settings → Environment under the "Workspace" header, and adjust the settings for Workspace SQL caching.
Editors can further adjust the logic timeout per project via the Environments sidebar:
Editors can adjust the published app cache timeout at the project level in the app run settings, or the publish dialog. See more details in the app run settings doc.
Admins can also choose to turn off the use of cached results for the entire workspace; however, note that this will lead to more queries being run against your warehouse and increased compute time in projects.
Using scheduled runs to refresh the SQL cache
A scheduled run of a published app will always execute SQL queries against a database rather than using cached results.
Additionally, rather than set a fixed timeout on cached SQL results in a published app, you can choose to use the timestamp of the most recent scheduled run to determine if a cached result is still valid.
This option will only appear if a scheduled run is configured.
Note that if the same query is run elsewhere in Hex (e.g. an Editor runs this query in Notebook view, or the same query is run in another project), the published app will use the most recent results, even if they are newer than the results produced by the most recent scheduled run.
FAQs
Which cells do caching settings apply to?
Hex may use cached result for any cell that runs SQL against a database. This includes SQL cells that use a data connection (and excludes SQL cells using dataframe SQL), as well as pivot cells and filter cells using query results as an input.
Can I disable caching for my workspace?
Admins can choose to disable caching for their workspace from the Workspace SQL caching settings. Once disabled, Editors will not be able to enable caching for their project.
Disabling caching will lead to increased run times and database loads. If you are concerned about freshness of data, consider reducing your cache timeout rather than disabling caching.
Why are my query results not caching?
There are a few scenarios where you may expect your query results to be cached, but are still seeing queries run against your database instead of pulling from cache. If your query meets any of the following conditions, Hex is unable to cache the results:
- The query contains statements that are not select statements
- The resultset is larger than 8 GB
- The query is dependent on a variable whose value is changing (e.g., the query uses Jinja to reference the current timestamp)
If your query does not meet any of these conditions and is still failing to cache, reach out to [email protected] for assistance troubleshooting.
Does running a query without cached results update the results for everyone?
If you choose to run a query without cached results, the results of this query that are stored in the cache will be updated. This means that if another project uses the same query, the next time the cell is run, the more-recent results will be pulled from the cache.