Performance benefits of SetLoadFields in a slow network

Introduction of the SetLoadFields function in AL language that allowed to limit the number of fields returned to the Business Central server by a SQL query was a big step forward towards achieving better performance of AL code. Nowadays, a SetLoadFields call before FindSet is a standard practice for every AL developer.

The key benefit of limiting the loaded fields is not so much in sending fewer records between the service tier and the database, as it is in avoiding unwanted table joins with a companion table when the queried table is extended. Nevertheless, the simple fact of reducing the amount of data transferred between BC and SQL Server must have its positive effect on performance, and I'd like to have a clearer understanding of how significant this benefit can be. And this is what I tried to measure and describe below.

But before delving into the test results, I need to write a few lines about the environment configuration and test data.

Test configuration 1: Local container

The first configuration I used for tests is a simple Docker container running both Business Central service and SQL Server in the same environment without network connection. It is a cloud configuration with separated application and tenant databases, but everything is hosted in one container.

Test configuration 2: Network connection

But the single container configuration is not going to give us real-life insights, because real production servers are typically separated - maybe these are separate virtual machines on the same physical hardware, or even physically separated servers connected via network. Therefore, to produce more life-like results, I bult the second test configuration in which BC service tier and SQL server run on separate laptops, connected via my home WiFi router. These are still Docker containers, but one of the containers runs Business Central Server and connect to another container hosted on the second laptop, which runs SQL Server with both application and tenant databases.

For the test numbers to make any sense, it is important to know the network connection speed. According to NetIO, my home network throughput is limited to 18 Megabytes per second. The culprit seems to be one of the laptops' WiFi adapter which cannot handle higher rates. And if I try to google anything related to this adapter, the very first link Google gives me shouts that MediaTek Wi-Fi 6 MT7921 Wireless LAN Card is a piece of junk. Apparently, new drivers could solve the problem, but I didn't try and ran the network test at 18 MBytes/sec max.

Test configuration 3: Slow network connection

My network connection does not look too fast, but I found a way to make it even slower - simply connecting both laptops via another WiFi router installed by my ISP. I keep exactly the same configuration as in the previous case, just switching to a different router, and the transfer speed in the NetIO test drops to 12 MB/sec. This setup is what I will call "Slow network", while the same network configuration with the faster router is the "Fast network".

Test data

The purpose of the test is to run the same set of SQL queries (or BC FindSet functions) gradually adding more fields to the result set and measure the time it takes to run the query in all different test configurations.

The dataset I am testing on is the Item Ledger Entry table with approximately 1 600 000 records.

I cannot limit the selection list to just one field, because there is a number of fields which BC runtime always includes in each query, no matter what the SetLoadFields instructs. For the Item Ledger Entry this minimum set consists of 11 fields, and this is the first step in every test. In all following test steps, I add 6 more fields, selecting 11, 17, 23, 29, etc. The last step raises this number from 65 to 70, simply because there are no more fields to add. For each step, I measure the time, rerun it 5 times, and take the average value as the test result, which is marked on the chart.

Test 1: Query returns 1 600 records

In each of the tests, filters are applied on the Item No. and Posting Date fields, such that the subsequent FindSet returns a certain number of records. The first test always returns 1600 records.

The result for the local setup is a pretty much flat trendline. If there is any increase in time that can be attributed to the added fields, it is completely lost behind the noise.

Network configurations, although noisy as well, nonetheless exhibit a more pronounced growth trend: selecting 70 fields is around 30% slower compared to just 11 fields when transferring data over the "fast" network and 43% slower on the slow connection.

Test 2: Query returns 16 000 records

In the second test, the dataset grows ten times larger. I run the same sequence of FindSet's with SetLoadFields, selecting more fields each time, increasing the number from 11 in the first iteration to 70 in the last one. But this time, each FindSet returns 16 000 records instead of 1 600.

Now an increasing trend in time is noticeable even for the local service, and the network transfer grows increasingly slower.

The difference between FindSet with 11 fields vs FindSet with 70 fields:

Local service: 38%

Fast network connection: 115%

Slow network connection: 130%

Now that's the difference. Sending full records with all 70 fields over the network is more than two times slower compared to transferring the same records with the minimum number of 11 fields.

Test 3: Query returns 255 000 records

Taking this test even further, I select approximately 15% of all table content, returning 255 000 records.

Now the difference becomes more obvious even in the local setup, when all services run within a single container. It's clear in this case that the more fields we add to the query, the slower it gets. I'm not publishing all measurement numbers because they make sense only in my environment and will vary a lot on a different server with different network connection. What matters here is the trendline - how fast the query time grows as we throw more field into the SELECT statement. And with 255 000 records, it grows rather fast: for both network configurations, the runtime with the full set of 70 fields is over 3 times longer compared to the minimum set of 11 fields.

Test 4: Multiple fast queries

And the final test slightly changes the approach. Instead of running a single long query, I execute multiple queries, each returning only 20 rows.

Each of the coloured lines in the chart below represents a test run with different number of executed queries, from 10 to 100, in the local configuration.

Despite the background noise, inevitable when running small queries that take only a few milliseconds to execute, all results are clearly layered with no indication of an increase in time from lower number of selected fields to the longer selection list.

After running multiple queries in the local environment, I repeat the same test on the configuration with containers connected via the WiFi network. Only the "fast network" this time.

All execution times are obviously longer, but all lines are still layered in the same manner without any tendency to slow down with more fields selected.

And the last chart to demonstrate that the picture scales up perfectly even when the test runs up to one thousand queries. Still no hint of increased runtime with more fields selected.

To conclude, SetLoadFields can definitely help reduce the query execution time. Although its main purpose is to avoid unnecessary joins on table extensions, limiting the amount of data transferred between Business Central and the database server can be very beneficial for the query performance, especially in a poor quality network with slow connection between the services.

On the other hand, this improvement becomes clearly noticeable only when the number of rows returned from the database is relatively high (hundreds or thousands or rows, depending on the quality of the network connection). Small queries selecting a handful of rows do not benefit that much from SetLoadFields: the difference in data volume is too insignificant and is easily outweighed by the overhead of preparing and sending a query from BC to SQL Server.