Performance considerations: aggregate functions

It’s time for another quick performance tip. During reviews sometimes I come across code like this (table changed to protect the guilty):

    ProdTable prodTable;
    real total;
    ;
 
    while select prodTable
    {
        total += prodTable.QtySched;
    }
 
    return total;

Someone forgot that there’s such a thing as aggregate methods in queries. It’s a lot faster to let SQL Server do the work for you by calculating the sum in a single statement instead of fetching all records and do the calculation in Ax.

When I see something like that I recommend to rewrite it.

    ProdTable prodTable;
    real total;
    ;
 
    select sum(QtySched)
        from ProdTable;
 
    total = prodTable.QtySched;
 
    return total;

This is a lot faster. I ran both scenario’s 5000 times for 252 production orders in the USMF demo company on Ax 2012 R3.
aggregate_vs_loop_01

Quite a difference, no? Not only is it faster but it’s also more readable because it literally says what it does.

It was actually worse than this because the original code was part of an uncached display method on a table. Can you imagine the effect when it’s used in a grid on a form?

So for the love of insert deity of choice here please use aggregate functions when possible. It’s a low effort, low risk change that can make a noticeable difference.

Performance considerations: event handlers

Lately I’ve been involved quite a bit in performance analysis and tuning on Ax 2012. It’s not only important to solve the customer’s requirements but at the same time the solution must be implemented in a way that its processing ends before the heath death of the universe.

There’s are lot to say about performance but for now I’ll just stick to a quick heads-up about event handlers and delegates. And to boot I can demonstrate it without worrying about customer specific information.

Eventing was added to Ax 2012 but it’s not widely used in the standard code (if at all). Despite the potentially cleaner design, I’ve seen some instances where it caused some issues.

I decided to try to measure the overhead caused by eventing, ignoring whatever business logic they might perform. The results were beyond my expectations.

My test setup is a simple class with empty methods for different scenarios. The test was performed on an Ax 2012 R3 demo VM.

Class_EventHandlerTester

On the method runWithAOTSubscription a post event handler is defined that calls theAOTHandler. Method runWithDelegateSubscription calls theDelegate to which theDelegateMethod is subscribed using the eventHandler function. Lastly, runWithMethod directly calls theMethod without any events in between.

In main() every scenario is executed 100000 times.

public static void main(Args _args)
{
    EventHandlerTester eht = new EventHandlerTester();
    int i;
    int maxLoops = 100000;
    ;
 
    for(i=1; i<=maxLoops; i++)
    {
        eht.runWithAOTSubscription();
    }
 
    eht.theDelegate += eventhandler(EventHandlerTester::theDelegateMethod);
    for(i=1; i<=maxLoops; i++)
    {
        eht.runWithDelegateSubscription();
    }
 
    for(i=1; i<=maxLoops; i++)
    {
        eht.runWithMethod();
    }
}

Using the trace parser I found some interesting results. I ran it a couple of times and results were consistent.
EventHandlerTraceResults01

The event handlers subscribed using eventHandler are by far the worst. When looking at the call tree it’s fairly obvious why: there seems to be a lot of bookkeeping going on internally.
EventHandlerTraceResults02

Event handlers defined in the AOT are an improvement, even though it’s the only scenario which uses a parameter (XppPrePostArgs). This could make matters worse but actually it doesn’t.
EventHandlerTraceResults03

And as expected it’s still quicker when calling a method directly.
EventHandlerTraceResults04

Now why did I bother investigating this? Because I’ve seen the effect of using event handlers on data methods of often used tables. Even ignoring the body of the event handlers, the simple fact of calling the event handler has a noticeable performance cost. I’m not advocating against the use of event handlers but beware of them when they’re part of code involved in a performance problem.

If you’d like to test this yourself you can use the XPO. Let me know if you have other results or if my approach is flawed (it was getting late when I came up with the idea :)).

On hand inventory in code

Inventory in Ax is quite important. Often developers need to find out what the stock levels are in code. It’s tempting to write a query directly on the InventSum table and be done with it. This is usually not a good idea. If your code needs to deal with different inventory dimensions or you need to aggregate results on some random dimension, things can get complicated.

The good news is that there in standard Ax there already is a class to help you with this: InventDimOnHand. Many people have heard about it but don’t really know how to use it. It’s actually not that hard. Recently for a modification I had to get hold of physically available stock of an item per location. Naturally I ended up using InventDimOnHand to avoid getting lost in complex queries.

As an example I have made a simplified version of it. Try running it in the CEU company of the Contoso demo database.

static void demoInventOnHand(Args _args)
{
    InventDimOnHand         onHand;
    InventDimOnHandIterator iter;
    InventDimOnHandMember   member;
 
    ItemId                  itemId;
    InventDim               inventDimCrit;
    InventDimParm           inventDimParmCrit, inventDimParmOnHandLevel;
 
    InventDim               inventDim;
 
    InventDimOnHandLevel    level;
    ;
 
    itemId = '1509';
 
    // Known dimensions
    inventDimCrit.InventLocationId = '21';
    inventDimCrit = InventDim::findOrCreate(inventDimCrit);
 
    // Determine which of the known dimensions to use
    inventDimParmCrit.InventLocationIdFlag = true;
 
    level = InventDimOnHandLevel::DimParm;
 
    // Only matters for level DimParm.  Determines the level of detail returned
    inventDimParmOnHandLevel.ItemIdFlag = true;
    inventDimParmOnHandLevel.InventLocationIdFlag = true;
    inventDimParmOnHandLevel.WMSLocationIdFlag = true;
    inventDimParmOnHandLevel.InventBatchIdFlag = true;
 
    onHand = InventDimOnHand::newAvailPhysical(itemId, inventDimCrit, InventDimParmCrit, level, inventDimParmOnHandLevel);
 
    iter = onHand.onHandIterator();
    while (iter.more())
    {
        member = iter.value();
 
        inventDim = InventDim::find(member.parmInventDimId());
 
        info(con2str([member.parmItemId(), inventDim.inventLocationId, inventDim.wMSLocationId, inventDim.inventBatchId, member.parmInventQty()]));
 
        iter.next();
    }
 
    info('Done');
}

This prints a list of the available stock per batch number of the item in warehouse 21. If you change the level to Item or comment out lines for the WMS location and batch flags, you will get the total stock for the item in the warehouse.

Basically the first two parameters of newAvailPhysical() contain the item and dimensions for which you want to find the inventory. The InventDimParmCrit parameter determines on which of the known dimensions should be filtered during the lookup. This means it is possible to ignore values in InventDimCrit or force checks on empty values.

The last two parameters determine the level of detail of the stock. In this case each batch of this item in warehouse 21 is reported. You can check this in the on-hand inventory screen.

The InventDimOnHand class then uses a couple of other classes like InventDimOnHandMember and InventDimOnHandIterator to make it possible to get the actual values.

Feel free to change the values of the parameters passed to availPhysical() to see what happens. It is also possible to get something other than the physically available stock. Check out the other functions on the class.

Deleting items from a list

Just a quick one to point out a common pitfall when working with list objects. To remove objects from a list you need to use an iterator.

In X++ iterators require the developer to write a loop with calls to more() and next() to check if there are any elements left and make iterator advance to the next element.

Not so when you’re going to delete elements from the list. A call to delete() also advances to the next available element. It makes sense, but if you forget it could make you scratch your head for a few minutes.

So don’t write what I first wrote:

    list = new List(Types::String);
    list.addEnd('a'); list.addEnd('b'); list.addEnd('c'); list.addEnd('d');
 
    iter = new ListIterator(list);
 
    while (iter.more())
    {
        value = iter.value();
 
        if (true) // do a real check
        {
            iter.delete();
        }
 
        iter.next();
    }
 
    info(list.toString());

But rather do this:

    while (iter.more())
    {
        value = iter.value();
 
        if (true) // check value
        {
            iter.delete();
        }
        else
        {
            iter.next();
        }
    }
 
    info(list.toString());

Either do a delete() or a next(). Not both. The same goes for maps and sets.

And by the way, if you want to do anything other than delete elements it’s highly recommended you use enumerators instead of iterators. The reason is explained here on MSDN.

Fix for long AOS startup time

When you’re starting an AOS you may notice it takes a very long time before the service gets the status Started. A possible cause is left-over data in the SysClientSessions table. When starting up, the AOS seems to check if the client is still around somewhere for each record.

I have noticed that not it’s not always cleaned up properly after an AOS is shut down. If an AOS is acting up and needs to be restarted anyway I check the table on SQL Server and delete the client records before restarting the service.

Don’t just do this if multiple AOSes are set up for the same application. All instances should be shut down before cleaning up data like this.

Unfortunately I haven’t yet figured out how records get stuck in that table after a graceful shutdown in the first place. Of course, if it crashes the sessions won’t be erased either.

Of arrays and methods

In X++ arrays and methods don’t mix well. You can write a function that takes a parameter as an input value like this:

void arrayInput(str values[])
{
    int i;
    ;
 
    for (i=1; i<=dimOf(values); ++i)
    {
        info(values[i]);
    }
}

The compiler doesn’t object. Yet. Code calling this method just doesn’t compile.

static void main(Args _args)
{
    DemoArray c = new DemoArray();
    str v[];
    ;
 
    // ...
    c.arrayInput(v);  // Compiler says no
}

Writing a method that returns an array doesn’t work either. Because of the syntax of arrays in X++, with brackets following the variable name, there’s no decent way to define the return type.

str[] arrayOutput()  // Try defining an array return type here...
{
    str v[];
    ;
 
    v[1] = "a";
    v[2] = "ab";
    v[3] = "abc";
 
    return v;
}

However, there is a way around it. If you use an extended data type with several array elements the compiler won’t choke on it.
Screenshot of data type
The main drawback here is that the length of the array is fixed in the data type.

With the data type the code looks like this:

ValueArray arrayOutput()
{
    ValueArray v;
    ;
 
    v[1] = "a";
    v[2] = "ab";
    v[3] = "abc";
 
    return v;
}
 
void arrayInput(ValueArray values[])
{
    int i;
    ;
 
    for (i=1; i<=dimOf(values); ++i)
    {
        info(values[i]);
    }
}
 
static void main(Args _args)
{
    DemoArray c = new DemoArray();
    ValueArray v;
    ;
 
    v = c.arrayOutput();
 
    c.arrayInput(v);
}

It’s kind of weird but it works fine. Obviously the runtime can handle more than the compiler and X++ syntax allow. It’s a bit kludgy but it can save you a lot of work when confronted with legacy code.

Comparing records

Sometimes you need to know what’s the difference between records. I made a simple function to do just that. It’s an example of how to use reflection and field IDs on records.

It takes two records and returns a container with the field IDs and the values from both records. For simplicity I used a flattened container instead of more complicated data structures. Feel free to replace it with nested containers or some kind of collection.

I added this to the class Global for easy access.

public static container compareRecords(Common _record1, Common _record2)
{
    SysDictTable    dictTable = new SysDictTable(_record1.TableId);
    SysDictField    dictField;
    FieldId         fieldId, extFieldId;
    container       ret;
    int             i, j;
    ;
 
    if (_record1.TableId != _record2.TableId)
        return conNull();
 
    for (i=1; i<=dictTable.fieldCnt(); ++i)
    {
        fieldId = dictTable.fieldCnt2Id(i);
        dictField = new SysDictField(_record1.tableId, fieldId);
 
        if (!dictField.isSystem())
        {
            for (j=1; j<= dictField.arraySize(); ++j)
            {
                extFieldId = fieldId2Ext(fieldId, j);
 
                if (_record1.(extFieldId) != _record2.(extFieldId))
                {
                    ret += [extFieldId, _record1.(extFieldId), _record2.(extFieldId)];
                }
            }
        }
    }
 
    return ret;
}

As you can see it only compares records of the same type and skips system fields (e.g. RecId). Special care is taken to handle array fields correctly.

Using it is quite straightforward.

static void demoCompareRecords(Args _args)
{
    CustTable   custTable1 = CustTable::find('1101'); // CEE demo data
    CustTable   custTable2 = CustTable::find('1102'); // CEE demo data
 
    container   con;
    int         i;
    ;
 
    con = Global::compareRecords(custTable1, custTable2);
 
    for (i=1; i<=conLen(con); i+=3)
    {
        info(strFmt("%1: '%2'  '%3'"
                   ,fieldId2Name(tableNum(CustTable), conPeek(con, i))
                   ,conPeek(con, i+1)
                   ,conPeek(con, i+2)
                   )
             );
    }
}

Intrinsic function weirdness

Over at Dynamics Ax Daily I found a post about a compile error using tableNum() in a select statement.

As mentioned there, the following code doesn’t compile.

    select extCodeTable
        where extCodeTable.ExtCodeTableId == tableNum(CompanyInfo)
    join extCodeValueTable
        where extCodeValueTable.ExtCodeId == extCodeTable.ExtCodeId;

The compiler chokes on the call to tableNum(). I was surprised to see this, as I could have sworn that I have used tableNum() in select statements before. As it turns out it does compile in some cases.

It compiles without the join.

    select extCodeTable
        where extCodeTable.ExtCodeTableId == tableNum(CompanyInfo);

It also works if you add another where clause after tableNum().

    select extCodeTable
        where extCodeTable.ExtCodeTableId == tableNum(CompanyInfo)
             && extCodeTable.RecId != 0
    join extCodeValueTable
        where extCodeValueTable.ExtCodeId == extCodeTable.ExtCodeId;

And using a non-intrinsic function works too.

    select extCodeTable
        where extCodeTable.ExtCodeTableId == str2int("1234")
    join extCodeValueTable
        where extCodeValueTable.ExtCodeId == extCodeTable.ExtCodeId;

This example doesn’t make much sense with regards to business logic but it does compile.

I’m guessing this is a bug in the compiler. As far as I can tell it only fails when you use an intrinsic function before a join clause. Until it’s fixed just use a variable or throw in another where clause to check for RecId != 0. Since all records have a RecId it won’t affect the results you get back. This happens in Ax 4.0 and 2009.

All intrinsic functions are listed on MSDN.

Tricks with X++ embedded SQL

There are some tricks that you can do in X++ SQL. They’re not really well known because they’re not suited for normal use and there are not many clues in the editor that they exist. But they do come in handy every now and then.

Getting data without declaring a table variable

It’s possible to get data from the database without declaring any table variables. It goes something like this:

static void Job3(Args _args)
{
    // No variables!
    ;
 
    print (select CustTable).AccountNum;
    pause;
}

Note the parentheses around the select statement. This tells the compiler to create a temporary, anonymous buffer to hold the record. The select statement uses the exact table name and is immediately followed by a field name to get a value from a field.

If you use this there’s no Intellisense to help you look up the field name because the editor doesn’t know the type of the anonymous buffer.

This technique is often used in exist() methods on tables.

Getting a value from a field if you only know the field ID

Sometimes you don’t know the exact field name but you do know a field ID. Even in cases like this it’s possible to write an X++ SQL statement without knowing any field names.

Take a look at this example.

static void Job4(Args _args)
{
    CustTable   custTable;
    FieldId     fieldId;
    ;
 
    fieldId = fieldNum(CustTable, AccountNum); // This could be passed as a method parameter
 
    select custTable; // Get first record
 
    print custTable.(fieldId); // Print account number
 
    select custTable
        where custTable.(fieldId) == '1101';  // Where clause based on field ID
 
    print custTable.Name;
    pause;
}

It’s possible to use field IDs to retrieve values as well as use them in a where clause. Again parentheses are the key. Instead of writing a regular field name, wrap the ID in parentheses and you’re done. This makes it possible create generic code to handle any table and field without knowing the types at compile time. If you dig around in the classes for importing and exporting data you’ll find some examples.