In Dynamics AX 2012, if you are running a query with too many joins you can get an error or warning message saying “The number of joins in the statement is XX. This exceeds the maximum of 26. Turn on exception for this warning to see the call stack.”
So what is this, and what can you do about it?
Running queries with too many joins is not a particularly good idea from a performance point of view, so your first preference should be to rework the query into something simpler, something which doesn’t make SQL Server fall on its knees begging for mercy, so let’s take a look at that first.
The following steps will require restarting the AOS, so make sure you can reproduce the problem in a non-production environment first.
Simplifying the query
If you don’t already know where the query is being called from, follow the suggestion in the warning message and turn on exceptions for this warning. Unfortunately the message doesn’t actually say where and how to do this…
To make the change, go to the System Administration menu, Setup, System and then Server Configuration. In the Performance Optimisation tab, find the “Error on exceeding maximum number of tables in join” setting and change it to Yes.
Close AX and restart the AOS. (Non-production, right?)
Now, try that query again:
Ah, that’s where it is hiding… (Don’t mind the fact that the error message shows %2 instead of the number of joins, it’s above the limit, and that’s what’s important)
So, how to simplify the query? It can be split into smaller queries which each retrieve only parts of the data, either in an X++ loop over an outer query and a call to get the inner query for each iteration, or by setting the FetchMode property to “1:n” on one or more branches of the query.
Here I’m assuming that you are doing this through an AOT query. If you are building this query in X++ or with a while-select statement… What are you doing??? That sort of stuff gets REALLY painful with queries this complex.
Another way of simplifying the query would be to split one or more query branches off into views, backed by their own queries. This will still let you run the query with 1:1 FetchMode without this error, but unfortunately it will not make SQL Server’s job any easier. Actually, unless you manage to get the predicates pushed down to the nested views, performance will go down, not up…
I know it is the AX way, to build queries, based on views, based on other queries again based on views, based on further queries. (Bank transaction views and global address book views, anyone?) However, this smells more of a workaround than a solution, and when you start looking into the SQL Server query plans those things generate you’ll see why.
What if I can’t change the query?
If you can’t change the query, your only other option is to increase the maximum limit for number of joins in a query.
To do that, open the Server Configuration form as described above and change the “Maximum number of tables in join”, close the form and restart the AOS.
Please consider this to be a workaround as well and be aware of the performance implications raising this limit can have. This limit only controls the number of tables (or views) that can be joined in a single call from AX to the database, and it is possible to get into the same (or worse) performance problems by splitting up the query, converting to use views for query branches, etc.
When your query is working, time it, measure the performance of it. Use the Dynamics AX Trace Parser to capture the SQL statement your query generates and sends to SQL Server and run that query through a tool like SQL Sentry Plan Explorer or similar.
The real solution
Unfortunately, the real solution is usually the one which requires the most work. Review the requirements and the solution architecture to find out why such massive joins are required, and even if they are required at all. It is extremely likely that the code and data can be restructured to avoid it, and that will be the best way to get good performance out of your queries.