AX 2012 R3 sales order invoice performance

(0) Share

Report

Posted on by Community Member

hi All,

we upgraded Ax from 2012 R1 to R3 recently, the kernel version is 6.3.164.0

When I tried to post multi sales orders that with same invoice account, I catch sql statement trace log, below statement using long query time and that cause our sales order invoicing performance very slow( on my instance, took above 26k query time for two sales orders, each order has 10-15 sales lines).

statement:

select sum(EndDisc), sum(InvoiceAmount), sum(SalesBalance), sum(SumMarkup), sum(SumTax) from custInvoiceJour
where custInvoiceJour.RefNum == RefNum::SalesOrder
notexists join custInvoiceSalesLink
where custInvoiceJour.SalesId == custInvoiceSalesLink.SalesId
&& custInvoiceJour.InvoiceDate == custInvoiceSalesLink.InvoiceDate
&& custInvoiceJour.InvoiceId == custInvoiceSalesLink.InvoiceId
&& custInvoiceSalesLink.OrigSalesId != _salesTable.SalesId;

the statement above from class: MCRSalesOrderTotals, method:

calcInvoicedExistMultiple(SalesTable _salesTable)

the statement seems to sum invoice amount, I tried to add info box to show what result calculated. it shows me huge amount that I believe sum almost whole table from CustInvoiceJour. and that is the reason this statement take long time.

does anyone using AX 2012 R3? if so, could you help to check if you have this class and method be hit while invoice sales invoice?

thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (2)

André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

When you upgraded to R3, can you tell what steps were done in detail related to the AOS and database upgrade? Do you have customizations which were merged?

Can you also check on the SQL server if there are tables with fragmented indexes? If there is no (good) SQL maintenance this might be a culprit.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

hi Andre, thanks for reply.

we do have customizations code merged, but in this case, the class and method that from R3 (with MCR prefixed class name, I didn't find the class on R1). our sql admin did the upgrade from R1 to R3, I am ax developer, the role for code upgrade to merge custom code with R3. for sales order, we have only custom to add few fields on salestable or custinvoicejour table, and I have merged all R3 code.

by the way, do you have R3 instance that can help me to check the class MCRSalesOrderTotal.calcInvoiceExistMultiple method for me? I am wondering if we that has been revised by MS hotfix or CUs.

thanks again.

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

You can search for existing issues and probably find a hotfix on LCS issue search. Try the next link: https://fix.lcs.dynamics.com/Issue/Results?q=sales+AND+order+AND+performance&v=7&i=2_1_3_4&ct=1_3_5_4_2_6&ft=1_2_3&cu=&pi=&pm=&pc=&x=26&y=13

It seems like you upgraded to R3 RTM. It would be possible to upgrade to CU 10 for R3. Some fixes are also included in this release.

Was this reply helpful? Yes No
Verified answer

Mea_ 60,284 on at

Like (0)

Report

I checked this code in R3 CU10 and it is there without any changes.

Actually it looks really strange but should select only invoices for current sales order, it just use double negative - "select all invoices that does not have link to order with id not equal current". Crazy logic :)

If you have performance issues good start could be trace parser : blogs.msdn.microsoft.com/.../walk-through-major-features-of-microsoft-dynamics-ax-2012-trace-parser-part-1

Using it you can find all "bottlenecks" and maybe you will find that issue is in another place.

Also as Andre proposed, doing SQL index rebuild and statistic update could fix it as well.

blogs.msdn.microsoft.com/.../database-maintenance-strategies-for-dynamics-ax

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Andre, thanks again. the link is very helpful, actually, I found a hotfix for trial balance. but for Sales order process performance, saw the hotfix is opened, status is Under investigation. look like this is known issues though.

Hi Ievgen, thanks for your help to confirm that in Cu10 this incorrect logic code still remained. I used trace parser that lead me focus on above statement. if I comment the select statement, invoice performance increased 10 times (from 25 seconds drop to 2 seconds). before microsoft issue the hotfix, I think I can comment the statement to increase performance, the method is to sum different invoice for the sales order to mark up chargers. but in my case, we don't use any mark up charger, so, this calculation is not necessary. but I will let our financial team to make decision.

thanks.

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

Hey newguy2010,

You are saying that AX spend a bunch of time on this select, did you check indexes on this table ? they could be highly fragmented so simple rebuild could fix your issue. Also statistic update could increase performance if query plan is not optimal.

Was this reply helpful? Yes No
André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

In addition: If you open the Activity monitor in SQL server, you can view recent expensive queries. If you then run this process, the above mentioned statement could appear in this list. Then you are able to look at the execution plan and see if there might be some index improvements possible. Do this only when your indexes are not (or just slightly) fragmented.

Was this reply helpful? Yes No