Dynamics 365 Business Central: the impact of tableextensions

I think that everyone of you already knows that when working with Dynamics 365 Business Central, table extensions are killers for performances. But how much?

That’s a question that I’ve also done to myself a lot of time when working with partners or when creating my own solutions. Is there a “deadline” for how many table extensions can I create for the same table in order to not kill my performances?

In these days I was working on preparing a webcast for Microsoft WE (scheduled for late January) and for this webcast I’ve decided to create a small test myself to “mesasure” the impact of table extensions on a table with a quite large number of records.

What I’ve done is quite simple:

• I’ve created a first extension that defines a new table with two fields (Entry No. and Amount)
• I’ve filled this table with 200000 records
• I’ve created 6 extensions that adds a new field on the table created in the first extension (by using tablextension objects)
• I’ve measured the time to perform the same action: looping through all items and calculating the total amount

The custom table added from the main extension (called EXT1) is defined as follows:

Then I’ve created a page with a “Test Action” (called StartTesting) defined as follows:

This code uses FINDSET to loop through all records and calculating the total amount, then it prompts the duration of the calculation.

The 6 other dependent extensions that I’ve created are all similar and simply they define a new tableextension object for adding a new custom field on the Tableext Test Table table previously defined in the main extension. The code of these 6 extensions is simply the following:

I’ve repeated the StartTesting action code 10 times for each extension installed in order to have an average value (first with 0 tableextensions, then with 1 tableextension, 2,3 and so on).

Then, I’ve also created a new action that performs the same calculation, but this time by using the new Partial Record Loading feature:

What are the results of this game? Here it is…

Please take these results “as is”, but from the above numbers you can see that adding table extensions to the same table affect performances when using FINDSET for performing calculations, because a JOIN is always performed with all the companion tables.

A simple tableextension of an heavy table can affect your code performances for about 20%. With 6 tableextensions of the same heavy table, your code performances can be affected by 300% and more.

If you have an heavy table with too much table extensions that adds fields on it (my personal deadline is estimated to 5) you could start seeing thinkg like this in the UI:

Quite noisy I think, so be careful…

As you can see in the above numbers, there”s also a very interesting result: if you have a lot of tableextensions on the same heavy table, the usage of the partial record loading feature (SetLoadFields) helps a lot on the performance aspect (result is quite the same as without table extensions).

So, my conclusions here are the following:

• Be careful on having too much extensions that extends the same table
• Be careful on having too much extensions on a large table (using related tables could help)
• Use partial record loading where possible
• Spend time on well designing your solution architecture