Report performance

Artikel
01/19/2024

Reports generally fall into two categories. They can be specific to a single instance of an entity, like an invoice. Or, they can be of a more analytical nature that joins data from multiple instances of multiple entities. Typically, performance issues in reports lie in the latter category. The following articles contain advice about implementing faster reports:

Use Read Scale-Out to read data from a read-only copy of the database, see Using Read Scale-Out for Better Performance for more information.
Use Partial Records to reduce the data loaded from the database, see Using Partial Records for more information.
Use AL queries to optimize the way data is read from the database, see Queries in Business Central for more information.
Compared to Word layouts, RDL layouts can result in slower performance with document reports, especially for actions related to the user interface (like sending emails). For more information, see Creating an RDL Layout Report.
Can you run the report in the background? It will not make it run faster, but the user might feel this way (as they are not blocked in the UI while waiting on the report to finish). For more information, see Schedule a report.

Tip

From the Business Central client, you can export report results as raw data to a Microsoft Excel file. The file contains all columns of the dataset, but without the layout applied. Use the file to help validate that the report returns the expected data, and to ensure that the report layout controls match the dataset value types. To export a report dataset to Excel, run the report and select the Send to > Microsoft Excel Document (data only) on the request page. For more information, see Working with Reports - Send to Excel.

Analyzing report performance telemetry

Report telemetry gathers data about which reports are run on the environment. It provides information about whether the report succeeded, failed, or was canceled. For each report, it tells you how long it ran, how many SQL statements it executed, and how many rows it consumed. You use this data to gather statistics on report usage or to help identify slow-running reports.

For more information about report telemetry, see Report telemetry

Sample KQL code

This KQL code can help you get started analyzing report rendering:

traces
| where timestamp > ago(60d) // adjust as needed
| where customDimensions has 'RT0006'
| where customDimensions.eventId == 'RT0006' 
// | where operation_Name == "Success report generation" // use this instead of eventId clause for versions 16.0 or earlier
| where customDimensions.result == "Success"
| project timestamp
// in which environment/company did it happen
, aadTenantId = customDimensions.aadTenantId
, environmentName = customDimensions.environmentName
, environmentType = customDimensions.environmentType
, companyName = customDimensions.companyName
// in which extension/app
, extensionId = customDimensions.extensionId
, extensionName = customDimensions.extensionName
, extensionVersion = customDimensions.extensionVersion
, extensionPublisher = customDimensions.extensionPublisher
// in which object
, alObjectId = customDimensions.alObjectId
, alObjectName = customDimensions.alObjectName
, alObjectType = customDimensions.alObjectType
// what did the user do
, documentFormat = customDimensions.documentFormat   // documentFormat dimension added in version 20.0
, LayoutAppId = customDimensions.layoutAppId         // layout dimensions added in version 20.0
, LayoutName = customDimensions.layoutName           // layout dimensions added in version 20.0
, LayoutType = customDimensions.layoutType           // layout dimensions added in version 20.0
, reportAction = customDimensions.reportAction       // reportAction dimension added in version 20.0
, reportingEngine = customDimensions.reportingEngine // reportingEngine dimension was added in version 17.3
// which user ran the report
, usertelemetryId = case(
  toint( substring(customDimensions.componentVersion,0,2)) >= 20, user_Id // user telemetry ID was introduced in the platform in version 20.0
, 'N/A'
)
// performance data
, numberOfRows = customDimensions.numberOfRows
// , serverExecutionTime = customDimensions.serverExecutionTime // the datatype for executionTime is timespan
, serverExecutionTimeInMS = toreal(totimespan(customDimensions.serverExecutionTime))/10000 // this shows how to convert timespan to milliseconds
, sqlDatabaseAccessIntent = customDimensions.sqlDatabaseAccessIntent  // sqlDatabaseAccessIntent dimension added in version 19.1
, sqlExecutes = customDimensions.sqlExecutes 
, sqlRowsRead = customDimensions.sqlRowsRead
// , totalTime = customDimensions.totalTime // the datatype for totalTime is timespan
, totalTimeInMS = toreal(totimespan(customDimensions.totalTime))/10000 // this shows how to convert timespan to milliseconds
| extend renderTimeInMS = totalTimeInMS - serverExecutionTimeInMS

Tuning potential

When looking at telemetry data for report performance, the following strategies might be helpful for identifying reports to optimize:

aggregate the sum of execution time by report and sort by sum descending. High sum values show the reports that consume a lot of resources.
aggregate the count of report executions by report and sort by count descending. High number of renderings show the reports that are run the most.
aggregate the average execution time by report and sort by average descending. Reports with high average execution time could be candidates for tuning.

Calculating time to compute the dataset and to render the report (apply the layout)

The time spent to run a report consists of two parts: generating the dataset and rendering the report (applying the layout). In report telemetry, you get two durations: serverExecutionTime and totalTime. The former is roughly the time it takes for the server to generate the dataset. To calculate the rendering time, simply subtract serverExecutionTime from totalTime: renderingTime = totalTime - serverExecutionTime.

Checking if Read Scale-Out is used on reports

As mentioned in the list above, the Read Scale-Out feature can be used to load-balance read-only workloads to a read-only replica of the database (where supported).

The use of Read Scale-Out in reports is captured in the custom dimension sqlDatabaseAccessIntent.

Checking if reports run in the background

As mentioned in the list above, you if you can schedule reports to run in the background, users might feel that the application runs fast as they are not blocked in the UI while waiting on the report to finish.

The use of UI sessions vs. background sessions in reports is captured in the custom dimension clientType.

For more information, see Schedule a report

Calculating how set-based the report dataset loads data

Calculate the ratio SQL rows/SQL statement. The higher this number is, the more set-based the data is loaded (which is good). So, look for low values of this ratio to identify reports, where you might be able to use a query as the datasource.

For more information about how to use a query in a dataset definition, see Defining a report dataset.

For more information about queries, see Queries in Business Central.

Report limit (maximum execution time that it can take to generate a report)

The Business Central platform has built-in limits to protect the stability of the system from a single report consuming too many resources. For example, there's a limit on the maximum execution time that it can take to generate a report.

For more information on report limits, see Report limits.

Dela via