Category: Programming

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 :)).

Can’t define abstract or final methods on abstract table

It seems Ax doesn’t allow you to use abstract or final on abstract tables. When adding abstract public myMethod() to an abstract table (e.g. EcoResProduct), you get this compile error:

Conflicting access modifier given.

The error message doesn’t really make sense because the only access modifier specified is public. I’m not sure if this is intended behaviour or an issue with the compiler. There’s a whitepaper that mentions you can’t declare a table as final but it doesn’t say anything about the methods. In any case, a more meaningful error message would help.

This was discovered on Ax 2012 R2 CU7 but we could reproduce it in the Ax 2012 R3 CU8 demo machine.

To enforce overrides on derived tables you can still use the old school method like so:

public void myMethod()
{
    throw error(Error::missingOverride(funcName()));
}

Unexpected assertion failure caused by impossible modification

Today I was testing some modifications in the Ax 2012 project module and wanted to delete a test project I had just created. Much to my surprise I ended up in the debugger on a failed Debug::assert() call in DimensionEnabledType.constructForSystemDefinedByTableId().
Assertion failure screenshot
Ax was telling me there was a major problem with one of the dimensions and the delete couldn’t continue. I hadn’t even changed any of the dimensions and they were empty anyway. No one else had reported the problem either. The same action still worked fine in another environment. It just didn’t make sense.

It turns out the culprit was the backing table for the Department dimension: OMOperatingUnit. At the point where the assertion fails, it checks if the field group AutoIdentification contains the field that represents the dimension value. This should work because OMOperatingUnit is a descendant of DirPartyTable and that table has the Name field in the group.

It’s impossible to change that group on OMOperatingUnit, but somehow that group had ended up in the CUS layer and was causing problems. I can only guess why. There were some other minor modifications on the table but nothing related to the field groups.Field Group

Since I’m not allowed to edit that group on derived tables I couldn’t remove it. I finally exported the entire table from the AOT, deleted the object from the CUS layer and imported the XPO again. This restored the other modification and, lo and behold, no more field group in the CUS layer and no more problems when deleting a project.

Keeping an eye on output to files

Just a quick tip when you want to do some simple logging when jobs are running. Suppose you need to modify a lot of data and you want to log which records are updated and some other things. You could send it to the infolog window but its (default) limit of 10,000 lines may not be enough and you lose information. It also means you get the information after the job has finished.

For a data correction job I had to update some 20,000 records and couldn’t rely on the infolog for output. I decided to write the information to a file. The fun part is you can use Powershell to view content in real time as it’s being added to the file.

So I made the job with logging to a file, say c:\temp\logfile.csv. While the job was running, and Ax is blocked, I opened a Powershell prompt and issued this command:

get-content -path 'C:\temp\logfile.csv' -wait

It’s the -Wait parameter that makes it work like tail -f on a Unix system. I think this is a very simple and good enough solution for one-off jobs. Because we can do everything in Ax we can make it harder than it should be for things like this. Sometimes it’s easier to step outside the Ax box and use existing tools with little effort.

Inconsistent behavior between X++ and CIL using tables with inheritance

This one is a doozy. Recently a customer contacted me because they were having problems running a job they made themselves to export product information. It seemed to work fine in the client during development but the results when running in batch were very different.

Starting from a query on EcoResProduct they collect a lot of information and export it to a file which is picked up by another application. Sounds simple enough. It certainly is a lot easier than putting a fridge on a comet.

What they were trying to do came down to this:

    query = new Query();
 
    qbds = query.addDataSource(tableNum(EcoResProduct));
 
    queryRun = new QueryRun(query);
 
    while (queryRun.next())
    {
        ecoResProduct = queryRun.get(tableNum(EcoResProduct));
 
        // Look up information in related tables, sometimes using table ID
    }

Nothing extraordinary going on there. Until they put it in batch. As it turns out ecoResProduct.TableId was no longer correct and because of that they couldn’t find some related records.

While debugging I noticed that when running in batch the variable ecoResProduct was actually of type EcoResDistinctProduct. I could see it happening in Visual Studio. When running the job in my client and using the X++ debugger the variable was of type EcoResProduct.

Then it clicked for me: it looks like the CIL version of the code handles abstract base tables differently. It makes sense actually, because you can’t create instances of abstract types, only of derived concrete types. In X++ it is somehow supported for instances of table buffers to be abstract types. I decided to test my hypothesis with another infamous table using inheritance: DirPartyTable. I made a small class with a simple method to run a query on the table and get some records from it.

static container doStuff(container _params = conNull())
{
    DirPartyTable dirPartyTable;
    QueryRun queryRun;
    Query query;
    QueryBuildDataSource qbds;
 
    TableId tableId = tableNum(DirPartyTable);
 
    int i;
    ;
 
    query = new Query();
 
    qbds = query.addDataSource(tableId);
 
    queryRun = new QueryRun(query);
 
    while (queryRun.next())
    {
        dirPartyTable = queryRun.get(tableId);
 
        info(strFmt('Expected ID: %1, Real ID: %2',
                tableId, dirPartyTable.TableId));
 
        ++i;
 
        if (i > 5)
            break;
    }
 
    return conNull();
}

I then called the method in 2 ways:

server static private void runXpp()
{
    setPrefix(funcName());
    TestQueryTableId::doStuff();
}
server static private void runCIL()
{
    container ret;
    XppILExecutePermission  xppILExecutePermission;
    ;
 
    setPrefix(funcName());
 
    xppILExecutePermission = new XppILExecutePermission();
    xppILExecutePermission.assert();
 
    ret = runClassMethodIL(classStr(TestQueryTableId),
                        staticMethodStr(TestQueryTableId, doStuff),
                        conNull());
 
    CodeAccessPermission::revertAssert();
}

The results were exactly what I expected.

Info	TestQueryTableId.run\TestQueryTableId::runXpp	Expected ID: 2303, Real ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runXpp	Expected ID: 2303, Real ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runXpp	Expected ID: 2303, Real ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runXpp	Expected ID: 2303, Real ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runXpp	Expected ID: 2303, Real ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runXpp	Expected ID: 2303, Real ID: 2303

Info	TestQueryTableId.run\TestQueryTableId::runCIL	Expected ID: 2303, Real ID: 2377
Info	TestQueryTableId.run\TestQueryTableId::runCIL	Expected ID: 2303, Real ID: 2978
Info	TestQueryTableId.run\TestQueryTableId::runCIL	Expected ID: 2303, Real ID: 2978
Info	TestQueryTableId.run\TestQueryTableId::runCIL	Expected ID: 2303, Real ID: 2978
Info	TestQueryTableId.run\TestQueryTableId::runCIL	Expected ID: 2303, Real ID: 2975
Info	TestQueryTableId.run\TestQueryTableId::runCIL	Expected ID: 2303, Real ID: 2975

Luckily it can be fixed with SysDictTable::getRootTable().

        info(strFmt('Expected ID: %1, Real ID: %2, Fixed ID: %3',
                tableId, dirPartyTable.TableId, SysDictTable::getRootTable(dirPartyTable.TableId)));

Giving me this:

Info	TestQueryTableId.run\TestQueryTableId::runXpp	Expected ID: 2303, Real ID: 2303, Fixed ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runXpp	Expected ID: 2303, Real ID: 2303, Fixed ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runXpp	Expected ID: 2303, Real ID: 2303, Fixed ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runXpp	Expected ID: 2303, Real ID: 2303, Fixed ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runXpp	Expected ID: 2303, Real ID: 2303, Fixed ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runXpp	Expected ID: 2303, Real ID: 2303, Fixed ID: 2303

Info	TestQueryTableId.run\TestQueryTableId::runCIL	Expected ID: 2303, Real ID: 2377, Fixed ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runCIL	Expected ID: 2303, Real ID: 2978, Fixed ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runCIL	Expected ID: 2303, Real ID: 2978, Fixed ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runCIL	Expected ID: 2303, Real ID: 2978, Fixed ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runCIL	Expected ID: 2303, Real ID: 2975, Fixed ID: 2303
Info	TestQueryTableId.run\TestQueryTableId::runCIL	Expected ID: 2303, Real ID: 2975, Fixed ID: 2303

The problem was discovered in Ax 2012 R2 but I ran the test on and R3 demo machine and the results were exactly the same.

Quite confusing and time consuming to figure out what’s going on.

Ax 2012 X++ editor extensions

If you’re developing in Ax 2012 and you’re not yet using the editor extensions immediately stop what you’re doing and head over to Codeplex to download the extensions. Installing them is as easy as copying a couple of files to the Ax client directory.

They’re really useful and I’m a bit annoyed when I log on to a system where they’re not installed. Now stop reading and go.

Don’t forget the correct method signature when using runClassMethodIL()

If you think you know how something works, but really don’t and you’re not paying attention when errors pop up then you’re going to have a bad time.

So if you write this:

static private void myMethod()
{
    info(funcName());
}

Or this:

static private void myMethod(container _container)
{
    info(funcName());
}

Or this:

static private container myMethod()
{
    info(funcName());
    return conNull();
}

To use like this:

server static public void runIL()
{
    container ret;
    XppILExecutePermission  xppILExecutePermission;
    ;
 
    xppILExecutePermission = new XppILExecutePermission();
    xppILExecutePermission.assert();
 
    ret = runClassMethodIL(classStr(MyClass), staticMethodStr(MyClass, myMethod), conNull());
 
    CodeAccessPermission::revertAssert();
}

Then you’ll get this error at runtime and be confused. 

Error executing code: MyClass object does not have method 'myMethod'.

The method is right there, how can it not exist? You try some variations but the error remains. Confusion intensifies. After few minutes of grumbling and hasty attempts to fix it common sense kicks in and you understand the correct way to write it is:

static private container myMethod(container _container = conNull())
{
    info(funcName());
    return conNull();
}

Which makes perfect sense once you think about it because it’s the only way to get values in and out of the method. Of course, this could have been avoided by taking a step back and thinking a bit before typing.

Bonus tip for dealing with this runtime error:

Request for the permission of type 'XppILExecutePermission' failed.

Check if your method calling runClassMethodIL() and creating the permission (runIL() in my example) is actually running on server. If not, runClassMethodIL() won’t find the XppILExecutePermission object because it’s on another tier.

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.