Error - The number of joins in the statement is 28. This exceeds the maximum of 26. Turn on exception for this warning to see the call stack.

Table inheritance in AX 2012 is a nice feature, but it has to be used carefully due to the performance impact it might have. Putting the performance impact aside, this post is mainly focus on the error due to exceeded maximum table join, which is caused by Table inheritance and Replacement key.

The error message - "The number of joins in the statement is 28. This exceeds the maximum of 26. Turn on exception for this warning to see the call stack." (the 28 is due to the form has 28 table join and the 26 is a server settings for AOS, so these two numbers are not fixed, it depends on your form's datasource and server settings)

Brief info
- Forms\CustTable has an additional datasource. Eg. CustTableExtended
- CustTableExtended is InnerJoin to CustTable

A brief history of sample issue
While it is working OK the day before, the next day this error suddenly occur on Forms\CustTable. A further investigation found that the table join has increased due to a new field has been added to a table (Eg. CustTableExtended) which is InnerJoin with CustTable.

Cause of error
- The new table field on CustTableExtended has a relation to a derived/child table
- This derived/child table has a relation to the parent table
- On the parent table, it has a Replacement key.

When the CustTable form is opened, this additional field is joined to the child table, which is then joined to the parent table, this has caused an additional two table join, which happens to hit the limit of maximum table join.

Resolution
A. Consider redesign the solution

B. Remove Replacement key (usually won't choose this option due to presentable on form level is not user friendly - Eg. Showing RecId. See screenshot at the bottom of this post)

C. Increase the maximum table join (System administration > Setup > Server configuration > 'Performance optimization' tab > 'Performance settings' group > 'Maximum number of tables in join' field)

Simpler example
1. Current server settings: Maximum number of tables in join = 30

2. Tables\DemoBaseGroup
Parent table, has replacement key, SupportInheritance = Yes

3. Tables\DemoGroupA - Q (17 tables)
Derived/child table, extends DemoBaseGroup, SupportInheritance = Yes

4. Tables\DemoMainTable
Standalone table, , SupportInheritance = No, contain 17 fields (GroupA - Q), each field has a relation to its related table (DemoGroupA - Q) on RecId

5. Forms\DemoMainTable
Only 1 DataSource (DemoMainTable)

When the form is opened, it hit the maximum tables join error.
Given there's only 1 table in the DataSource, this error would first appears to be weird, but once you know field(s) with relation to inherited table which has Replacement key will cause an auto table join to the related tables, then it will make sense.

Below are the query generated when the form is run.
Two table join is created for each field with relation to inherited table with Replacement key.
Eg. 17 fields in the table will cause 34 table joins.


SELECT * 
FROM  DEMOMAINTABLE T1 
LEFT OUTER JOIN (DEMOBASEGROUP T2 INNER JOIN DEMOGROUPA T3 ON ((T3.DATAAREAID=?) AND (T3.RECID=T2.RECID))) ON ((T2.DATAAREAID=?) AND (T1.GROUPA=T3.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T4 INNER JOIN DEMOGROUPB T5 ON ((T5.DATAAREAID=?) AND (T5.RECID=T4.RECID))) ON ((T4.DATAAREAID=?) AND (T1.GROUPB=T5.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T6 INNER JOIN DEMOGROUPC T7 ON ((T7.DATAAREAID=?) AND (T7.RECID=T6.RECID))) ON ((T6.DATAAREAID=?) AND (T1.GROUPC=T7.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T8 INNER JOIN DEMOGROUPD T9 ON ((T9.DATAAREAID=?) AND (T9.RECID=T8.RECID))) ON ((T8.DATAAREAID=?) AND (T1.GROUPD=T9.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T10 INNER JOIN DEMOGROUPE T11 ON ((T11.DATAAREAID=?) AND (T11.RECID=T10.RECID))) ON ((T10.DATAAREAID=?) AND (T1.GROUPE=T11.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T12 INNER JOIN DEMOGROUPF T13 ON ((T13.DATAAREAID=?) AND (T13.RECID=T12.RECID))) ON ((T12.DATAAREAID=?) AND (T1.GROUPF=T13.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T14 INNER JOIN DEMOGROUPG T15 ON ((T15.DATAAREAID=?) AND (T15.RECID=T14.RECID))) ON ((T14.DATAAREAID=?) AND (T1.GROUPG=T15.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T16 INNER JOIN DEMOGROUPH T17 ON ((T17.DATAAREAID=?) AND (T17.RECID=T16.RECID))) ON ((T16.DATAAREAID=?) AND (T1.GROUPH=T17.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T18 INNER JOIN DEMOGROUPI T19 ON ((T19.DATAAREAID=?) AND (T19.RECID=T18.RECID))) ON ((T18.DATAAREAID=?) AND (T1.GROUPI=T19.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T20 INNER JOIN DEMOGROUPJ T21 ON ((T21.DATAAREAID=?) AND (T21.RECID=T20.RECID))) ON ((T20.DATAAREAID=?) AND (T1.GROUPJ=T21.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T22 INNER JOIN DEMOGROUPK T23 ON ((T23.DATAAREAID=?) AND (T23.RECID=T22.RECID))) ON ((T22.DATAAREAID=?) AND (T1.GROUPK=T23.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T24 INNER JOIN DEMOGROUPL T25 ON ((T25.DATAAREAID=?) AND (T25.RECID=T24.RECID))) ON ((T24.DATAAREAID=?) AND (T1.GROUPL=T25.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T26 INNER JOIN DEMOGROUPM T27 ON ((T27.DATAAREAID=?) AND (T27.RECID=T26.RECID))) ON ((T26.DATAAREAID=?) AND (T1.GROUPM=T27.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T28 INNER JOIN DEMOGROUPN T29 ON ((T29.DATAAREAID=?) AND (T29.RECID=T28.RECID))) ON ((T28.DATAAREAID=?) AND (T1.GROUPN=T29.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T30 INNER JOIN DEMOGROUPO T31 ON ((T31.DATAAREAID=?) AND (T31.RECID=T30.RECID))) ON ((T30.DATAAREAID=?) AND (T1.GROUPO=T31.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T32 INNER JOIN DEMOGROUPP T33 ON ((T33.DATAAREAID=?) AND (T33.RECID=T32.RECID))) ON ((T32.DATAAREAID=?) AND (T1.GROUPP=T33.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T34 INNER JOIN DEMOGROUPQ T35 ON ((T35.DATAAREAID=?) AND (T35.RECID=T34.RECID))) ON ((T34.DATAAREAID=?) AND (T1.GROUPQ=T35.RECID)) 
WHERE (T1.DATAAREAID=?) ORDER BY T1.RECID OPTION(FAST 5)

By removing the Replacement key, the error is gone and the query only has one table, but the form will show the RecId value itself only, which appears to be not user friendly/non-readable. So this usually won't be the solution. The best to do is either redesign the solution or increase the maximum table join.

One might think, why design a table with so many fields with relation to separate inherited tables? Bear in mind the demo above is just to show how the error occur, it wasn't a real life example, but on CustTable, that could easily be the real life example due to the massive table join in standard AX. 

The 'DirPartyPostalAddressView' in Forms\CustTable itself already has many table, together with other datasource, that's more than 10, if the form is further developed/customized (Eg. added one or two custom table and those table each has two or three field relation to inherited tables with Replacement key) then it would be easy to reach the limit.