Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Long running Query - P...
Microsoft Dynamics AX (Archived)

Long running Query - Purchase order posting

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

While doing the purchase order posting in AX 2012 R3 CU13 -   i can able to see the update query in the database blocking .  During that time , AX performance was very slow until i kill the block. Once it is killed , AX performance become normal.

Please find th eupdate query: 

UPDATE T1 SET DEPENDENTSUBLEDGERJOURNALACCOUNTENTRY=T2.RECID,DEPENDENTACCOUNTINGDATE=T3.ACCOUNTINGDATE,RECVERSION=@P1 FROM tempdb."DBO".t108350_9D779272B90D499ABE733AB6A1417FA3 T1 CROSS JOIN SUBLEDGERJOURNALACCOUNTENTRY T2 CROSS JOIN tempdb."DBO".t7461_531F90C085584C3D89C84C743F44DE47 T3 WHERE (T1.PARTITION=5637144576) AND ((T2.PARTITION=5637144576) AND ((((((((((((T2.EXCHANGERATE1=T1.EXCHANGERATE1) AND (T2.EXCHANGERATE2=T1.EXCHANGERATE2)) AND (T2.EXCHANGERATEDATE=T1.EXCHANGERATEDATE)) AND (T2.HISTORICALEXCHANGERATEDATE=T1.HISTORICALEXCHANGERATEDATE)) AND (T2.ISCORRECTION=T1.ISCORRECTION)) AND (T2.LEDGERDIMENSION=T1.LEDGERDIMENSION)) AND (T2.ORIGINALTRANSFERPOLICY=T1.ORIGINALTRANSFERPOLICY)) AND (T2.POSTINGTYPE=T1.POSTINGTYPE)) AND (T2.REPORTINGEXCHANGERATE1=T1.REPORTINGEXCHANGERATE1)) AND (T2.REPORTINGEXCHANGERATE2=T1.REPORTINGEXCHANGERATE2)) AND (T2.SIDE=T1.SIDE)) AND (T2.TRANSACTIONCURRENCY=T1.TRANSACTIONCURRENCY))) AND ((T3.PARTITION=5637144576) AND (T3.SUBLEDGERJOURNALENTRY=T2.SUBLEDGERJOURNALENTRY))

*This post is locked for comments

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Long running Query - Purchase order posting

    Got the solution for it .Thanks

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Long running Query - Purchase order posting

    Thanks for your time Martin. I spend time on investigating the error by SQL Trace , found the Class and the method with locks the database.   Fixed by creating some additional index and changing the code , hope it should work.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Long running Query - Purchase order posting

    I found the answer for it. Thanks

  • Martin Dráb Profile Picture
    Martin Dráb 230,253 Most Valuable Professional on at
    RE: Long running Query - Purchase order posting

    What was the locked resource? What code was blocking and what process was blocked? You showed a query - was this one the culprit or the victim, or were both the culprit and the victim two instances of the same query?

    Do you know why the lock was hold for a long time? Maybe the problem is caused by a performance problem (which may be in other queries than those involved in blocking) that prevents the lock to be released in reasonable time.

  • Martin Dráb Profile Picture
    Martin Dráb 230,253 Most Valuable Professional on at
    RE: AX 2012 R3 CU13 Long running Query - Purchase order posting

    Hi Ramesh, please separate tags with comma, because what you attached was a single tag AX 2012 R3 CU13 Long running Query - Purchase order posting. I also removed the version from title - it's already in a tag, which is the right places. And you'll have a better chance that somebody will spend time with your code if you make it easier to read. Like this:

    UPDATE T1 SET DEPENDENTSUBLEDGERJOURNALACCOUNTENTRY=T2.RECID,DEPENDENTACCOUNTINGDATE=T3.ACCOUNTINGDATE,RECVERSION=@P1
FROM tempdb."DBO".t108350_9D779272B90D499ABE733AB6A1417FA3 T1
CROSS JOIN SUBLEDGERJOURNALACCOUNTENTRY T2
CROSS JOIN tempdb."DBO".t7461_531F90C085584C3D89C84C743F44DE47 T3
WHERE T1.PARTITION=5637144576
AND T2.PARTITION=5637144576
AND T2.EXCHANGERATE1=T1.EXCHANGERATE1
AND T2.EXCHANGERATE2=T1.EXCHANGERATE2
AND T2.EXCHANGERATEDATE=T1.EXCHANGERATEDATE
AND T2.HISTORICALEXCHANGERATEDATE=T1.HISTORICALEXCHANGERATEDATE
AND T2.ISCORRECTION=T1.ISCORRECTION
AND T2.LEDGERDIMENSION=T1.LEDGERDIMENSION
AND T2.ORIGINALTRANSFERPOLICY=T1.ORIGINALTRANSFERPOLICY
AND T2.POSTINGTYPE=T1.POSTINGTYPE
AND T2.REPORTINGEXCHANGERATE1=T1.REPORTINGEXCHANGERATE1
AND T2.REPORTINGEXCHANGERATE2=T1.REPORTINGEXCHANGERATE2
AND T2.SIDE=T1.SIDE
AND T2.TRANSACTIONCURRENCY=T1.TRANSACTIONCURRENCY
AND T3.PARTITION=5637144576
AND T3.SUBLEDGERJOURNALENTRY=T2.SUBLEDGERJOURNALENTRY

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,253 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans