Wednesday, February 24, 2010

Casing and text search/comparison tutorial

I have recently received a question from one of my blog readers.
He was asking about the possibility of doing case-sensitive search in AX.

I would like to reply to that by creating a small tutorial on search in AX. It is definitely not going to cover all the scenarios, but will give beginners a basic understanding of their options.

Download the tutorial xpo from my Skydrive

The tutorial consists of 1 form with 3 tab pages.

Comparing two strings in AX is very simple: You can basically use the equality operator "==". As you can see from the tutorial, this is the case-insensitive operations, so "vanya" is equal to "Vanya".
AX also supports case-sensitive comparison. Kernel function strCmp() compares two strings taking into account the casing of the symbols in the string.

Searching for a substring is a common operation in AX.
For that you have a number of options, as usual:
strScan() function ignores casing and allows you to specify from which position to search, and how many symbols. This is very basic, and a method like this is present in any programming language.
TextBuffer.find() is a more advanced use of the search mechanism. First of all, it allows to ignore or take into account the casing in the source text. Similar to strScan, it allows to specify the start position for the search.
What it also has is support for Regular Expressions, as well as the ability to, for example, paste the text to Windows clipboard.
Lastly, there is the match() function. The main purpose of it is to find a match based on the specified pattern using regular expressions, but nothing prevents using it for a simple search operation. It has a rather limited output though. You only get a boolean value stating whether a match was found or not, while with the previous 2 methods you also get the position of the substring.

An interesting discovery that I made when writing the tutorial was about the speed of the different search operations. I have included this into the tutorial, so you can go in and try it yourself on your specific setup.
On my box, strScan() was the slowest operation of all, while TextBuffer, which I considered to be a very heavy class, was performing rather well.

Of course, single operation time compared to database operations is very low, so you won't notice it in your daily work. But it is something to think about.

Finding a symbol in a string based on a specified set of symbols is also possible.
You have 2 functions at your disposal for that: strFind and strNFind. The difference is that strNFind searches for any symbol NOT present in the provided set, compared to strFind.

This is not an extensive list, so I would be interested in hearing which functions you use or what your results for performance comparison would be.

Tuesday, February 16, 2010

UtcDateTime in Dynamics AX 2009


Download the xpo for the tutorial from my SkyDrive

  • a grid, displaying data from CustTable
  • 4 buttons for various filtering actions
  • 3 controls that allow specifying the filtering conditions for the data

Dynamics AX UtcDateTime tutorial form


    SQL trace for Date filter on UtcDateTime field

    SQL trace for Time filter on UtcDateTime field

    SQL Trace for UtcDateTime range

Sunday, February 07, 2010

Performance optimization: Deleting inventory journal lines


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)

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.


  • 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