Web services performance

Article
01/16/2024

The performance of web services (OData, API, and SOAP) is both the responsibility of the Business Central server endpoint and the consumer (the client). In this article, you can learn more about both topics.

General anti-patterns (don't do this)

Avoid using standard UI pages to expose as web service endpoints. Many things, such as fact boxes aren't returned in web service results, but use resources to prepare. Things that have historically caused performance issues on pages that are exposed as endpoints are:
- Heavy logic in OnAfterGetCurrRecord or OnAfterGetRecord
- Many SIFT fields
- FactBoxes
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 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.
Don't use a deprecated protocol such as SOAP. Instead, utilize newer technology stacks such as OData, or preferably API pages/queries. The latter are up to 10 times faster than using the SOAP protocol.

If you’re using codeunits exposed as a web service via SOAP and it is keeping you from migrating to OData, then unbound actions are equivalent to SOAP exposed codeunits in OData. For more information, see Creating and Interacting with an OData V4 Unbound Action.

When reading data from API queries, the use of aggregation methods can affect performance. Don't define this method if you intend to read data on the highest granularity of the underlying table (for example, the entry no. on G/L Entries). While the method property is meaningful for aggregated data, it slows down the performance in context of detailed level queries.

General performance patterns (do this)

Instead of exposing UI pages as web service endpoints, use the API pages or API queries 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.
If you do expose UI pages as web service endpoints as web service endpoints, then triggers must be run for all records returned from the Business Central server. As a developer, you must make your AL code conditional on the ClientType. Specifically, avoid updating FactBoxes, avoid calculation, and avoid defaulting logic. For more information, see System.GuiAllowed() Method.
The choice of protocol (SOAP, OData, or APIs) for the endpoint can have a significant impact on performance. Favor OData version 4 or APIs for the best 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.
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.

API/OData client performance patterns

When calling APIs and OData web services, there are many strategies that you can use to speed up your queries, like:

Limiting the set ($filter or $top) if you're using an expensive $expand statement
Using OData transaction $batch
Using Data Access Intent Read-only with OData

For more information about OData query performance, see OData Query Performance.

Power BI Performance patterns

When calling APIs and OData web services from Power BI, it's important that you understand the concepts of query folding. The Business Central connector uses the OData.Feed library behind the scenes, which translates numerous the Power BI constructs into OData query parameters. For example:

Table.RemoveColumns or Table.SelectColumns becomes $select
Table.SelectRows becomes $filter
Table.ExpandTableColumn becomes $expand

The following example illustrates how filters are pushed from Power BI to the Business Central connector.

It is common practice to have a query that refreshes the current date and time at every refresh in your Power BI dataset:

// LastRefreshDate query
let
    Source = DateTimeZone.UtcNow()
in
    Source

To apply a filter such as “the last 12 months of data” to other queries, simply use the PowerQuery Table.SelectRows on appropriate dates, for example:

// SalesInvoices
// Filtered to last 12 months
let
    Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null),
    PRODUCTION = Source{[Name="PRODUCTION"]}[Data],
    #"CRONUS USA, Inc." = PRODUCTION{[Name="CRONUS USA, Inc."]}[Data],
    v2.0 = #"CRONUS USA, Inc."{[Name="v2.0"]}[Data],
    salesInvoices_table = v2.0{[Name="salesInvoices",Signature="table"]}[Data],
    filteredRows = Table.SelectRows(salesInvoices_table, each [postingDate] > Date.From(Date.AddMonths(LastRefreshDate, -12)))
in
    filteredRows

For a LastRefreshDate of 2022-08-17, the Power BI connector adds this $filter predicate to the API call: /v2.0/PRODUCTION/api/v2.0/salesInvoices?$filter=postingDate gt 2022-08-17&company=CRONUS USA, Inc.

For more information about query folding in Power BI, see Power Query query folding.

Note

Query folding optimizations might not be applied in a few cases:

Query folding is NOT applied when loading data preview (such as while you are editing your query and data updates in the query editor screen).
Be careful if you do joins with other data in Power Query. If you do, some OData.Feed limitations might apply, and the OData parameters will be applied client side instead. The Workaround from the Power BI team is to use Table.Buffer before doing any join. For more information, refere to Known issues and limitations in OData-based Power BI connectors.

For more information about OData/API client performance in Business Central, see OData query performance.

When refreshing a query in a Power BI semantic model, you might experience an error such as DataSource.Error: OData: Unable to read data from the transport connection: existing connection was forcibly closed by the remote host. This error can happen either when the query refresh was interrupted by a transient error (in this case, just implement a retry) or if the query can't finish within the limit defined for web service calls on the Business Central server. In the latter case, the Business Central server will cancel the call. If this happens consistently, you need to tune your query, either by applying data filters, by reducing the number of columns (do you use all of them?), or maybe by partitioning the table (using custom partitioning through the XMLA endpoint for the model).

How to handle large throughput of web service calls

When integrating to Business Central from external systems using web services, it's important to understand the operational limits for the Business Central servers that host the web service endpoints being called. To ensure that excessive traffic doesn't cause stability and performance issues for all users, the online version of Business Central server has set up throttling limits on web service endpoints.

Make sure that your external application can handle the three HTTP status codes 429 (Too Many Requests), 503 (Service Temporarily Unavailable), and 504 (Gateway Timeout).

Handling status codes 429 and 503 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
- Randomization
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.

A common pattern is to implement a queue in your external application so that you can flatten spikes of traffic. If a request gets the HTTP status code 429 (Too Many Requests) or 503 (Service Temporarily Unavailable), then put it back in the queue and apply one of the retry strategies described previously.

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.

Outgoing web service calls block AL execution

If you call an external web service using the HttpClient module in AL, be aware that the Business Central Server blocks the execution of AL code for the session until the call completes. For interactive sessions, this behavior means that the user sees a spinning wheel during the call.

If you have enabled telemetry for your environment or app, you can use this KQL query to analyze how much time users are delayed in the UI by calls to external services.

traces
| where customDimensions has 'RT0019'
| where customDimensions.clientType == 'WebClient'
| extend executionTimeInMs = toreal(totimespan(customDimensions.serverExecutionTime))/10000 //the datatype for executionTime is timespan
| summarize count() // how many calls
, sum(executionTimeInMs) // sum of delays for UI sessions
, avg(executionTimeInMs) // average waiting time by this app
, max(executionTimeInMs) // average waiting time by this app
by 
// which app is calling out from the UI
  extensionPublisher = tostring( customDimensions.extensionPublisher )
, extensionName = tostring( customDimensions.extensionName )
, extensionVersion = tostring( customDimensions.extensionVersion )

Web service telemetry

All incoming calls to Business Central web services and outgoing calls to external services are logged to partner telemetry. Telemetry enables you to monitor which endpoints are being used and the category of the web service, like SOAP, OData, or API. You can also see possible failures, which are tracked in the HTTP status codes for the calls.

For more information, see Web service telemetry