Why AX kernel Creates Cross Joins wherever Inner Join is required / expected ??

(0) Share

Report

Posted on by Manmohan Mehta

1,185

Hi i am using ax 2012 R3 cu10 version. I have seen that for certain system or custom views, kernel is using cross join even if i have used 1:1 inner join and table relations. I have seen one such discussion on the blog

(https://community.dynamics.com/ax/f/33/t/180942) but didn't find any solution.

In my case system is using DimensionAttributelevelValueView in Financial Statement(traditional) report and is taking too much time ( hours). But when i alter this view on sql then system loads the data in few minutes.

Is there any way we can fix this from AX itself, as we are facing performance issues on many views ?

View in AX 2012 :

SQL View:

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

Your images haven't been attached (the post contains empty img tags).

Was this reply helpful? Yes No
Manmohan Mehta 1,185 on at

Like (0)

Report

Thanks Martin, You can see them now :)

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

What performance problem you have with this query? (The execution plan will tell you which part takes most time.)

What alteration did you make?

In general, it shouldn't matter that there is CROSS JOIN, as long as there are right conditions in WHERE.

Was this reply helpful? Yes No
Manmohan Mehta 1,185 on at

Like (0)

Report

Hi Martin,

Thanks for reply.

I alter query of this view in sql by using inner joins wherever there is cross join , the same query with same set of data gives result for 1000 records in 3 sec otherwise system query took 2 minutes to give that output.

I checked the index on tables used in query and rebuild them for performance gain but found no change.

I cannot find the reason from execution plan otherwise i would have done that.

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

Execution plans are the best place where you can find the answer. I can say how your execution plan differs, so there is nothing I can tell you. Consider share sharing both queries as text (so others can copy and run them) and execution plans from your environment.

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report

We do not think that having a cross join is the root cause of the performance issue.

Please note that changing metadata, like what the views look like in SQL Server database is bad practice, since metadata must be maintained within the AX Application Object Tree. If your views would be recreated with a Data Dictionary synchronization, it would go back to what they were before.

Was this reply helpful? Yes No
Manmohan Mehta 1,185 on at

Like (0)

Report

@Vilmos That is what i was trying to figureout and yes i knew that synchronization will revert the view from ax. I am trying to understand why kernel is creating such unoptimized query...

@Martin Sure i will revert and share my both queries as text and try my best to figureout the cause from execution plan

Was this reply helpful? Yes No

Community site session details

Why AX kernel Creates Cross Joins wherever Inner Join is required / expected ??

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Why AX kernel Creates Cross Joins wherever Inner Join is required / expected ??

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates