You will love this post if you have ever experienced slowness on the Customer, Sales Orders, or Vendors pages. There is performance tuning and than performance tuning on a high-volume implementation. High Volume implementations will not work for long out of the box and will require human tuning. Let’s look at a case of something that I just dealt with today as an example.

Out of nowhere, a high volume client of mine reported that the Sales Order form was taking up to minutes to load.

Okay, this could have been the story of my life type of thing because it is so common as anything that starts to mess with accounts receivable (aka incoming money) gets noticed fast.

Step 1: Always Pull Out the Trace Parser.

Step 1, always use Trace Parser to look at the scenario. Before I go into advanced debugging and symbols, I examine trace parser to see if the Bottleneck is showing up in SQL (business logic or bad setup) or in the AOS (business logic or something strange in the Operating System). In this case, it was SQL so I knew that I would need to concentrate efforts on the business logic as this client had an excellent SQL Setup.

STEP 2: Isolate the Query, Run it in Management Studio but with IO Statistics and Time on. See what happens:

The query showed exactly what was happening. AX ran a second huge SQL query which pulled in every sales order that was created before this one. It also ran another query that pulled every sales order afterwards. This works well for small shops without many orders but for companies with many thousands of customers or sales orders or production orders or vendors,ect.. this gets real slow real fast. And playing with fact boxes won’t alleviate this.

STEP 3: Use Common Sense to figure out why Microsoft is doing something that makes no sense like loading thousands of Sales Orders when you only need one

The internal tech arch and I spent awhile discussing this and looking at fact boxes and all sorts of things on the form to figure out why AX loads all the records when you are loading a form that only allows you to work with one record at a time. Finally, he suggested that we look at the navigation pane and that was the culprit for the behavior. Upon asking the users how they felt about it, they said give them back quick loading. Well, actually, they used harsher language than that, but you get it.

STEP 4: if Microsoft’s logic doesn’t work, than change it. Some implementations are too big to load all SalesOrder records each time a person wants to see one. So, we made a 10 minute change.

From the init method in the SalesTable form, SalesTable datasource, add another Case which looks at the args right after the super is ran and adds the SalesID to the query like above. Open up the class to see an example before the args. Problem fixed in 10 minutes. Things load in seconds because you are just loading the record that you want rather than all of them. You lose the thumb through ability on the bottom but most people don’t care. Thousands of orders won’t load each time and stuff just works.

STEP 5: Special FUNCTIONAL hack for those of you who don’t use code but need to get your stuff working.

1. Go to the Sales Table list Page and hit [ctrl +g]

   

   Be sure to type in the number you are looking for and hit enter on the list page of whatever you want to open.

   

2. Double-click on the one line and watch the slow form suddenly open up in seconds if your database isn’t in need of tuning or you don’t have bunches of fact boxes open.

And Wallah.. A problem that has plagued Microsoft Large Implementations for the last 3 years is fixed. The design of 2012 allowed scrolling at a price of large data loads. How this mechanism has gone through so many hotfixes and patches with this issue is beyond me but it has. I confirm that this pattern is also in AX7 at present so you will want to understand it. It is a common fix slow vendor forms, slow Sales Order forms, Slow Customer forms and anything else that uses the list page format which opens up an editable record. Just another day in the performance tuning neighborhood and special thanks to the brilliant architect and great company that I had the pleasure of working on this issue today with.. Having a blast!