Performance Articles For Developers
In this article, you can read about ways to tune performance when developing for Business Central.
- Writing efficient pages
- Writing efficient Web Services
- Writing efficient reports
- AL performance patterns
- Efficient Data access
- Testing and validating performance
- Tuning the Development Environment
Writing efficient pages
There are many patterns that a developer can use to get a page to load faster. Consider the following patterns:
- Avoid unnecessary recalculation
- Do less
- Offloading the UI thread
Pattern - Avoid unnecessary recalculation
To avoid unnecessary recalculation of expensive results, consider caching the data and refresh the cache regularly. Let's say you want to show the top five open sales orders or a VIP customers list on the role center. The content of such a list probably doesn't change significantly every hour. There's no need to calculate that from raw data every time the page is loaded. Instead, create a table that can contain the calculated data and refresh every hour/day using a background job.
Another example of unexpected recalculation is when using query objects. In contrast to using the record API, query results aren't cached in the primary key cache in the Business Central server. Any use of a query object will always go to the database. So, sometimes it's faster to not use a query object.
Pattern - Do less
One way to speed up things is to reduce the work that the system must do. For example, to reduce slowness of role centers, consider how many page parts are needed for the user. Another benefit of a simple page with few UI elements can also be ease of use and navigation.
Remove calculated fields from lists if they aren't needed, especially on larger tables. Also, if indexing is inadequate, calculated fields can significantly slow down a list page.
Consider creating dedicated lookup pages instead of the normal pages when adding a lookup (the one that looks like a dropdown) from a field. Default list pages will run all triggers and FactBoxes even if they aren't shown in the lookup. For example, Business Central 2019 release wave 1 added dedicated lookup pages for Customer, Vendor, and Item to the Base Application.
Pattern - Offloading the UI thread
To get to a responsive UI fast, consider using Page Background Tasks for calculated values, for example, the values shown in cues.
For more information about Page Background Tasks, see Page Background Tasks.
Writing efficient Web Services
Business Central supports for Web services to make it easier to integrate with external systems. As a developer, you need to think about performance of web services both seen from the Business Central server (the endpoint) and as seen from the consumer (the client).
Anti-patterns (don't do this)
Avoid using standard UI pages to expose as web service endpoints. Many things, like FactBoxes, aren't exposed in OData, but will use resources to compute.
Things that have historically caused performance issues on pages that are exposed as endpoints are:
- Heavy logic in
- Many SIFT fields
Avoid exposing calculated fields, because calculated fields are expensive. Try to move them to a separate page or to refactor the code so the value is stored on the physical table (if applicable). Complex types are also a performance hit because they take a lot of time to calculate.
Don't use temp tables as a source if you have many records. Temp tables that are based on APIs are a performance hit. The server has to fetch and insert every record, and there's no caching on data in temp tables. Paging becomes difficult to do in a performant manner. A rule of thumb is if you have more than 100 records, don't use temp tables.
Don't insert child records belonging to same parent in parallel. This condition causes locks on parent and Integration Record tables because parallel calls try to update the same parent record. The solution is to wait for the first call to finish or use $batch, which will make sure calls get executed one after another.
Performance patterns (do this)
Instead of exposing UI pages as web service endpoints, use the built-in API pages because they've been optimized for this scenario. Select the highest API version available. Don't use the beta version of the API pages. To read more about API pages, see API Page Type.
The choice of protocol for the endpoint can have a significant impact on performance. Favor OData version 4 for the fastest performance. It's possible to expose procedures in a codeunit as an OData end point using unbound actions. To read more about OData unbound actions, see Creating and Interacting with an OData V4 Unbound Action.
For OData, limit the set ($filter or $top) if you're using an expensive $expand statement. If you've moved calculated fields to a separate page, then it's good practice to limit the set to get better performance.
If you want OData endpoints that work as data readers (like for consumption in Power BI), consider using API queries and set DataAccessIntent = ReadOnly. For more information, see API Query Type and DataAccessIntent Property.
Use OData transaction $batch requests where relevant. They can reduce the number of requests the client needs to do when errors occur. For more information, see Tips for working with the APIs - OData transactional $batch requests.
Web service client performance
The online version of Business Central server has set up throttling limits on web service endpoints to ensure that excessive traffic can't cause stability and performance issues.
Make sure that your client respects the two HTTP status codes 429 (Too Many Requests) and 504 (Gateway Timeout).
Handling status code 429 requires the client to adopt a retry logic while providing a cool off period. You can apply different strategies, like:
- Regular interval retry
- Incremental intervals retry
- Exponential back-off
Handling status code 504 - Gateway Timeout requires the client to refactor the long running request to execute within time limit by splitting the request into multiple requests. Then, deal with potential 429 codes by applying a back off strategy.
Read more about web service limits, see Working with API limits in Dynamics 365 Business Central.
The same advice applies for outgoing web service calls using the AL module HttpClient. Make sure your AL code can handle slow response times, throttling, and failures in external services that you integrate with.
Writing efficient reports
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:
To use queries to implement fast reports, see Queries in Business Central.
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.
Read more about how to tune RDL reports here:
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, 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.
Efficient extracts to data lakes or data warehouses
When establishing a data lake or a data warehouse, you typically need to do two types of data extraction:
- A historical load (all data from a given point-in-time)
- Delta loads (what's changed since the historical load)
The fastest (and least disruptive) way to get a historical load from Business Central online is to get a database export as a BACPAC file (using the Business Central admin center) and restore it in Azure SQL Database or on a SQL Server. For on-premises installations, you can just take a backup of the tenant database.
The fastest (and least disruptive) way to get delta loads from Business Central online is to set up API queries configured with read-scaleout and use the data audit field LastModifiedOn (introduced in version 17.0) on filters.
AL performance patterns
Knowledge about different AL performance patterns can greatly improve the performance of the code you write. In this section, we'll describe the following patterns and their impact on performance.
- Use built-in data structures
- Run async (and parallelize)
- Use set-based methods instead of looping
- Other AL performance tips and tricks
Pattern - Use built-in data structures
AL comes with built-in data structures that have been optimized for performance and server resource consumption. Make sure that you're familiar with them to make your AL code as efficient as possible.
When concatenating strings, make sure to use the
TextBuilder data type and not repeated use of the
+= operator on a
Text variable. For more information, see TextBuilder Data Type.
If you need a key-value data structure that is optimized for fast lookups, use a
Dictionary data type. For more information, see Dictionary Data Type.
List data type if you need an unbound "array" (where you would previously create a temporary table object). For more information, see List Data Type.
Mediaset data types instead of the
Blob data type. The
MediaSet data types have a couple advantages over the
Blob data type when working with images. First of all, a thumbnail version of the image is generated when you save the data. You can use the thumbnail when loading a page and then load the larger image asynchronously using a page background task. Second, data for
MediaSet data types is cached on the client. Data for the
Blob data type is never cached on the server. It's always fetched from the database.
Pattern - Run async (and parallelize)
It's often desirable to offload AL execution from the UI thread to a background session.
Here are some examples of this pattern:
- Don't let the user wait for batches
- Split large tasks into smaller tasks and run them in parallel
There are many different ways to spin up a new task:
They come with different characteristics as described in this table:
|Method to start a new task||Properties|
|Page Background Task||Can (will) be canceled
Call back to parent session
Runs on same server
Not as controlled as a Page Background Task
Any server in a cluster can start it
Survives server restarts
Any server in a cluster can start it
Survives server restarts
Logging of results
Pattern - Use set-based methods instead of looping
The AL methods such as
SetAutoCalcFields are examples of set-based operations that are much faster than looping over a result set and do the calculation for each row.
One common use of the
CalcSums method is to efficiently calculate totals.
Try to minimize work done in the
OnAfterGetRecord trigger code. Common performance coding patterns in this trigger are:
CalcFieldscalls. Defer them until the end.
- Avoiding repeated calculations. Move them outside the loop, if possible.
- Avoid changing filters. This pattern requires the server to throw away the result set.
Consider using a query object if you want to use a set-based coding paradigm. These pros and cons for using query objects:
|Pros for using a query object||Cons for using a query object|
|- Will bypass the AL record API where server reads all fields.
- With a covering index, you can get fast read performance for tables with many fields.
- Can join multiple tables.
|- Query object result sets aren't cached in the servers primary key (data) cache.
- No writes are allowed.
- You can't add a page on a query object.
Read more about query objects here:
- Using Queries Instead of Record Variables
- Query object
- Query overview
- TopNumberOfRows Property
- Query Objects and Performance
Pattern - Use partial records when looping over data or when table extension fields aren't needed
When writing AL code for which the fields needed on a record are known, you can use the partial records capability to only load out these fields initially. The remaining fields are still accessible, but they'll be loaded as needed.
Partial records improve performance in two major ways. First, they limit the fields that need to be loaded from the database. Loading more fields leads to more data being read, sent over the connection, and created on the record. Second, partial records limit the number of table extensions that need to be joined.
The performance gains compound when looping over many records, because both effects scale with the number of rows loaded.
For more information, see Using Partial Records.
Other AL performance tips and tricks
If you need a fast, non-blocking number sequence that can be used from AL, refer to the number sequence object type. Use a number sequence object if you:
- Don't want to use a number series.
- Can accept holes in the number range.
For more information, see NumberSequence Data Type.
Table extension impact on performance
Table extensions are eager-joined in the data stack when accessing the base table. It's currently not possible to define indexes that span base and extension fields. So avoid splitting your code into too many table extensions. Also, be careful about extending central tables, such as General Ledger entry, because it can severely hurt performance.
An alternative when doing data modeling for extending a table with new fields is to use a related table and define a FlowField on the base table.
Here are the pros and cons of the two data models:
|Data model for extending a table||Properties|
|Table extension||Fields can be added to lists and are searchable.
Always loaded with the base table.
Expensive at runtime but easy to use.
Use only for critical fields.
|Related tables||Need to set up table relations.
Dedicated page for editing.
Requires flow field to be shown in lists.
Doesn't affect performance of base table.
Excellent for FactBoxes.
Limit your event subscriptions
The following are best practices for getting performant events:
- There's no significant cost of having a publisher defined.
- Static automatic has a cost over manually binding (there's an overhead of creating and disposing objects).
- Codeunit size of the subscriber matters. Try to have smaller codeunits.
- Use single instance codeunits for subscribers, if possible.
Table events change the behavior of SQL optimizations on the Business Central Server in the following ways:
- The Business Central Server will issue SQL update/delete statements row in a for loop rather than one SQL statement.
- They impact
DeleteAllmethods that normally do bulk SQL operations to be forced to do single row operations.
Efficient data access
Many performance issues are related to how data is defined, accessed, and modified. It's important to know how concepts in AL metadata and the AL language translate to their counterparts in SQL.
Tables and keys
Many performance issues can be traced back to missing indexes (also called keys in Business Central), but index design is often not a key skill for AL developers. For best performance, even with large amounts of data, it's imperative to design appropriate indexes according to the way your code will access data.
These articles on indexing are worth knowing as an AL developer:
Indexes have a cost to update, so it's recommended to not use them too frequently.
SumIndexField Technology (SIFT)
SumIndexField Technology (SIFT) lets you quickly calculate the sums of numeric data type columns in tables, even in tables with thousands of records. The data type includes Decimal, Integer, BigInteger, and Duration. SIFT optimizes the performance of FlowFields and query results in a Business Central application.
Ensure appropriate SIFT indices for all FlowFields of type sum or count.
Read more about SIFT here:
The following article can help you find missing SIFT indexes on FlowFields:
How AL relates to SQL
The AL programming language, to some degree, hides how data is read and written to the database. To effectively code for performance, you need to know how AL statements translate to the equivalent SQL statements.
The following articles cover how AL relates to SQL:
- AL Database Methods and Performance on SQL Server
- Data Access
- Data read/write performance
- Bulk Inserts
How to get insights into how AL translates to SQL
If you want to track how Business Central Server translates AL statements to SQL statements, use either database statistics in the AL debugger or telemetry on long running queries.
Read more here:
Using Read-Scale Out
Business Central supports the Read Scale-Out feature in Azure SQL Database and SQL Server. Read Scale-Out is used to load-balance analytical workloads in the database that only read data. Read Scale-Out is built in as part of Business Central online, but it can also be enabled for on-premises.
Read Scale-Out applies to queries, reports, or API pages. With these objects, instead of sharing the primary, they can be set up to run against a read-only replica. This setup essentially isolates them from the main read-write workload so that they won't affect the performance of business processes.
Testing and validating performance
It's imperative to test and validate a Business Central project before deploying it to production. In this section, you find resources on how to analyze and troubleshoot performance issues and guidance on how to validate performance of a system.
Performance Unit Testing
You can use the
SessionInformation data type in unit tests that track the number of SQL statements or rows read. Use it before and after the code to be tested. Then, have assert statements that check for normal behavior.
For more information, see SessionInformation Data Type.
Performance Scenario and Regression Testing
Use the Performance Toolkit to simulate the amount of resources that customers use in realistic scenarios to compare performance between builds of their solutions.
The Performance Toolkit helps answer questions such as, "Does my solution for Business Central support X number of users doing this, that, and the other thing at the same time?"
For more information, see The Performance Toolkit Extension.
To test insert/update performance, make sure to un-install the test framework first. If the test framework is installed, then no insert/update statements can utilize bulk mode and will instead run row-by-row.
Performance Throughput Analysis
The Performance Toolkit doesn't answer questions such as, "How many orders can Business Central process per hour?" For this kind of analysis, test the time to execute key scenarios using the Performance Toolkit, and then use the guidance on Operational Limits for Business Central Online. For advanced analysis, consider using a queueing model such as a M/M/1 queue to answer whether the system can process the workload you intend.
The following performance telemetry is available in Azure Application Insights (if that has been configured for the environment):
- Database locks
- Long Running AL operations
- Long Running SQL Queries
- Page views
- Sessions started
- Web Service Requests
Read more in this section: How to use telemetry to analyze performance
The following articles can be of help in troubleshooting performance issues:
- Find missing SIFT indexes for FlowFields by Disabling SmartSQL
- Use Page Inspection to find extensions participating on a page
- Viewing Table Sizes
Tuning the Development Environment
The following articles explain what you can do as a developer to tune your development environment for better performance:
How Application Configurations Affect Performance
Performance of On-Premises Installations
How to Work with a Performance Problem
Performance tips for business users