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

(0) Share

Report

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

I have the same question (0)

All responses (7)

Answers (1)

Andrew Xu 3,877 on at

Like (0)

Report

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

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

Like (0)

Report

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)?

Was this reply helpful? Yes No
Math Dax on at

Like (0)

Report

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 ?

Was this reply helpful? Yes No
Verified answer

Brandon Wiese 17,788 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Andrew Xu 3,877 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

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.

Was this reply helpful? Yes No