Improving Dashboard Performance by Implementing Sorting Logic in Stored Procedures

This article explains why implementing sorting logic in stored procedures instead of Bold BI improves dashboard performance and provides recommendations based on internal analysis.

Purpose

To reduce performance delays caused by multiple query executions during sorting and filtering in Bold BI dashboards and widgets.

Problem

When sorting is applied within Bold BI using stored procedures, the dashboard executes multiple open queries for different sorting and filtering scenarios.
This happens because:

Bold BI passes an open query during query formation instead of using a fixed table name.
Each sorting/filtering action triggers additional query executions, increasing load time.
This approach prevents effective optimization at the database level.

Impact:

Increased dashboard load time.
Higher resource consumption on the database server.
Poor user experience due to delayed responsiveness.

Root Causes

Dynamic Query Formation in Bold BI
Sorting logic applied at the widget level forces Bold BI to regenerate queries dynamically.
Multiple Query Executions
For every sort/filter action, Bold BI sends separate queries instead of reusing optimized results.

Lack of Database-Level Optimization
Since sorting is not handled in the stored procedure, indexes and query plans cannot be fully leveraged.

Solution

Implement the sorting logic within the stored procedure rather than in Bold BI.

This approach:

Minimizes performance delays by reducing redundant query executions.
Improves dashboard responsiveness by leveraging SQL Server’s optimized sorting.
Reduces network overhead between Bold BI and the database.

Note: Recommended to always implement sorting logic inside the stored procedure for optimal performance.

Samples

Custom Field Sorting in Bold BI

When sorting is applied in Bold BI widgets (e.g., Grid Widget), the dashboard sends multiple queries dynamically.

Below is an example of how Bold BI applies custom sorting:

CREATE PROCEDURE dbo.sp_GetSalesOrders_OpenQuery
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM OPENQUERY(
        [RemoteSqlSrv],
		'SELECT TOP 1 OrderID, OrderDate, CustomerName, Country, Amount, Status
		FROM SalesDB.dbo.SalesOrders'
    );
END
GO

Grid Widget:

Query Matrix timing:

Performance Issue:

Each sort/filter action triggers a new query.
Stored procedure execution is repeated unnecessarily.
Dashboard load time increases significantly.

Add Sorting Logic to the Stored Procedure

Modify the stored procedure to include sorting logic:

ALTER PROCEDURE dbo.sp_GetSalesOrders_OpenQuerywithSorting
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM OPENQUERY (
        [RemoteSqlSrv],
		'SELECT TOP 1 OrderID, OrderDate, CustomerName, Country, Amount, Status
		FROM SalesDB.dbo.SalesOrders ORDER BY Country DESC'
    );
END
GO

Grid Widget:

Query matrix timing:

Advantages:

Sorting happens at the database level.
Bold BI only calls the stored procedure once.
Query execution plan is optimized.
Dashboard performance improves significantly.

Additional References

Was this article useful?

Dislike

Written by Jeevanandham Venu

Updated:

Comments (0)

Improving Dashboard Performance by Implementing Sorting Logic in Stored Procedures

Purpose

Problem

Solution

Samples

Custom Field Sorting in Bold BI

Add Sorting Logic to the Stored Procedure

Grid Widget:

Query matrix timing:

Advantages:

Additional References

Access denied