Index for delete actions

When using delete actions in Dynamics AX, don’t forget that AX will have to look into database to know if there is any related record. And if there is no index, the performance can be really bad.

I’ve just run into such a case, when deleting a customer executed a query running for six seconds. SQL tracing in AX showed me the following query and call stack:

SELECT T1.RECID FROM SOME_CUSTOM_TABLE T1
WHERE (((PARTITION=?) AND (DATAAREAID=?)) AND (CUSTACCOUNT=?))
(C)\Classes\xRecord\doValidateDelete
(C)\Classes\xRecord\validateDelete
(C)\Data Dictionary\Tables\CustTable\Methods\validateDelete - line 8
(C)\Classes\FormDataSource\validateDelete
(C)\Classes\FormDataSource\delete
(C)\Forms\CustTable\Data Sources\CustTable\Methods\delete - line 8

Although CustTable has overridden validateDelete(), the code at line 8 is just a call of super(). Therefore it’s all handled by AX kernel and the obvious explanation is a delete action. As expected, there was a delete action from CustTable, a relation on AccountNum and no corresponding index on the custom table.

Every time you create a delete action, think about how AX will look for the record and whether an index isn’t needed.