Category / Section
Optimizing Performance with MS SQL Stored Procedures in Bold BI
Published:
Overview
This article demonstrates how to use MS SQL stored procedures with Bold BI to create high-performance dashboards. Stored procedures are a powerful tool for processing large datasets efficiently at the database level. We tested a Sales Analysis Dashboard using a dataset containing 1 million records to analyze performance metrics such as rendering time and demonstrate scalability.
Dashboard Details
- Dashboard Name: Sales Analysis Dashboard - Bold BI
- Data Source: Stored Procedure
- Dataset Size: 1 million records
- Dashboard Image:
Dashboard Rendering Time
| Component | Rendering Time |
|---|---|
| Dashboard | 18.76 seconds |
Widget-Level Rendering Performance
| Component | Rendering Time in browser(seconds) | Data Fetching Time(seconds) |
|---|---|---|
| Total Orders By Country | 0.252 | 0.537 |
| Order Date | 0.182 | 0.876 |
| Years Orders vs Profit | 0.246 | 0.737 |
| Ship Date | 0.279 | 0.075 |
| Item Type | 0.318 | 0.274 |
| Country | 0.472 | 0.447 |
| Total Profit | 0.505 | 0.246 |
| Total Revenue | 0.533 | 0.291 |
| Total Units Sold | 0.568 | 0.279 |
| Top 5 Countries by Revenue | 0.665 | 0.044 |
| Region-wise Profit Share | 0.7898 | 0.393 |
| Product Revenue Contribution | 0.841 | 0.347 |
| Units Sold by Product Type | 0.987 | 0.440 |
| Total | 6.637 | 4.199 |
Key Insights
Stored Procedure Advantages:
- Efficient Data Processing: Handles large datasets directly in the database, reducing the need for post-processing.
- Reduced Server Load: Minimizes the load on the Bold BI server by decreasing data transfer overhead.
- Accurate Results: Encapsulates complex business logic within the database to ensure reliable outputs.
- Enhanced Security: Limits direct access to raw data and enforces data manipulation rules within the database.
Conclusion
Using MS SQL stored procedures in Bold BI ensures optimal performance and scalability for dashboards handling large datasets. The Sales Analysis Dashboard serves as a proof of concept, demonstrating that rendering times remain well within acceptable limits for enterprise applications.
References
44
