How to speed up a dashboard?

How to optimize dashboard loading speed?

A well-made dashboard is not only about useful charts and tables. A good dashboard should load fast, and certainly should not fail with an error because of a long wait for a response from the database.

So what should you do if the dashboard still does not work perfectly? How do you fix the situation?

Optimize your SQL queries

In SQL, the same task can be solved in different ways. If a query runs slowly, that is a reason to try doing it differently and to pick the fastest option.

Drop the views (not the circus kind)

Dashboards are often connected to pre-built views or use a SQL query directly inside the dashboard. In that case, all the calculations are performed on the fly when the dashboard is accessed. Switching to materialized views and tables will give a substantial speedup. In such tables, all the metrics you need are pre-computed and laid out in columns.

Add indexes and partitions

Indexes and partitioning in tables organize how the data is stored. This significantly speeds up searches across the table. It is important to be reasonable about using indexes and to add them only on frequently used columns of a table. The same applies to partitioning.

Prepare data marts

Data in a database can be loosely divided into “raw” and “aggregated”. Raw data is unprocessed data that has been loaded into the database from an external source. Such tables usually contain a great deal of data (millions or billions of rows). Because of this, a dashboard connected to a table with raw data will load noticeably slowly. It is better to use pre-built tables with aggregated data (also known as data marts). The users of your dashboards will thank you for it )

Move to an analytical database

If a dashboard is connected to a transactional database (PostgreSQL, MySQL, etc.) or to Google Sheets, then moving to an analytical database (for example, ClickHouse or Google BigQuery) will give a substantial boost to the dashboard’s speed.

Increase database and server resources

Sometimes everything that can be sped up in the database has already been sped up - the data marts are built, the queries are optimized, the indexes are in place, but the dashboard still fails with an error. In that case it is worth paying attention to the server limits and the database settings. There may not be enough RAM, disk space, or the number of available connections to the database.

Reduce the filtering period

Also pay attention to the filtering inside the dashboard itself. Do you have a filter (selector) for the period? How wide is the range selected there? Shorten it to the minimum necessary, and the users will then pick the period they need themselves. This will reduce the load on the database, improve the dashboard’s loading speed, and in the case of Google BigQuery it will also reduce costs.

Simplify the dashboard

The number of visualizations and the number of metrics in them can also significantly slow down loading. Consider splitting a dashboard tab into several separate ones. Suggest to the users that they split the many metrics in a shared table into different ones (perhaps some of them are no longer relevant at all). Pivot tables often take the longest to load - discuss with the users switching to a flat table instead of a pivot one. All of this will also help speed up the dashboard.

I have extensive experience optimizing dashboards and the costs of an analytical warehouse. Get in touch! I will be glad to help you.

Subscribe to my Telegram channel https://t.me/online_data_analytics - I promise plenty of useful tips on dashboards, ETL, and DWH every week.