Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Strange! Query runs sl...
Microsoft Dynamics AX (Archived)

Strange! Query runs slowly in AX, but the equivalent T-SQL runs pretty fast.

(0) ShareShare
ReportReport
Posted on by Andrew Xu 3,877

I was trying to mark the open customer transactions in 'Settle open transaction', and I found it took about 3 minutes to mark after I ticked the checkbox.

After tracing the code, I found when executing the code, there's a query slows down the process. But the weird thing is that the same T-SQL which I got from trace parser runs less than 1 second to return the same result set as the query returns. So I think the index and statics should be good on the tables. What could be the cause of this performance issue?

*This post is locked for comments

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: Strange! Query runs slowly in AX, but the equivalent T-SQL runs pretty fast.

    There are a handful of queries in AX that, in my experience, just won't generate a cached plan that works across all of the situations where it gets used.  And yes, I've already exhausted statistics and indexes as a means of making those queries compile better.  I don't normally recommend forceLiterals, as I agree it is not a best practice.  But in this case, with these specific queries (customer/vendor open transactions), I've found it's the best solution.

    I could list a few other cases, but it would be changing the subject.

    I did just recently solve a problem with the highly complex queries in Classes\ReqTransNeutralDelete\methods\run() by forceLiterals, moving the exist join to the end, and using forceSelectOrder for the first time ever in AX.  Now the query takes less than 1 second instead of 10 minutes to run.  Sometimes these problems are caused by a SQL Server service pack or cumulative update that tweaks the engine, and the only solution is to tweak the queries to get them to behave well.

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at
    RE: Strange! Query runs slowly in AX, but the equivalent T-SQL runs pretty fast.

    ForceLiterals is a cosmetic fix/workaround for the underlying problem, forcing a new and possibly better query plan generation (by using fixed parameter values).

    It is not recommended to use forceliterals, since you will end up with a query plan compiled for each execution, it is not being reused from cache. If you run the sp_Blitz analysis toolkit from Brent Ozar on your SQL instance, you may get performance issue highlights for high number of plans generated due to that.

    You most likely have a bad query plan, so definitely doing an indexing+stats update+sp_recompile on the tables involved will cause new query plans to be generated (without the forceliterals), and might be a better plan that executes faster.

    Then you could pin that query plan if it runs faster as a Plan guide, so it will always execute like that.

  • Andrew Xu Profile Picture
    Andrew Xu 3,877 on at
    RE: Strange! Query runs slowly in AX, but the equivalent T-SQL runs pretty fast.

    Thank you, Brandon,

    After I enabled the forceliterals for the query, it's much faster. I guess the query is loading more data, joining bigger tables than AX expected, then somehow forceliterals was not enabled automatically. Correct me if I'm wrong.

    Thank Andre and Mathieu for your help as well.

  • Verified answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: Strange! Query runs slowly in AX, but the equivalent T-SQL runs pretty fast.

    Did you run the same query as a cursor with parameters, or did you insert your values into the T-SQL prior to execution (in which case a more specific query plan will be generated).

    I've found those exact queries to be quite cumbersome, especially in an environment with many companies with a wide range of data sizes.

    Add a forceLiterals to the query in X++ and see if that resolves your issue.

  • Math Dax Profile Picture
    Math Dax on at
    RE: Strange! Query runs slowly in AX, but the equivalent T-SQL runs pretty fast.

    What is the build number of AX ?

    Same problem with different users?

    Did you clear AX local cache?

    Did you delete usage data ?

    Did you try from a different client machine?

    Same performance issue from the AOS machine?

    Same issue in a different environment ?

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,622 Super User 2024 Season 2 on at
    RE: Strange! Query runs slowly in AX, but the equivalent T-SQL runs pretty fast.

    Hi Andrew,

    How many companies have you setup in AX2012? Did you check for fragmented indexes? Can you share the part of the trace where it is showing long duration when running in AX (screenshot)?

  • Andrew Xu Profile Picture
    Andrew Xu 3,877 on at
    RE: Strange! Query runs slowly in AX, but the equivalent T-SQL runs pretty fast.

    I did check the execution plan of the query, no index is missing.

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

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,354 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans