Improving Query Performance in Bold BI through Indexing and Views
Introduction
Optimizing query performance in Bold BI is essential for efficient data analysis and a seamless user experience. When dealing with large datasets, it is crucial to ensure that query execution time aligns with that of the underlying SQL server. However, if the execution time exceeds a certain threshold, it may lead to timeout errors in dashboards. In this article, we will explore effective techniques to enhance query performance in Bold BI by leveraging indexing and materialized views with Microsoft SQL Server’s database.
Techniques for Improved Performance
Indexing
A SQL index is a set of lookup tables designed to help users find items they require regularly. By converting the rowstore to a columnstore, you can gain high performance for analytic queries that require scanning large amounts of data. Starting with Microsoft SQL Server 2016, you can combine rowstore indices and columnstore indices on the same table.
Aggregated Tables and Views as Data Marts
Aggregated tables or views as data marts in SQL are database tables that contain aggregated values by combining multiple rows of data. They are used to perform dynamic calculations and reduce the input/output, CPU, RAM, and swapping requirements. Also, they increase the SQL query performance with large data sets.
Examples of Indexing and Aggregation
To provide a better understanding of the benefits of indexing and aggregation, consider the following examples:
Indexing Example:
Let’s say you have a table named “Sales” with millions of rows. By creating appropriate indexes on columns frequently used in filtering, sorting, or joining operations (such as ProductID, CustomerID, or OrderDate), you can significantly speed up queries that involve these columns. The SQL server can utilize the indexes to quickly locate and retrieve the relevant data, resulting in improved query performance.
Aggregation Example:
Suppose you have a table named SalesData with individual sales transactions. Instead of querying the raw transactional data every time you need to calculate the total sales for a specific date range or region, you can create an aggregated table or view named SalesByDate or SalesByRegion. These aggregated tables or views would contain precomputed totals or summaries of sales data based on date or region, enabling faster and more efficient queries for such calculations.
Performance Improvement Results
After applying indexing and creating aggregated tables or views, you can see a monumental improvement in SQL query performance. The execution time for sample queries is significantly reduced, making the dashboard load faster and more efficiently.
| Query | Original table | Indexing table | Aggregated table | Aggregated view |
|---|---|---|---|---|
| SELECT COUNT(Channel_Id) FROM marketingAggregated | 6 Seconds | 1 second | <1 Second | 3 Seconds |
| SELECT TOP 10 [Date], Sum(Cast([Expense] AS DECIMAL)) FROM marketingAggregated GROUP BY [Date] ORDER BY [Date] | 8 Seconds | <1 Second | <1 Second | 3 Seconds |
| SELECT Sum(Cast([Expense] AS DECIMAL)) AS NEWCOLUMN FROM marketingAggregated | 9 Seconds | <1 Second | <1 Second | 3 Seconds |
Conclusion
Improving query performance in Bold BI, especially for Live connections, is crucial for handling large data sets and ensuring a smooth experience. By employing techniques such as indexing and creating aggregated tables or views, you can significantly enhance the performance of your dashboards and connected databases. These techniques enable faster query execution and reduce the chances of timeout errors, providing a seamless user experience for data analysis in Bold BI.
Additional References
