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

8 Comments

  1. Hi, very nice and informative!
    How about if the table added to the Item is an “Additional information”-table existing on its own – not as a table extension.

    And then to have fields shown on the Item List I create a table extension only with flowfields looking up information from the “additional information” table.

    So the table extension only contains flowfields.

    Would this affect performance in the same way as if I just add the fields directly as a table extension?

    I would use the above solution because the additional information is not mandatory on all items.

    Thank you for all your good information on BC and have a very nice new year!

    Like

  2. thnx for sharing. what about dependecy extention does it have a Limit number? it has affect the Performance?

    Like

    1. There’s no an hard limit but yes, if you have lots of apps extending the same table and this is an heavy used table, this could affect performances (on cloud and on SaaS). You should design your solution carefully.

      Like

  3. Hello

    Ok, thank you for your response. Is there a limit on app dependecy number , Infact when we work with multiple projet we gonna have multiple app dependency on every extention.

    Even more, can we depend on the same project multiple time.

    all this issue can affect performance?

    Is that affect the performence of the solution on the production envirenement (on-PREM)?

    Best Regards!

    Like

Leave a Reply to Henrik Frederiksen Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.