4. Caching, local cells and scheduling

Now you've started building some queries, let's consider how we can optimise your canvas performance (before you go too far!)

Using a combination of caching, local cells and scheduled queries you'll be able to explore your data more quickly - you're not waiting for your database to execute a query every time a cell changes, instead it all happens really fast in your browser.

Caching

Caching your results means that your query results are stored in the canvas. This means when your canvas is refreshed, or someone else joins your canvas, the queries aren't run against your database again, and so will load immediately (showing the results from when they last ran against the database). When you query the results of your cached data using local cells (DuckDB or Python), these queries execute much more rapidly because they're running locally in your browser.

How to set up/update caching in your canvas

1. Click on the play icon in the top right corner of your canvas

2. Select the option "Caching settings..."

3. Choose how long you want your cached results to be saved for

   The most performant option is typically 'Cache results indefinitely' which will only refresh a query when prompted or when an upstream cell is also updated.

Local cells

Local cells (DuckDB or Python) run in-memory in your browser. This means you can query cached database results immediately, and you don't have to wait for your query to be returned from your database.

It's important to be aware that there is a limit on the amount of data that a cell can return (approximately 120MB), so it's best to get your results down to a reasonable size before switching to DuckDB.

If you're unable to reduce the size of your data, you can still make use of local cells to first prototype locally then revert back to live data on your database for the full results.

How to use local cells alongside caching to improve performance

1. Create your base cell (the cell that is returning the data from your database).

2. Create a second cell.

3. Change the data source to local (using the right hand design bar)

   These results should be almost instantaneous because the local cell is querying the results from the database cell its referencing, rather than your database directly.

Scheduling

With scheduling you set your canvas to automatically run at a specific interval. For example, you may want to set your queries to refresh every morning at 7AM before other canvas viewers typically open your canvas. If the rest of your canvas is set up to heavily make use of local cells, then when users come in later in the day they'll not be querying your database, meaning canvases run fast for the user, and the database load (and potentially costs) are lower.

💡 Learn more about caching and scheduling in our guides here.

FAQ

How do I update a cell if caching is enabled?

If you ever need to refresh your results from your database, you can click on any cell, select the play button just above, and select Run cell.

What causes a cell to re-execute when caching is enabled?

When a cell input is edited, Count will re-compile the SQL query and compare it to the previous one. If the SQL has changed, then a new query will be executed.

Changes that consist only of whitespace alterations will not cause a new query to be executed.

Can I use caching and local cells by default?

Yes! For caching, you can set this in your workspace settings.

For DuckDB - you can set by default at a connection setting level to Use DuckDB by default for visuals.

Where is cached data stored?

Cached data is stored in-memory on our servers, and sometimes also saved to disk for longer caching periods. Stale cached data is periodically deleted. Read more about data security here.