Best Practices for Optimizing Data Models and Dashboards for Server Performance
With a growing user base, it’s crucial to refine data models and dashboards for optimal server performance. This guide presents key strategies to enhance performance and user satisfaction.
Enable Data Caching for Dashboards
Incorporate data caching in dashboards to lessen the frequency of query requests to the server, thereby boosting performance and reducing server strain. Detailed steps for setting up Data Cache are available here.
Server Enhancement
- Upgrade server components like CPU, memory, and storage to support more concurrent users.
- Refine database server configurations—adjust memory use, parallel processes, and query expiration—to align with usage patterns and system potential.
- Continuously track and adjust server performance indicators to maintain optimal settings.
- The recommended system configuration for Docker environment is here.
- For Linux systems, you can refer to the suggested configuration here.
Database Refinement
Materialized Views or Data Marts
Employ materialized views or create summary tables (Data Marts) for data pre-computation, which can lead to faster query execution.
This document offers an in-depth look at the performance benefits of materialized views.
Materialized view performance in Google Big Query
Data Source Creation in Table Mode
- Utilize the design interface to easily select tables, apply filters, and execute simple computations to construct efficient queries.
- Minimize the use of unnecessary joins, nested queries, and intricate calculations.
Migrate Data to a Data Warehouse
- Transfer bulky datasets to a data warehouse solution such as Apache Doris using Bold ETL or other systems like Snowflake or SQL Data Warehouse.
Index Creation for Tables
- A SQL index is a set of lookup tables designed to help users find items they require regularly. An index is a data structure that is small, fast, and designed for speedy searches. It’s great for linking relational tables and searching large databases.
Partitioning the tables
- Table partitioning is a database process. Large tables are divided into smaller parts based on the values in one or more table columns. The table partitioning helps you maintain the large tables, reduce the overall response time, and read and load data for the SQL operations.
More details on index creation and partitioning can be found in below blogs.
How to Effectively Handle Millions of Rows in Bold BI
Using column store indexes for optimized data access
Dashboard Structuring
- Aim for straightforward and clear dashboard designs to minimize data processing and visualization.
- Restrict the number of widgets per dashboard to prevent server overload.
- Implement dynamic filters for user-driven data exploration that doesn’t demand intensive query execution.
By adopting these strategies, you can markedly boost your data models and dashboards’ performance, ensuring your server can cope with a growing number of users effectively.
References:
