Dynamics Community Forum Thread Details

AX Form with very slow column sort

I have a form in Dynamics AX 2012 R3 that uses a View as the data source. Our staff wants to have this form (Issues and Receipts) sort by a date. The form loads quickly (1 or 2 seconds), but when you manually sort the grid by this date column, the form takes around 10 minutes to complete the sorting. The View pulls 2,500,000 records so no surprise it takes some time. I ran trace parser to get the SQL and analyze it for missing indexes.

Turns out it needed an index (including an index for the date column we are attempting to sort) so I created it and synchronized from AOT, but the sorting is still very slow. No improvement. I have read through other solutions on our AX forum, including adding an Index to the form's data source so it automatically sorts using it. But I think one thing that may be unique is the fact that this form's data source is a View. Looking at the form in AOT, I browse to the data sources and to the View (it is the primary), then click on the Index drop down in the properties window, but it is blank. The other table's listed further down the form's data sources do allow me to select an Index. So my question is: Is it possible to add an Index to the View data source?

I am fairly new to this company and wanted to also note that this system is starting to show a few performance issues (no surprise) in AX as the data has had time to grow over the years. There are a few examples of very slow performance that are tied to this much larger amount of data compared to a few years back. This is not a hardware issue and our IT system admins have been working diligently to make our AX environment run smoothly. And it does, except for some areas like this form. We are running statistics and reindexing nightly, so the system is very stable and performs well. This particular form I am highlighting in this post is an example of a large of amount of data, and maybe a new approach to viewing it is needed (PBI?). I appreciate any insight you may be able to provide.

Thank you!

Quick Links