Forceliterals - somewhat surprising results

A wee test of the forceLiterals keyword. I started by taking more or less the exact query from Dev III chapter 2. Then I added some more statements to the WHERE clause, in the hope of amplifying the discrepancy between using and not using this keyword. Here's how it shook down:

static void Nathan_ForceLiterals(Args _args)
{
    CustTrans ct;
    System.DateTime first, second;
    System.TimeSpan ts;
    int counter;
    str message;
    ;

    flush CustTrans;
    first = System.DateTime::get_Now();
    while select forceLiterals * from ct
        order by AccountNum
        where ct.TransDate >= mkDate(1, 1, 2010)
        && ct.AmountCur >= 10
        && ct.PostingProfile != ''
    {
        counter++;
    }
    second = System.DateTime::get_Now();
    ts = second.Subtract(first);
    message += ts.ToString();
    message += strFmt("\nCount is: %1\n", counter);


    flush CustTrans;
    first = System.DateTime::get_Now();
    while select * from ct
        order by AccountNum
        where ct.TransDate >= mkDate(1, 1, 2010)
        && ct.AmountCur >= 10
        && ct.PostingProfile != ''
    {
        counter++;
    }
    second = System.DateTime::get_Now();
    ts = second.Subtract(first);
    message += ts.ToString();
    message += strFmt("\nCount is: %1\n", counter);
    
    info(message);
}

The first time I ran it I got this:

00:00:02.0322032
Count is: 6091
00:00:02.1672167
Count is: 12182

I ran it a second time:

00:00:01.9231923
Count is: 6091
00:00:01.9531953
Count is: 12182

So, some DB caching probably smudging the results here. Well within the margin of error. Maybe one day I'll do some more analysis on the SQL Server side to see what queries came through. Another day...

Join comparison

This is a slight modification of the standard DAX Development III example of joins being used to increase performance. I expect the data set I've chose isn't ideal to highlight the concept, but it's adequate for my purpose, which is to demonstrate that the concept holds:

static void Nathan_Join(Args _args)
{
    System.DateTime first, second;
    System.TimeSpan ts;
    str dummy;
    LedgerTrans lTr;
    LedgerTable lTa;
    AmountMST amount;
    str message;
    ;

    flush LedgerTable;
    flush LedgerTrans;
    first = System.DateTime::get_Now();
    while select AccountNum from lTa where lTa.AccountName like 'Bank*'
    {
        while select lTr
            where lTr.accountNum == lTa.accountNum
        {
            amount += lTr.amountMST;
        }
    }
    second = System.DateTime::get_Now();
    ts = second.Subtract(first);
    message += ts.ToString();
    message += strFmt("\nNo join: %1\n", amount);

    amount = 0;
    flush LedgerTable;
    flush LedgerTrans;
    first = System.DateTime::get_Now();
    while select accountNum from lTa where lTa.AccountName like 'Bank*'
        join amountMST from lTr
        where lTr.accountNum == lTa.accountNum
    {
        amount += lTr.amountMST;
    }
    second = System.DateTime::get_Now();
    ts = second.Subtract(first);
    message += ts.ToString();
    message += strFmt("\nJoin amount: %1", amount);

    info(message);
}

Result:
00:00:02.5510000
No join: 36,289,571.52
00:00:02.1480000
Join amount: 36,289,571.52

Less that 100% of difference. Smaller scale of difference than the field list and aggregation testing. As mentioned, different data sets would produce different results. If performance matters, it's always worth profiling alternatives - and checking the result is consistent between all alternatives :-)

Note, I've rejigged the output approach so a single infolog message it produced. This is easier to copy/paste into here!

Aggregation comparison

We perform a while/select and compare that to using an aggregation function, count():

static void Nathan_Aggregation(Args _args)
{
    CustTable ct;
    System.DateTime first, second;
    System.TimeSpan ts;
    int counter;
    ;
   
    flush CustTable;
    first = System.DateTime::get_Now();
    while select * from ct
    {
        counter++;
    }
    second = System.DateTime::get_Now();
    ts = second.Subtract(first);
    info(ts.ToString());
    info(strFmt("Count of CustTable records using while-select: %1", counter));
    
    flush CustTable;
    first = System.DateTime::get_Now();
    select count(RecId) from ct;
    second = System.DateTime::get_Now();
    ts = second.Subtract(first);
    info(ts.ToString());
    info(strFmt("Count of CustTable records using count() aggregate function: %1", ct.RecId));
}

Output:
00:00:27.1730000
Count of CustTable records using while-select: 41904
00:00:00.6350000
Count of CustTable records using count() aggregate function: 41904

Performance improvement factor: 42.8

Compare field list performance to '*'

A quick comparison between the venerable field list and our old friend the '*':

static void Nathan_FieldList(Args _args)
{
   CustTable ct;
   System.DateTime first, second;
   System.TimeSpan ts;
   str dummy;
   ;
 
   flush CustTable;
   first = System.DateTime::get_Now();
   while select * from ct
   {
       dummy = ct.AccountNum;
   }
   second = System.DateTime::get_Now();
   ts = second.Subtract(first);
   info(ts.ToString());
  
  
   flush CustTable;
   first = System.DateTime::get_Now();
   while select AccountNum from ct
   {
       dummy = ct.AccountNum;
   }
   second = System.DateTime::get_Now();
   ts = second.Subtract(first);
   info(ts.ToString());
}

Results:
00:00:27.6584672
00:00:05.3869224

That's 5.1 times quicker using the field list. Not bad.

Display a millisecond timespan in Dynamics AX

Simple little job to get the juices flowing. It was nigh-on impossible to track down some code to do this natively within AX, they seem to have removed all decent tracing into some external tool. Not conducive to job-based hacking ;-)

static void Nathan_timeSpan(Args _args)
{
    CustTable ct;
    System.DateTime first, second;
    System.TimeSpan ts;
    ;
   
    first = System.DateTime::get_Now();
    sleep(123);
    second = System.DateTime::get_Now();
   
    ts = second.Subtract(first);
   
    info(ts.ToString());
}