Performance Troubleshooting
- 12 minutes to read
This article describes how to detect and solve common performance issues that may occur in the Desktop and Web Dashboard controls.
Web Dashboard
Detect Where the Problem Occurs
You can use the browser’s Developer Tools to check where the problem occurs: on the client or server side.
Open DevTools and switch to the Network tab. The DevTools window displays several data requests for the opened dashboard. All dashboard requests have the fetch type. You can check how much time these requests take to complete.
In the example below, all data requests are performed within three seconds:
The issue is caused by the client-side scripts if data requests are executed quickly in your project, but the dashboard requires additional time to display data.
The issue occurs on the server-side if requests take excessive time to load data. Set the
ItemDataRequestModeproperty toBatchRequeststo increase the data processing speed.
If these techniques do not suit your needs, refer to the following section: Server-Side Issues.
Client-Side Issues
Client-Side Scripts Work Slowly
If you detected that the performance issue is caused by the client-side scripts, check the amount of data displayed by different dashboard items. It may take time to render a large amount of data in JavaScript widgets. You can reduce the amount of visible data to accelerate visual element rendering in widgets. For this, set the dashboard control’s LimitVisibleDataMode property to DesignerAndViewer. You can also use Master Filtering, Drill-Down, Top N, and other interactivity features to limit the amount of data displayed by dashboard items.
Excessive Time is Required to Request Data in Client Data Processing Mode
Dashboard controls save data requested from a database to the in-memory data cache and load data from the cache for further processing. The Dashboard control uses the same shared cache for all users that work with a specific data source. The in-memory cache is created when data is loaded for the first time. The Web Dashboard can create several caches to accelerate data loading for different dashboards/data sources. Refer to the following topic for more information: Manage an In-Memory Data Cache.
Another possible solution is to use DashboardExtractDataSource, which allows the Dashboard control to save data from a database to a file. You can load data from the file when needed and process this data in memory in client mode. Refer to the following topic for information on how to create a data extract: Extract Data Source in ASP.NET Web Forms.
Measure the Time Required to Load a Dashboard
To check how much time a data load operation takes on the client side, use the JavaScript-based console.time / console.timeEnd methods within the onDashboardBeginUpdate / onDashboardEndUpdate event handlers, as shown below.
function onBeforeRender(args) {
...
dashboardControl.on("dashboardBeginUpdate", e => {
console.time("Client side dashboard loading time");
});
dashboardControl.on("dashboardEndUpdate", e => {
console.timeEnd("Client side dashboard loading time");
});
}
Server-Side Issues
The Server-Side Dashboard Code Works Slowly
You can use the Microsoft PerfView tool to profile the code used to process requested data in Dashboard controls.
Many Requests are Sent to the Server
Dashboard can send additional requests to the server to load data and build a color scheme for dashboard items. You can use the following techniques to reduce the loading and rendering time:
Configure a local color scheme.
A Global Color Scheme uses consistent colors for identical values across the dashboard. The Dashboard control loads all data to build the color scheme. Set the “Color Scheme Type” option to
Localfor all items in your dashboard to request colors for the current item only.
Use the Tab Container.
You can add a Tab Container with multiple tabs to the dashboard and place items with heavy content on invisible tabs. Set the
ItemDataLoadingModeproperty to theOnDemandvalue to load data for visible items only. For example, if you have more than one tab page, data is loaded only for the active page until you switch to another tab.Filter data.
Filter loaded data at the underlying data source level (see Filter Data Sources and Filter Queries) or limit displayed data at the item level (see Filtering). You can filter data dynamically based on specific conditions. For details, review the following code examples that use dynamic filtering:
Queries Work Slowly in Server Data Processing Mode
You can reconfigure your database if query execution takes excessive time.
If your database is not optimized for usage in server data processing mode, you can enable client mode instead. For this, switch the DataProcessingMode property to Client or handle the DashboardLoading event to create a custom storage and override the IDashboardStorage.LoadDashboard(String) method.
public class CustomFileStorage : DashboardFileStorage {
public CustomFileStorage(string workingDirectory)
: base(workingDirectory) { }
protected override System.Xml.Linq.XDocument LoadDashboard(string dashboardID) {
if (dashboardID == "dashboard1") {
var document = base.LoadDashboard(dashboardID);
Dashboard dashboard = new Dashboard();
dashboard.LoadFromXDocument(document);
dashboard.DataSources.OfType<DashboardSqlDataSource>().ToList().ForEach(dataSource => {
dataSource.DataProcessingMode = DataProcessingMode.Client;
});
return dashboard.SaveToXDocument();
}
return base.LoadDashboard(dashboardID);
}
}
Measure the Time Required to Load a Dashboard
To check how long it takes to create a dashboard on the server side, use the .NET-based Stopwatch class within the custom dashboard storage’s IDashboardStorage.LoadDashboard method, as shown below.
public class CustomDashboardFileStorage : DashboardFileStorage {
public CustomDashboardFileStorage(string workingDirectory) : base(workingDirectory) { }
protected override XDocument LoadDashboard(string dashboardID) {
var timer = new Stopwatch();
timer.Start();
var doc = base.LoadDashboard(dashboardID);
timer.Stop();
Debug.WriteLine("Dashboard loading time on the server (ms): " + timer.ElapsedMilliseconds);
return doc;
}
}
Data Loading Issues
If data requests take excessive time to handle, find out what causes the delay on the server side. The solution depends on the data source type you use in your app.
SQL Data Source Works Slowly
If the Dashboard control executes SQL queries to retrieve data from a database, use the SQL Server Profiler to see how much time it takes to execute each query.
Review generated queries to determine whether server or client data processing mode is used.
If client mode is used, the Dashboard control requests target data as is:
SELECT Dimension1, Dimension2, .., DimensionN, Measure1, Measure2, .., MeasureN FROM TableName
If server mode is used, the Dashboard control requests aggregated data with calculated summary values:
SELECT Dimension1, Dimension2, .., DimensionN, Sum(Measure1), Sum(Measure2), .., Sum(MeasureN)
FROM TableName
GROUP BY Dimension1, Dimension2, .., DimensionN
Server data processing mode is the default mode. Client mode is used if server mode is not supported or if the database cannot execute specific queries.
If you use a database server that does not support the SQL Server Profiler tool, you can use the XPO Profiler. For this, define and register the following XpoDebugLogger class in your application:
// Definition:
public class XpoDebugLogger : DevExpress.Xpo.Logger.ILogger {
public XpoDebugLogger() {
Enabled = true;
}
public int Count { get { return 0; } }
public int LostMessageCount { get { return 0; } }
public bool IsServerActive { get { return true; } }
public bool Enabled { get; set; }
public int Capacity { get { return 0; } }
public void ClearLog() { }
public void Log(DevExpress.Xpo.Logger.LogMessage message) {
if (Enabled) {
// Place your code here to log the message to System.Diagnostics.Debug, a file, or your custom logging engine.
System.Diagnostics.Debug.WriteLine(message.ToString());
}
}
public void Log(DevExpress.Xpo.Logger.LogMessage[] messages) {
foreach (var msg in messages) {
Log(msg);
}
}
}
...
// Registration:
DevExpress.Xpo.Logger.LogManager.SetTransport(new XpoDebugLogger());
The XPO Profiler helps you troubleshoot data cache misconfigurations and optimize your app to avoid excessive database calls.
Refer to the following article for more information on how to intercept and log all SQL queries (including stored procedures and their parameters): How to Log the SQL Queries.
OLAP Data Source Works Slowly
Use the SQL Server Profiler to determine why the OLAP data source works slowly. If a specific MDX query works slowly, find and inspect the dashboard item whose data this query requests. If it takes time to retrieve specific data, try to request the same data in Microsoft Excel or SQL Server Management Studio. If the same problem occurs, modify the cube to resolve the issue. If the issue occurs only in our dashboards, contact the DevExpress Support Center and share details.
Object Data Source Works Slowly
Inspect how long the DataLoading event is executed and how often this event is raised. The Dashboard control saves data from the data source to the in-memory data cache and loads data from the cache when needed. The Dashboard control uses the same shared cache for all users that work with a specific data source. The in-memory cache is created when data is loaded for the first time. It is necessary to request data only on the first load and refresh the cache when the reloading timeout (the default setting is 5 minutes) expires. If you see that data is loaded more frequently, review the following help topic to see what might cause this behavior: Manage an In-Memory Data Cache.
WinForms and WPF Dashboards
This section describes how to improve performance of the WinForms and WPF Dashboard controls. The examples below use the BI Dashboard Diagnostic Tool to inspect the dashboard performance.
Tip
The following database is used in the examples below: Contoso. The results of the performance tests listed below are obtained under the following conditions: Contoso is restored on the SQL Server 2019 installed on the same machine. Note that results on your system may vary from the results listed below.
The examples below show how to inspect the data load time in a dashboard. The Dashboard control requests small (less than 100,000 rows), medium (less than 1 million rows), and large (more than 3 million rows) amounts of data from the database in server and client modes.
Database Returns a Small Amount of Data but It Takes Excessive Time to Aggregate Data on the Server
To illustrate this case, we execute the following query in the Dashboard control:
select "V_ProductForecast".*
from "dbo"."V_ProductForecast"
"V_ProductForecast"
The resulting dashboard appears as follows:
The V_ProductForecast view returns 288 rows, and the Dashboard control has the following benchmark results when it loads a dashboard with this data:
Client Mode:
Server Mode:
The total load time in client mode is 6.871 sec. The Dashboard control takes the most time to load data from the database into memory in client mode. Further requests (used to load data for dashboard items or build the colorization model) are executed faster than in server mode because the Dashboard control already contains all required data in memory.
The total load time in server mode is 29.164 sec. In server mode, the control requests data from the database each time you perform any data-aware operation. Each dashboard item executes a separate query to obtain its data and color model from the database. These query executions can take more time than loading data from memory.
The images below illustrate benchmarks for methods used to populate dashboard items with data.
Client Mode:
Server Mode:
Client mode has better performance in this case. Further data requests in client mode (for instance, drill down or master filtering) will be faster because the control already contains all required data.
SQL Data Source Contains a Medium Amount of Data
To illustrate this case, we retrieve data from the FactSales table joined with several dimension tables in the ContosoRetailDW database. This table contains 884,823 rows and 80 columns.
The resulting dashboard appears as follows:
Below are the benchmarks for the dashboard load operation.
Client mode:
The dashboard load time is 24.348 seconds.
Server mode:
The dashboard load time is 4.526 seconds.
In this case, the data load operation takes more time in client mode, but data-aware operations are executed more quickly.
Dashboard is Bound to a Large Database and Displays a Small Filtered Portion of Detailed Data
To illustrate this case, we retrieve data from the FactSales table joined with several dimension tables in the ContosoRetailDW database. This table contains 3.41 million rows and 80 columns.
Server Mode:
The dashboard load time is 6.793 seconds.
Client Mode:
The dashboard load time is 81.656 seconds.
The following image illustrates the dashboard with filtered data:
Master filtering takes 0.7 seconds in server mode and 7.948 seconds in client mode.
Server mode operates faster because in client mode the Dashboard control loads all data in memory and it takes about 70 seconds. In server mode, the control requests only filtered data.
You can also create DashboardExtractDataSource to reduce the dashboard load time. The data extract has the following benefits:
- The initial data load operation requires less time, since the data extract file already contains all requested data.
- The Extract Data Source does not require a connection to a database. You can deploy the data extract on a client machine that does not have Internet/network connections.
It takes 8.661 seconds to load a dashboard connected to the Extract Data Source with the same data. Master filtering takes 0.7 seconds.