Code highlighting

Sunday, February 07, 2010

Performance optimization: Deleting inventory journal lines

Preamble

As a developer, you should always consider performance implications of the code you write. In an ERP application like Microsoft Dynamics AX, the main focus should be on query execution, since it takes up the overwhelming part of the servers' resources.

You should always write queries that would execute the minimum amount of time and use the minimum amount of resources, at the same time producing the expected output in all cases.

Note, that performance is one of those things you cannot really verify on a 1-box install with a small test dataset. Most query problems show up only when tested with many users concurrently loading the AOS on a large-size database.

Code example

Table method
AOT\Data Dictionary\Tables\InventJournalTrans\Methods\delete
contains the following code:

if (this.Voucher)
{
if (this.numOfVoucherLines() == 0)
JournalError::deleteVoucher(tablenum(InventJournalTable),this.JournalId,this.Voucher);
}

The code is logically correct, deleting related records from the JournalError table, which contains error messages generated during validation and posting of journals.
But now let us consider the actual implementation. If we rephrase the conditions under which we delete the error message history, it would sound something like:
If a Voucher number is specified on the line being deleted, and there is no more lines in this journal that use this Voucher number, then we remove the JournalError records.

Deeper code analysis for performance

So, let's go in deeper, and open the code for method numOfVoucherLines:

Integer numOfVoucherLines()
{
return any2int((select count(RecId) from inventJournalTrans
where inventJournalTrans.JournalId == this.JournalId &&
inventJournalTrans.Voucher == this.Voucher).RecId);
}

As you can see, it counts all the journal lines with the specified Voucher number and JournalId.
Does it generate the expected output? Yes.
Is it optimal for the scenario in which it is used? No.

In order to determine if JournalError records can be deleted, we only need to know if at least 1 record with the specified Voucher number exists. So why do we need to search and count all such records? We don't.
Note that this code is being executed for each journal line, magnifying the impact of a non-optimal query N-fold.

Suggested solution

So, if we create a new method like this:

boolean voucherLineExist()
{
return (select firstfast firstonly forceplaceholders recId from inventJournalTrans
index hint VoucherIdx
where inventJournalTrans.journalId == this.journalId &&
inventJournalTrans.voucher == this.voucher).recId != 0;
}

and use it instead of numOfVoucherLines() in delete() method, we will greatly improve the overall performance when deleting journal lines.
It has been measured, that the time it takes to delete a journal with a large number of lines (88000, to be specific) has reduced by 6 times, which is fantastic.

Notes

  • forceplaceholders is used in the query explicitly to make sure a query plan is created and reused for this query, which further improves the performance, since the Voucher number most probably changes from line to line (depends on related journal name setup).
  • It is generally still a best practice to avoid creating inventory journals with such a large number of rows, because the journal is usually processed in 1 database transaction.

See also

10 comments:

  1. Hi Ivan! Thanks for the idea to be prudent with select.

    Just a quick questions, if I may.

    What did you change in the code of the original author?

    By the way, to format your code you can use http://www.manoli.net/csharpformat/.

    ReplyDelete
  2. I actually like the code formatting like the one I have. I have tried a couple of sites like the one you suggest, but it resulted in the inability to copy the code from the page.

    As for your question - the solution is exactly the same as on AxForum, nothing changed compared to that.
    Note, that in AX 2009 this code has still not been optimized. Hopefully, in AX6

    ReplyDelete
  3. maybe you know how I can set up your blog to receive new comments from it?

    ReplyDelete
  4. I have activated the Subscription widget on the right hand side. There is an option to subscribe to all comments with one of the RSS reader engines, but I haven't tried it, so I don't know if it works. We can try it out, if you want.

    ReplyDelete
  5. yes, it does! just got the response by email.

    ReplyDelete
  6. Cool. Thanks for trying it out.
    I wonder though, if you will receive comments from other unrelated threads, or only from the ones you participate in

    ReplyDelete
  7. Hi Vanya, as you´re a specialist in the inventory team, maybe you can have a solution to a problem that was not solved yet in the the dax forum.
    If possible, look at the http://dynamicsuser.net/forums/t/31336.aspx
    In fact the "table", "form" and PK are the own InventTable and ItemId. We´ve made huge customizations in these.
    Sorry if the doubt is not related to the post, but I wasn´t able to find another way to contact you.

    Thanks a lot
    Lucas

    ReplyDelete
  8. yes, it does! just got the response by email.

    ReplyDelete
  9. Hi Vanya,

    My name is Elena Dudau and I'm a Dynamics AX developer from Romania. I read your blog and I was interested if you faced with a similar issue I'm facing now. I'm working on a issues regarding the subsidiary items inventory Dimensions display. I noticed that sometime or allmost the time for the subsidiary items when I want to see the On hand> Overview screen from the Sales orders lines it's not "remembering" the setting I've done for the Dimensions display in a previous session. Have you encountered such a problem? Do you have any idea why is that possible to happen?

    Thank you in advance for your time.


    Best regards,
    Elena Dudau

    email: elena_dudau@yahoo.com
    skype: ciobanu_elena

    ReplyDelete

Please don't forget to leave your contact details if you expect a reply from me. Thank you