Table inheritance is an interesting feature of Dynamics AX 2012 allowing to model type hierarchies in database in a way similar to object modeling. Dynamics AX allows not only to define inheritance in metadata, but it also takes it automatically into account in queries to database, in forms and so on.

The problem is that many developers uses table inheritance without understanding how it really works and an improper usage may have significant impact to quality of data model and to performance. (It also complicates implementation of forms etc. but that’s out of scope for today.)

Let’s start with this trivial query:

OMInternalOrganization org;
select org;

We want to know what query is in fact sent to database – we can find it easily by another feature added in AX2012.

OMInternalOrganization org;
select generateOnly org;
info(org.getSQLStatement());

Don’t overlook generateOnly keyword, getSQLStatement() would return an empty string without it. You also don’t get any output if data is read from cache, because no query is really sent to database.

If you expected some result like SELECT * FROM OMInternalOrganization, you’ll be rather surprised. The resulting query looks in AX2012 this way (I replaced the list of columns by star, for the sake of brevity):

SELECT * FROM DIRPARTYTABLE T1
    CROSS JOIN DIRORGANIZATIONBASE T2
    CROSS JOIN OMINTERNALORGANIZATION T3
    LEFT OUTER JOIN OMTEAM T4 ON (T3.RECID=T4.RECID)
    LEFT OUTER JOIN OMOPERATINGUNIT T5 ON (T3.RECID=T5.RECID)
    LEFT OUTER JOIN COMPANYINFO T6 ON (T3.RECID=T6.RECID)
    WHERE (T2.RECID=T1.RECID) AND (T3.RECID=T2.RECID)

We see a query for a single table in X++ but the query sent to database uses six tables. How come?

If the system returned only fields from OMInternalOrganization table, it wouldn’t be very useful (there is, indeed, a single active field: OrganizationType). In the first place, we want to get also all inherited fields, therefore AX must query for fields in all parents, in this case in tables DirPartyTable and DirOrganizationBase. (Don’t get confused by those CROSS JOINs, the WHERE clause effectively turns them to INNER JOINs.)

A record in OMInternalOrganization table may de facto represents one of its children (OMInternalOrganization is even abstract, so it have no sense by itself). To get complete data for all records, we have to link also fields from all children, namely from OMTeam, OMOperatingUnit and CompanyInfo. That naturally requires INNER JOINs.

The whole complicated query simply tries to obtain all needed fields. Nevertheless if you require just some fields, AX can significantly simplify the query. For example:

OMInternalOrganization org;
select OrganizationType from org;

… generates this query:

SELECT T1.INSTANCERELATIONTYPE,T1.RECID,T2.ORGANIZATIONTYPE,T2.RECID
FROM DIRPARTYTABLE T1
CROSS JOIN OMINTERNALORGANIZATION T2
WHERE (T2.RECID=T1.RECID)

To avoid joining too many tables, you shouldn’t create too extensive inheritance hierarchies. You should also try to limit the returned columns, because then it’s not necessary to fetch all tables in the given sub-tree.

If you want to learn more, try Developing with Table Inheritance.

AX2012 R2

To make it even more complicated, let’s look at how it works in AX 2012 R2 (CTP). We’ll run an almost identical query (forceLiterals will help to get details needed later):

OMInternalOrganization org;
select generateOnly forceLiterals org;

And we’ll get this T-SQL code:

SELECT * FROM DIRPARTYTABLE T1
WHERE ((T1.PARTITION=5637144576) AND (T1.INSTANCERELATIONTYPE IN (2376,41,2377,5329) ))

It’s obvious that the query is much simpler than in the previous version, but it’s probably not so clear how it works.

You may be taken aback by the fact that the query doesn’t use any table but DirPartyTable, it doesn’t contain even OMInternalOrganization that we used in our X++ query. If I showed all fields here, you could find (after some amount of investigation) that all fields defined in Dynamics AX in children are placed directly in DirPartyTable in database.

But if all fields are in DirPartyTable, what is the purpose of the child tables such as OMInternalOrganization and CompanyInfo? The answer is: They are really not needed in database and they don’t exist there at all. They exist as independent objects in application layer only.

Although data for all type from a particular hierarchy are saved in a single table, information about structure still exists there. You can see in T-SQL how AX obtains the sub-tree containing the requested table:

WHERE ((T1.PARTITION=5637144576) AND (T1.INSTANCERELATIONTYPE IN (2376,41,2377,5329) ))

The Partition field is related to another new feature in AX2012 R2, but it has no impact to inheritance, so we can ignore it here. The important thing is InstanceRelationType – this field formerly defined which additional tables need to be read, now it’s simply used to filter the requested types of records. Values in InstanceRelationType represents table IDs:

All fields that are not defined in the parent table make sense only for specific types of records. If a field doesn’t exist in a particular subtype, its value in database is simply NULL.

Although table inheritance in AX looks still the same way, the implementation in database has changed significantly. AX2012 R2 can do without joining many tables, but on the other hand, it makes a single, potentially large table. The way of designing and optimizing may therefore vary in AX2012 and AX2012 R2, but the basic rules can be applied universally: to understand what happens in background, to avoid too complicated hierarchies, to avoid inheritance in performance-critical areas and to test the final implementation (including performance testing).