Microsoft Dynamics AX [multiple versions] - Slow DOCUREF Delete Performance

When deleting DocuRef records, i.e. Document handling, the response can be much slower than expected.  This problem exists in 2012 and goes back to versions 2009, 4.0, and I believe even 3.0.

To understand this issue, one must first understand the relationship between DocuRef and DocuValue records.  When a DocuRef record has an attached file that is being stored in the database, that file is not stored directly on the DocuRef record but is inserted into a DocuValue record instead, and referenced by the DocuRef record using the ValueRecId field.  These DocuRef records can be copied, either automatically by the system under certain conditions, or manually by using the copy/paste function of Document handling.  When DocuRef records are copied, the copied records continue to point at the same DocuValue record, which is not copied.  Thus, a DocuValue can be referenced by many DocuRef records.

The challenge when deleting a DocuRef record that references a DocuValue record is to determine whether or not the associated DocuValue record should also be deleted.  In other words, if during the delete of a DocuRef record, a check must be be made to determine if the associated DocuValue record is referenced by any other DocuRef records, in which case the DocuValue record cannot be deleted yet.  This is also why DocuValue records are not simply cascade deleted from DocuRef.  The many-to-one relationship must be respected.

In the delete() method of DocuRef shown below, a call is first made to this.existsAnotherValue(), and then the DocuValue record itself is fetched, followed finally by a check to see if such a record even exists, i.e. DocuValue.RecId.  Technically, this is backwards.



The existsAnotherValue() method executes a query looking for other DocuRef records that reference the same ValueRecId.  Note that it does this even if the ValueRecId is 0, which indicates that no DocuValue record is referenced at all.  When ValueRecId is 0, such a check is meaningless.



There are two optimizations that can be implemented here.

First, no such check for another referencing DocuRef record should be done when ValueRecId is 0, and a simple if statement to completely avoid the database query is a huge performance improvement.  This is especially true considering that likely most DocuRef records do not reference a DocuValue record at all and have 0 for ValueRecId.  Let that sink in for a moment.  Most DocuRef records probably share the ValueRecId of 0, which means the method .existsAnotherValue() can return true even when no DocuValue record even exists.

Second, when such a check is actually necessary, it only matters whether at least one such record exists, so the query hints firstOnly and firstFast minimize that effort.



Together, these two changes highly optimize the performance of the delete() method on the DocuRef table.