D365 – CustTable – fast – faster – fastest – WOW!

I wanted to look deeper into an area that have troubled me for some time. Why are some forms very fast in D365, and some forms do not have the expected start-up time. At the end of this article you can see my finding, and I hope this will have an positive effect on user experienced performance.

The form I wanted to take a deeper look into is the custTable form, as this is one of the most used forms at customers. Over time we have seen that this form has increased in size, by additional features and code being added. New features are great, but it comes at a cost.

I wanted a simple test, where we are looking at a warm system, and time how long time it would take to open the CustTable form. I would like to test the opening of custTable a Cloud Hosted Tier-1 (DS12 V2), Tier-2 and PROD. This is benchmarked with a top-watch, and timing is from I click on menu item, until form is drawn and responsive. I will be using google chrome with F12, and measure until all network, and the main measurement will be TTFB (Time To First byte), as seen in the picture below. The actual waiting time tend to be beyond this, but it is the most concrete KPI I have found. The timing is therefore not the actual or experienced performance, but a KPI that can be used for comparing scenario’s.

The KPI represents the time the AOS/IIS is using to render and return the form object to the browser. Each “warm test” will be conducted 3 times, and the data is an extremely small dataset (just a few customers), as the purpose of this test is NOT to test the database, indexes or queries. It is about testing how the execution of code and caching on a form is performing.

Below is a screenshot showing where to find my performance KPI in the F12 Google chrome developer menu.

Test of architecture

In this test I’m testing how fast the custTable form is opened on Tier1, Tier-2 and on a PROD environment. The PROD/Tier-2 environments are on service fabrics(self-service), and the databases seams to be elastic pool based.

As seen on the table below the fastest execution happens on Tier-1, that is a one-box SQL, and the Tier-2 and PROD

Customer form

Warm execution

Cold execution

Tier-1 (DS12 V2)

1.50, 1.49, 1.49

22.99

Tier-2

2.20, 2.32, 2.20

16.96

Prod (6 AOS’s)

3.22, 3.25, 3.10 (20:00 CET)

2.37, 2.46, 2.40 (22:00 CET)

Not measured

What we here see is that a cold execution of the CustTable form is extreme, with a dramatic increase execution time. What we also see is that PROD differs on execution time. This can be because of different connection to another AOS, or affection of “noisy neighbor” caused by switch to Azure SQL elastic pool architecture.

On a simpler form like the “customer reason code” form, without much code, we see a very nice execution time on all tier-levels, and even cold executions are within acceptable range.

Customer reason code form

Warm execution

Cold execution

Tier-1 (DS12 V2)

0.11, 0.11, 0.12

1.01

Tier-2

0.26, 0.27, 0.26

0.98

Prod (6 AOS’s)

0.27, 0.28, 0.23

Not measured

The conclusion seams that complex forms, as the custTable are much more affected when opening a form in a cold state.

The complexity of the CustTable

As seen below, the CustTable contains 12 datasources, and quite many of them are joins. There are also 4 extension to the form.

We also see in the code in the CustTable is heavily regulated by code that controls features, country specific/regulatory elements, and display items. If we open the Customer form on a Tier-2 environment with 5 customers takes between 2-3s. In total there are 16.413 method calls, and of them 1.330 are unique method calls.

I did not get any meaningful information out of the recorded summarized tracefile analysis, so I must continue to more manually look into the actual execution of code.

Test of effect when reducing complexity CustTable

My next step in the analysis is to see what is affecting the execution time. In the following section I’m testing in a Tier-1 D12V2 environment. I have made 5 copies of the CustTable form, in each form, I’m removing more and more code and data sources. I name them:

  1. Standard, but no calls to feature enablement
  2. Fast : All code and data datasources removed, except custTable and DirParty
  3. Faster : All code and data datasources removed, except custTable. Display method on customer name
  4. Fastest; All code is removed except CustTable data source

To simulate a “cold execution” we can flush the cache by adding the following to the URL: &mi=ACTION%3ASysFlushAOD

CustTable form type

Warm execution (s)

Cold execution (s)

Standard 10.0.18

1.50, 1.49, 1.49

22.99

1.Standard, but no calls to feature enablement code

1.34, 1.43, 1,39

17.94

2.Fast : All code and data datasources removed, except custTable and DirParty

0.72, 0.72, 0.73

1.22

3.Faster : All code and data datasources removed, except custTable. Display method on customer name

0.56, 0.62, 0.57

0.96

4.Fastest; All is removed except CustTable data source

0.34, 0.34, 0.38

0.49

5.Customer reasoncode form

0.11, 0.11, 0.12

0.32

What we see in the table above, is that the main thing that is taking time, is the execution of code. The datasources do not affect the user experienced performance in this scenario. The results show that simpler forms with less code have a huge effect of the execution and the cold-start scenario.

WOW! – Other findings.

I have found one area that is affecting heavily the cold startup of forms. That is the office button, that is typically initiated when the form is loading. I tried disabling the office button code, a cold startup of CustTable went from 23s to 5s. And this button is used everywhere.

This “fix” does not seam to have a large effect on warmed up system. But keep in mind that with the one-version strategy and adding extensions we are clearing any cache quite often, that the end-users needs to rebuild on each AOS. As there are thousands of forms, you can multiply the warmup with the number of AOS’s, and you realize why manual warmup take days.

I have informed Microsoft, and hope for a positive response. Let’s continue to dig for code changes that can make the best ERP system even better, and share what you find.

I realized, that when debugging line-by-line, a small gray text pop’s up showing the actual elapsed execution time per statement. This allowed me to find the lines that actually are using a lot of time, by jumping from line to line. The timing here, is from when I did a debug of a cold system. On a warm system it will not show, as then it all is cached.

I’m really proud of finding this, as it have been on my bucket list to find some real good improvements. For more details on the chase for more performance, take a look at the Microsoft Yammer group (If you have access?) https://www.yammer.com/dynamicsaxfeedbackprograms/threads/1105410564505600

6 thoughts on “D365 – CustTable – fast – faster – fastest – WOW!

  1. 🙂 Thanks for another great insight sharing post!
    Looks like MS reads your blog Kurt, looks like the office button on CustTable got an update:
    https:/ /fix.lcs.dynamics.com/Issue/Details/?bugId=574613

    Like

  2. Hi Kurt, I Actually have a customer which complains about the performance in opening the custtable form. I hope a performance improvement like that, will be made general for all prod environments? It seems strange if we need to ask for this if Microsoft already knows about it.

    Unfortunately I have not access to the Yammer conversation, I will try to get that.

    Like

  3. Hi Kurt, the Yammer conversation is either private or deleted. Can you update ourselves with the MS response, please?

    Like

      • Ok. Here are the results after azure zone pinning performed today.

        On warmed up system:

        Opening the custTable form(warm) went from 3.25s to1.43s in prod(10.0.18). This is an 56% improvement in form opening time. In our Tier-2(10.0.19) we went from 2.30s to 2.05s and this is a small 10% improvement.

        On a cold system (tier-2) we went from 16.9s to 15.4s. This is a 8% improvement. On the prod, the cold start up of custTable was 10.05s, and I no measurements on what this was before.

        So now our prod system is significantly faster than out Tier-2 system, and we have a production system that is more responsive.

        Keep in mind that this is not verified results, and you should not expect same results in other areas/forms/processes. Work closely together with your Microsoft support contact if you have any questions if this can be applied to you.

        Like

Leave a 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.