Query Performance (PurchUpdatePackingSlip)

(0) Share

Report

Posted on by jerry_216

125

Hi,

I was working with a performance issue in Dynamics Ax 2009 , "PurchEditLines" form (AccPayable-->Periodic--> Purchase Order Update-->Invoice). The user clicks the select button, which loads the "SysQueryForm" form with AOT query "PurchUpdatePackingSlip". Then put two parameters, Invoice Account(from Purchase Orders table) , and Physical date (from Inventory transactions).

But it take hours to load the result. When I check the SQL query that is executing, I found the search condition "Physical Date" was applied on the "On" clause of the Left Join, not on the "Where" clause of the query.

And this causes the query to return more than 100 thousand rows and then filter it in the client to show around 10 rows.

I changed the Join mode of the Inventrans table in query, "PurchUpdatePackingSlip" from OuterJoin to InnerJoin and the performance improved to around 12 minuts.

But I know it is not correct to change a query in AOT(in Sys Layer) for this.

But in Ax 2009, the filter in Query Ranges are applied in the Join condition and will cause performance hit in Outer Joins. Is there any work around to this problem ?

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Suggested answer

Ali Zaidi 4,657 on at

Like (0)

Report

Most query tuning options are increase the buffer size and change the join. You applied both options already; third option is to change the indexes on tables

Click System administration > Periodic > Database > SQL administration. Select all tables, all indexes, or a specific table or index, and then click Table and index options.

I found a nice article on MSDN on query performance tuning, It will possible helps you and many others.

technet.microsoft.com/.../aa569634.aspx

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

"But I know it is not correct to change a query in AOT(in Sys Layer) for this."

I think you can create a new query based on this one and, in the class that does the updates, instantiate your query instead. That won't change anything elsewhere the original query is used.

Regards,

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 301,020 Super User 2025 Season 2 on at

Like (0)

Report

Hi Jerry,

Next to a technical solution, you can also look at functionality. I think you have a very large number of records in the purchase orders and purchase order lines.

You can add a conditions in the query when selecting the lines: Status. If you include "Received" and/or "Open order" it will only look at open orders in stead of all, including the Invoiced and cancelled orders. This filter is not applied out of the box.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

André made a good point.

Now I kinda missed a "Like" or "Up vote" feature in this forum. :)

Was this reply helpful? Yes No
jerry_216 125 on at

Like (0)

Report

Hi Andre,

It really helped me. Now it is loading in 3-4 minutes.

Regards,

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

Like (0)

Report

Hi Jerry,

Glad to hear this improved the performance. Did you specify the Status filter on the Purchase order lines or the header? If it was not at the header, you can try this as well. I personally think it can be quicker than 3-4 minutes. But if you are happy, I'm happy as well :-)

Was this reply helpful? Yes No