Dynamics AX buf2Buf and buf2BufByName

One of the useful method from standard AX is the buf2Buf(), it behave similar to table's .data() method with one difference - buf2buf doesn't copy system field. Another reason of using buf2buf is copying of record from one company to another company. When using changeCompany(), the .data() method copy all field including DataAreaId and when insert(), DataAreaId field does not change, hence, the record is not inserted into the company indicated in the changeCompany(), here is where buf2Buf() comes in handy - copy all field except system field, then during insert(), let the system assign values to system field.


Sometimes, there're some functionality requires copy data from one table to another table which has similar structure (Eg. to history or logging table), in this case, the .data() and buf2Buf() cannot be used. But we can make some modification to the buf2Buf() method to copy based on field name instead of field Id.


Below are the two methods:
> buf2Buf() - Standard AX method
> buf2BufByName() - modified method

//Standard AX method, copy data based on field Id

static void buf2Buf(Common  _from, Common  _to)
{
    DictTable   dictTable = new DictTable(_from.TableId);
    fieldId     fieldId   = dictTable.fieldNext(0);


    while (fieldId && ! isSysId(fieldId))
    {
        _to.(fieldId)   = _from.(fieldId);
        fieldId         = dictTable.fieldNext(fieldId);
    }
}


//Modified method, copy data from one table to another table with similar structure

static void buf2BufByName(Common  _from, Common  _to)
{
    DictTable   dictTableFrom   = new DictTable(_from.TableId);
    DictTable   dictTableTo     = new DictTable(_to.TableId);
    DictField   dictFieldFrom;
    FieldId     fieldIdFrom     = dictTableFrom.fieldNext(0);
    FieldId     fieldIdTo
    ;


    while (fieldIdFrom && ! isSysId(fieldIdFrom))
    {
        dictFieldFrom   = new DictField(_from.TableId, fieldIdFrom);


        if(dictFieldFrom)
        {
            fieldIdTo = dictTableTo.fieldName2Id(dictFieldFrom.name());


            if(fieldIdTo)
                _to.(fieldIdTo) = _from.(fieldIdFrom);
        }


        fieldIdFrom = dictTableFrom.fieldNext(fieldIdFrom);
    }
}

SysImageResources

If you're looking for a way to view all the embedded image resources in Dynamics AX form, it is available at \Forms\SysImageResources.

Method to stop AOS when it stuck at "stopping" at Services

Sometimes when you stop AOS, it stuck at the “Stopping” state (Eg. like, forever), you can get it stopped by killing the Ax32Serv.exe.
This way, you don’t need to restart the whole server.

Steps:

1. Open Event Viewer
2. Filter with event Id “149”

3. Find the PID of the AOS you wanted to stop

4. Open Task Manager

5. Select the Ax32Serv.exe which has the same PID number as you found in Step #3

*If the “PID” column is not shown, just click on View > Select columns > check the PID > Click “OK”

6. End task for this program
7. Refresh the “Services” and you’ll see it stopped now

Dynamics AX regular expression - match()

Regular expression is a powerful tool for matching a text pattern, but it is not often seen in AX.

I've seen a developer coded a method today to validate a text string, return true if the text contains digit only (0-9), return false if either one of the character in the text contain non-digit, the method is ~6 lines long, find and loop through each character.

There's an easier way to achieve the same result - use of Regular expression.

The method can be simplified to 1 line.

This change, saved 0.31ms (from the trace captured), although the number looks small, but when this is executed millions time, it does cost a lot.

In the sample file I received, it imports around 20 million lines, given the 0.31ms saving, that reduce the execution time by:

> Time saved in ms x 20m lines = 6,200,000ms

> Time saved in seconds = 6,200 seconds

> Time saved in minutes = 103 minutes

> Time saved in hours = 1.72 hours

Using regular expression ===============================

//Return TRUE if '_text' contain digit only, otherwise return FALSE

boolean validateTextMatch(str _text)

{

    ;

    return !match("[^0-9]", _text);

}

Using strFind() & subStr() in loop =========================

boolean  validateTextLoop(str _text)

{

    int     counter, textLen = strlen(_text);

    Phone   validNumbers = '0123456789';

    ;

    for(counter = 1; counter <= textLen; counter ++)

    {

        if(!strfind(substr(_text,counter,1), validNumbers, 1, 10))

        {

            return false;

        }

    }

    return true;

}

Screenshot showing the trace time of both methods

For Regular expression syntax reference, visit this link: http://msdn.microsoft.com/en-us/library/aa886279.aspx


More usage of Trace Parser at these links:
http://mybhat.blogspot.co.uk/2011/11/performance-tuning-using-trace-parser.html
http://mybhat.blogspot.co.uk/2012/05/joining-permanent-table-with-temp-table.html

Dynamics AX - skipDataMethods & set based operation

Sometimes when importing large number of records (Eg. millions of lines), the quickest way is to use:

• insert_recordset
• update_recordset
• delete_from

But these set base operation will roll back to row-by-row operation when either of the following condition is true:

• it is not an SQL table (Eg. temporary table)
• Database log is enabled for the table
• Alert is setup for this table
• Record level security is enabled for the table
• AOSValidation method is overwritten
• when using insert_recordset
• the .insert() method is overwritten
• when using update_recordset
• the .update() method is overwritten
• when using delete_from
• the .delete() method is overwritten
• DeleteAction is defined

To prevent it from fallback to row-by-row operation, the following method can be used if:

• Delete action is defined, use skipDeleteActions
• Database log is setup, use skipDatabaseLog
• Alert is setup, use skipEvents
• Method is overloaded (.insert(), .update, .delete()), use skipDataMethods

Non-SQL table does not support set base operation.

NOTE: Only use these if it is logically correct to skip the row-by-row triggered operation (Eg. Database log, alert, RLS, overloaded method, etc)

Ref: http://msdn.microsoft.com/EN-US/library/aa849875

Execute SQL statement directly from Dynamics AX

This is not a recommended approach to run query against AX, but sometimes there's a need for it (Eg. stored procedure), below is the sample code to run SQL statement directly to the AX database (you'll need to do manual security check due to this approach will skip AX security validation).

I usually has a class with 2 simple method:

• executeUpdate
  Used to execute INSERT, UPDATE, DELETE statement
• executeQuery
  Used to execute SELECT statement

//Class :: SQLUtility ==========================================

class SQLUtility

{

}

//Method :: executeUpdate ======================================

server static int executeUpdate(str sql, Connection connection = new Connection())

{

    SqlStatementExecutePermission   sqlPerm;

    Statement                       statement;

    int                             rowsAffected;

    ;

    if(sql)

    {

        sqlPerm = new SqlStatementExecutePermission(sql);

        sqlPerm.assert();

        statement    = connection.createStatement();

        rowsAffected = statement.executeUpdate(sql);

        CodeAccessPermission::revertAssert();

    }

    return rowsAffected;

}

//Method :: executeQuery =======================================

server static ResultSet executeQuery(str sql, Connection connection = new Connection())

{

    SqlStatementExecutePermission   sqlPerm;

    Statement                       statement;

    ResultSet                       resultSet;

    ;

    sqlPerm = new SqlStatementExecutePermission(sql);

    sqlPerm.assert();

    statement = connection.createStatement();

    resultSet = statement.executeQuery(sql);

    CodeAccessPermission::revertAssert();

    return resultSet;

}

//Job :: Job_DirectSQLTest =====================================

static void Job_DirectSQLTest(Args _args)

{

    ResultSet       testRS;

    ;

    

    testRS = SQLUtility::executeQuery("select top 10 ItemId from InventTable");

    while(testRS.next())

    {

        info(testRS.getString(1));

    }

}

Result of running the job " Job_DirectSQLTest  "

Dynamics AX batch framework & parallelism

A good reading of Dynamics AX batch framework & parallelism.


Part I
http://blogs.msdn.com/b/axperf/archive/2012/02/24/batch-parallelism-in-ax-part-i.aspx


Part II
http://blogs.msdn.com/b/axperf/archive/2012/02/25/batch-parallelism-in-ax-part-ii.aspx


Part III
http://blogs.msdn.com/b/axperf/archive/2012/02/28/batch-parallelism-in-ax-part-iii.aspx


Part IV
http://blogs.msdn.com/b/axperf/archive/2012/03/01/batch-parallelism-in-ax-part-iv.aspx

Joining permanent table with temp table

Temporary table in AX4 and AX2009 is file base and only live during the execution session (AX2012 has an additional option to make it as SQL temp table). There's one key difference on query joining permanent table only and query joining the mix of permanent and temp table - The actual SQL statement sent to SQL server and its performance.


Writing code with the assumption it works the same could leads to badly perform code, hence, an understanding of how it works will helps avoid performance issue. This performance issue usually doesn't shows up during development due to small dataset, but after system go live, when table data grows over time, it might start showing up.


Given there're 3 tables:
> TableA: Contain 6 fields where Field1 is primary key
> TableB: Contain 3 fields where Field1 is primary key and foreign key to TableA
> TableC: Contain 1 field where Field1 is primary key and foreign key to TableA


The code below showing the same query, the first one is joining with all 3 table as permanent table and the second query is joining TableA & TableB as permanent table and TableC as temp table.

server static void testQuery()
{
    PerfTableA      perfTableA;
    PerfTableB      perfTableB;
    PerfTableC      perfTableC, perfTableCTemp;
    ;


    //All permanent table ======================================
    select perfTableA
      join perfTableB
     where perfTableB.Field1 == perfTableA.Field1
      join perfTableC
     where perfTableC.Field1 == perfTableA.Field1;


    //Mix of permanent & temp table ============================
    perfTableCTemp.setTmp();
    insert_recordset perfTableCTemp(Field1)
    select Field1 from perfTableC;


    select perfTableA
      join perfTableB
     where perfTableB.Field1     == perfTableA.Field1
      join perfTableCTemp
     where perfTableCTemp.Field1 == perfTableA.Field1;


    info("Test completed");
}



First query will have good performance due to SQL server will first filter TableC, reducing the result that join to TableA and TableC (refers Figure 5: Execution plan).



Actual SQL statement sent to SQL server


SELECT *
  FROM PERFTABLEA A,
       PERFTABLEB B,
       PERFTABLEC C 
 WHERE ( A.DATAAREAID='nl01') 
   AND ((B.DATAAREAID='nl01') 
   AND  (B.FIELD1=A.FIELD1) ) 
   AND ((C.DATAAREAID='nl01') 
   AND  (C.FIELD1=A.FIELD1))


Second query will have bad performance due to TableC is temp table, it is not part of the query sent to SQL Server, hence, SQL Server has to select from TableA & TableB, then returns a large dataset to AX, from AX further join with the temp table (TableC) to achieve the same result (refers Figure 5: Execution plan).


Actual SQL statement sent to SQL server


SELECT *
  FROM PERFTABLEA A,
       PERFTABLEB B 
 WHERE ( A.DATAAREAID='nl01') 
   AND ((B.DATAAREAID='nl01') 
   AND ( B.FIELD1=A.FIELD1))

Trace parser showing the actual query sent to SQL server (first query)

 


Trace parser showing the actual query sent to SQL server (second query)



The cost of first query (0.056144)



The cost of second query (4.48013)



Execution plan for both first and second query.
Notice the arrow thickness in execution plan on the right (2nd query), the thickness indicates the amount of data returns, the thicker the larger the rows and dataset.







Code used to populate test data

server static void populateData()
{
    #AviFiles
    PerfTableA              perfTableA;
    PerfTableB              perfTableB;
    PerfTableC              perfTableC;
    RecordInsertList        recordInsertListA, recordInsertListB, recordInsertListC;


    #define.upperLimit(100000)
    RandomGenerate          randomGen = new RandomGenerate();
    int                     counter;
    int                     modInt = real2int(#upperLimit/10);
    SysOperationProgress    progress = SysOperationProgress::newGeneral(#aviUpdate, 'Populate data', #upperLimit);
    ;


    delete_from perfTableA;
    delete_from perfTableB;
    delete_from perfTableC;


    recordInsertListA = new RecordInsertList(tableNum(perfTableA));
    recordInsertListB = new RecordInsertList(tableNum(perfTableB));
    recordInsertListC = new RecordInsertList(tableNum(perfTableC));


    for(counter = 1; counter <= #upperLimit; counter++)
    {
        //TableA ==========================================
        perfTableA.Field1 = int2str(counter);
        perfTableA.Field2 = int2str(randomGen.randomInt());
        perfTableA.Field3 = int2str(randomGen.randomInt());
        perfTableA.Field4 = int2str(randomGen.randomInt());
        perfTableA.Field5 = int2str(randomGen.randomInt());
        perfTableA.Field6 = int2str(randomGen.randomInt());
        recordInsertListA.add(perfTableA);


        //TableB ==========================================
        perfTableB.Field1 = int2str(counter);
        perfTableB.Field2 = int2str(randomGen.randomInt());
        perfTableB.Field3 = int2str(randomGen.randomInt());
        recordInsertListB.add(perfTableB);


        //TableC ==========================================
        if((counter mod modInt) == 0)
        {
            perfTableC.Field1 = perfTableA.Field1;
            recordInsertListC.add(perfTableC);
        }


        progress.incCount();
        progress.setText(strfmt("Populating: %1 of %2", counter, #upperLimit));
    }


    recordInsertListA.insertDatabase();
    recordInsertListB.insertDatabase();
    recordInsertListC.insertDatabase();
}

#Added 19/06/2012#
Post related to Trace Parser:
http://mybhat.blogspot.co.uk/2011/11/performance-tuning-using-trace-parser.html
http://mybhat.blogspot.co.uk/2012/06/dynamics-ax-regular-expression-match.html

SQL Server login timeout on first attempt, second immediate attempt success

Was having a login issue today with SQL Server after the installation.
There're 2 servers, one for AOS and another one for SQL Server.
Both server running Windows Server 2008 R2.
The SQL Server has 2 named instance (one SQL Server 2005 and another one SQL Server 2008 R2), firewall enabled, Named Pipes & TCP/IP has been enabled, port for SQL Server has been opened.
(Ref: http://support.microsoft.com/kb/968872)
SQL Server Browser service is running.


When starting up the AOS, it failed with the error message below: 
Object Server 12: Fatal SQL condition during login. Error message: "[Microsoft][ODBC SQL Server Driver]Timeout expired"


Trying to connect to the SQL Server using SSMS (from another server) gives this error:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)


First attempt to connect using SSMS failed with the above message, but on second attempt, it immediately connected, login success. It seems weird the login only timeout or failed on first attempt. This error can be consistently replicated, just close the SSMS, reopen and try login again, the error occur, then second login works.


Tried changed the "Remote Login Timeout" settings (Under SQL Server instance properties, on the "Advanced" page) to a higher number, but it doesn't help, the first login attempt still failed.


After further investigation, the reason was actually on the SQL Server Network Configuration.
The SQL Server is listen to dynamic ports.


Resolution: Change it to fixed port number
- Open SQL Server Configuration Manager
- Expand SQL Server Network Configuration
- Select the SQL Server instance you wanted to connect to
- Double click on TCP/IP
- Select the IP Addresses tab
- Under the IPAll group, remove the value in "TCP Dynamic Ports"
- Enter "1433" into "TCP" port (you can change the port according to your needs)


That resolve the issue.

SQL Server port settings

Standard AX data import & deletion

When using standard Dynamics AX 2009 import & export functionality for data migration, there's one thing that needs to be careful of - the default settings is deleting data before importing new data.

The deletion happens to the last two setup options:
> Clear table before inserting records
> Use definition group settings - "Delete and import"

This (deletion) is the default value on the table setup and during the import, unless it has been changed once and saved into usage data.

Unfortunately standard AX do the delete and import in two separate transaction.
Any error on import won’t rollback the transaction for the deletion.

Screenshot showing the transaction level, the deletion is in its own transaction.
DeleteAction is not executed as well, so no other table is affected.

Load test agent weighting

If you come across MSDN page regarding Load test agent weighting but still not sure about the load distribution when some are set to 100 and some to lower than that, below are the two example that should help explain more.

• Agent 1: weight 30. Agent 2: weight 70, Total users: 1000
  - Agent 1 will get 300 users
  - Agent 2 will get 700 users
  
• Agent 1: weight 50. Agent 2: weight 100, Total users: 1000
  - Agent 1 will get 333 users
  - Agent 2 will get 667 users
  
  For this example, the total weight is 150 (Agent 1 + Agent 2).
  Then get the percentage of each agent with this total weight, that leads to
  Agent 1 = 50/150 = 33.3333% of the total users = 333 users
  Agent 2 = 100/150 = 66.6667%  of the total users = 667 users
  

Screenshot of Load test agent properties

Case of bad performance due to EntireTable CacheLookup

Recently I was looking at an issue where changing a quantity in Purchase order line is taking up to a minute long. Tracing it and found the root cause was due to the CacheLookup property is set to EntireTable.
(This happens to intercompany order)


One of the code triggered from the PurchLine.PurchQty change is the code below which do an insert into ‘InterCompanyEndpointActionPolicyTransfer’ table.

This table has a property of CacheLookup = EntireTable, which its default behaviour is cache the entire table into memory if less than 128KB or write them to disk if more than 128KB.

A check into the data size of this table, it is around ~58MB, hence, each insert into this table is flushing the cache (for this table), then rewrite them all into the disc again.

In my case, changing this 'CacheLookup' property to "Found" resolve it.
The .insert() now completed in less than a second.

Microsoft Dynamics AX version and kernel/build number

Refer to this link for the Dynamics AX build number: 

AX2009 and above
http://blogs.msdn.com/b/axsupport/archive/2012/03/29/overview-of-ax-build-numbers.aspx

AX4 and below
http://blogs.msdn.com/b/emeadaxsupport/archive/2009/07/01/overview-ax-kernel-build-numbers.aspx

If you need to check what's the hotfix included in the rollup or build, click on the link on the "Comment" column.


Eg. AX2009 SP1 Roll Up 7: https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb$en-us$2503850


Last updated: 25/05/2012 (with new link to AX2009 and above)

Result of referencing field with inactive configuration key

Result of referencing field with inactive configuration key, the quick answer are:

• When used in query criteria, it ignore the criteria in AX and replace with another one in SQL
  
  From AX: Field1 == ''
  To SQL: 1==1
  
  From AX: Field1 == 'anyValue'
  To SQL: 1==0
  
  Empty string converted to 1 and a non-empty string converted to 0
• When referencing for its value, it just simply returns nothing/empty string

Below is an example of the test:

1. A configuration key – 'ADeletedConfig' (a disabled key)
2. A table with 3 fields created, 'Field1' has a configuration key which has been disabled (ADeletedConfig)
3. Table browser showing the existing records in the table
4. A job and its execution result

Table field with disabled configuration key

Showing records in table

Demo code

SQL statement from Trace Parser

Result of demo code execution

Add sales order confirmation into batch through code

One of the performance work done for our client is to create an additional button for quick order confirmation. They usually use all the default settings and don't make any changes during the prompt and most of the case they just post all the quantity. We retain the existing posting button and add another one for quick post (order confirmation). The main objective is just to let them post it then regain control and move on to the next task rather than spending time waiting for the prompt and click OK, then wait for the process to finish.


Without any code development, it can actually be done through the "Batch" button at the post Confirmation prompt, but the requirement we have is to complete the work in the least time possible, what we've done is just to bring this manual user process into code.


The usual posting can be done by 2 lines:

salesFormLetter = SalesFormLetter::construct(DocumentStatus::PackingSlip); 
salesFormLetter.update(......)


But that involve wait time, the user needs to wait till the posting to finish before they regain control and move on to the next task, so we modify it a bit to put the job into batch and return the control to user.

I've extracted the code into a job to show it here.
The "Init values" section is done as a setup in our solution, but I've replaced it with hard coded text here to shows the use of SalesFormLetter and add them into batch through code.


It is separated into 3 sections:

1. Initialize the values
2. Construct the SalesFormLetter class and pass in the required parameters & PrintJobSettings
3. Create the batch job and task

static void Job1(Args _args)
{
    SalesFormLetter     salesFormLetter;
    SalesId             salesId;
    SalesTable          salesTable;
    BatchInfo           batchInfo;
    BatchHeader         batchHeader;
    BatchGroupId        groupId;
    BatchCaption        batchInfoCaption, batchHeaderCaption;
    PrintJobSettings    printJobSettings;
    boolean             printToFile;
    NoYes               printReport;
    FileName            fileName;
    ;


    //Init values ==============================================================
    salesId             = "SO-101263";
    salesTable          = salesTable::find(salesId);
    fileName            = strFmt("\\\\serverName\\SOConfirmation_%1.pdf", salesId);
    groupId             = "TST";
    batchInfoCaption    = "Order confirmation posting: " + salesId;
    batchHeaderCaption  = "Order confirmation: " + salesId;
    printReport         = NoYes::Yes;
    printToFile         = false; //If 'printReport' is true, then this indicate print to file or printer


    //Construct SalesFormLetter and init values ================================
    salesFormLetter = SalesFormLetter::construct(DocumentStatus::Confirmation);
    salesFormLetter.salesTable(salesTable);
    salesFormLetter.transDate          (systemdateget());
    salesFormLetter.specQty            (SalesUpdate::All);
    salesFormLetter.proforma           (NoYes::No);
    salesFormLetter.printFormLetter    (printReport);
    salesFormLetter.printCODLabel      (NoYes::No);
    salesFormLetter.printFreightSlip   (NoYes::No);
    salesFormLetter.printShippingLabel (NoYes::No);
    salesFormLetter.usePrintManagement (false);
    salesFormLetter.creditRemaining    (salesFormLetter.creditRemaining());
    salesFormLetter.createParmUpdate(false);
    salesFormLetter.initParameters(salesFormLetter.salesParmUpdate(), Printout::Current);
    salesFormLetter.giroType(salesTable.GiroType);
    salesFormLetter.initLinesQuery();


    //PrintJobSettings
    printJobSettings = new PrintJobSettings(salesFormLetter.printerSettingsFormletter(PrintSetupOriginalCopy::Original));
    if(printToFile)
    {
        printJobSettings.setTarget(PrintMedium::File);
        printJobSettings.warnIfFileExists(false);
        printJobSettings.format(PrintFormat::PDF);
        printJobSettings.fileName(fileName);
    }
    else
    {
        printJobSettings.setTarget(PrintMedium::PrintArchive);
    }
    salesFormLetter.updatePrinterSettingsFormLetter(printJobSettings.packPrintJobSettings());


    //Init BatchInfo, BatchHeader, and add into batch job ======================
    batchInfo = salesFormLetter.batchInfo();
    batchInfo.parmBatchExecute(NoYes::Yes);
    batchInfo.parmCaption(batchInfoCaption);
    batchInfo.parmGroupId(groupId);
    batchInfo.parmRunClass(salesFormLetter);


    batchHeader = batchInfo.parmBatchHeader();
    batchHeader.parmCaption(batchHeaderCaption);
    batchInfo.doBatch();
}

Order confirmation added into batch job

Batch job waiting to be executed

Task within the batch job, waiting to be executed  

Task completed
Note: The order confirmation process added additional task to it when it executes. 

Report printed into the Print Archive

Test object is instantiated at server

Given an object where you doesn't know where it is instantiated, you can use the xGlobal class to test it.
xGlobal::objectOnServer(ioFlatFile)

Performance tuning using Trace Parser (Dynamics AX 2012)

On the text below, I'll be going through some traces and analysis using Trace Parser for Dynamics AX 2012.

It will covers:

1. RPC
2. Database call
3. Record caching
4. Method call
5. Set / row-by-row operation
6. Queries

I've created a demo class to shows the execution duration for each of the section mentioned above, below are the method name and its duration. You can download the XPO file from here.


Each pair of the method in the screenshot below has similar code, but only with a small difference, it makes it easier for you to do a comparison of how these small changes can make the code run faster. I'll go through each of that in details in this post.





NOTE: TEC_TraceDemo class 'RunOn' property has been set to run from server.




RPC (shows using file)


Both the .testRPCServer() and .testRPCClient() has similar code, except where the file is opened.


In .testRPCServer(), the file is opened from server side (static server AsciiIo openFileServer(Filename _fileName)), then returned to parent method and do the reading line by line. For each of that reading, due to the file is opened from server, there's no RPC call involved.






In .testRPCClient(), the file is opened from client side (static client AsciiIo openFileClient(Filename _fileName)), then returned to parent method (which is running from server) and do the reading line by line. For each of that reading, due to the file is opened from client, the server process needs to make an RPC call to client to read the file.

Comparing both of the methods above with the trace parser, you can see that:
- The .testRPCServer() has 0 RPC call and takes 8.33ms
- The .testRPCClient() has 204 RPC calls and takes 56.05ms


With a small change of opening the file at server where the code is running, it reduces the RPC calls and makes it run faster.


*The code sample above is using "C" drive as the directory, that's just for testing. If the code is running from server and the path selected by user is "C" drive of their own machine, it will cause error. Consider using shared directory.






RPC (shows using Map)

Both the .testMapLessRPC() and .testMapMoreRPC() has similar code, except how the map is used. The sample code below simulate & shows the situation when a Map is instantiated at a form, then passed to server for processing.


*TEC_MapTest 'RunOn' property is set to "Client"


In .testMapLessRPC(), the map is instantiated from client side, then it pack and return the map to server, recreate it from server side. After that it loop through the server map. Each call to read from the server map does not involve RPC call due to the map is on server side.






In .testMapMoreRPC(), the map is instantiated from client side, then it return the map to server. After that it loop through the client map. Each call to read from the client map involve RPC call due to the map is on client side and the calling code is from server side.

Comparing both of the methods above with the trace parser, you can see that:
- The .testMapLessRPC() has 6 RPC call and takes 3.29ms
- The .testMapMoreRPC() has 19 RPC calls and takes 5.52ms


*Create the map at server side does incurs some overhead, at the background, it actually insert the map one by one again from the packed container. But comparing to the cost of RPC call when looping through the client map, it might be better to recreate it. But if you don't intent to loop through all the element in the map, then probably client map remains better. It it entirely up what's the functionality you're writing, you'll have to make your own judgement. The example here is just to show what's the impact and how it can make a difference.







Database call


Both the .bulkInsertTraceDemoTable_RIL() and .bulkInsertTraceDemoTable_INS() has similar code, except how they're inserted.


In .bulkInsertTraceDemoTable_RIL(), records is added into RecordInsertList class and do the insert at once at the end. This reduce the database call. Periodically if the buffer limit of the RIL is reached, it will make a database call to insert and flush the buffer. Then at the end of the method, it do the final insert (one database call).






In .bulkInsertTraceDemoTable_INS(), records is inserted each time .insert() is call. Each of this .insert() make a database call to SQL server to do the insert.

Comparing both of the methods above with the trace parser, you can see that:
- The .bulkInsertTraceDemoTable_RIL() has 19 database call and takes 556.34ms
- The .bulkInsertTraceDemoTable_INS() has 1008 database calls and takes 2,780.07ms

Another way of viewing it is by looking at the 'X++/RPC' tab.
Eg. If you have done a trace for a poor performance functionality, using this tab, you can identify which method takes most of the execution time (sort by 'Total inclusive (ms)' column) or which method runs too many times (sort by 'Count' column). This gives you an idea of which one should be picked up for performance review & analysis.







Record caching


Both the .testRecordCache_Cached() and .testRecordCache_NotCached() has similar code, except one is reading from cached record and another one is not.


TEC_TraceDemoTable has a unique index on 'Field1' and doesn't have any index for 'Field3'


In .testRecordCache_Cached(), within the loop, the first read issue a database call and cached the record. Then on subsequent read, it read from cache, no database call needed. The ::dummyStatic() does not do anything, it is just to let it appear in the Trace Parser, make it easier to sees that it doesn't make a database call.

In between the ::dummyStatic(), the select statement didn't shows up due to it read from cache, doesn't make database call.

In .testRecordCache_NotCached(), within the loop, for each select, due to the query criteria 'Field3' does not have a unique index, it needs to make a database call to SQL server to read the data.

In between the ::dummyStatic(), the select statement shows up due to it needs to make a database call to SQL server to read it.

Comparing both of the methods above with the trace parser, you can see that:
- The .testRecordCache_Cached() has 1 database call and takes 48.59ms
- The .testRecordCache_NotCached() has 101 database calls and takes 247.02ms

Method call

Both the .testMethodCallInline() and .testMethodCallInner() has similar code, except one is running the counterBuffer++ within the loop and another one is running counterBuffer++ through another method.

In .testMethodCallInline(), it execute the 'counterBuffer++' within the loop, didn't involve passing control to another method.

In .testMethodCallInner(), it calls increaseValue() within the loop, which execute the 'counterBuffer++' for each of the call. For each of the loop, it pass the control to the child method, then back to parent method when it is finished.

Best practise is to have a separate method to do a specific task, then it can be reused somewhere else (Eg. the inner method, it usually is another method, I just write it as inner method to make it easier to read). But sometimes, on a performance intensive process where milliseconds does matter, we might have to break the rule. I'm not suggesting to always do that, the example here is just to show what difference it could make.


Comparing both of the methods above with the trace parser, you can see that:
- The .testMethodCallInline() takes 238.59ms
- The .testMethodCallInner() takes 974.41ms

Set / row by row operation

Both the .testSetOperation() and .testRowByRow() has exactly the same code except the table being used is different.


The .insert(), .update(), & .delete() method for table TEC_TraceDemoRowByRowTable has been overwritten but not for table TEC_TraceDemoSetOperationTable.







In .testSetOperation(), due to the .insert(), .update(), & .delete() has not been overwritten, it make one database call to do set operation. Eg. Insert 50 records, 1 database call.







In .testRowByRow(), the method .insert(), .update(), & .delete() has been overwritten, the set operation is downgraded to row-by-row operation and make one database call for each record it operate on. Eg. Insert 50 records, 50 database calls.


It will downgrade to row-by-row operation if either of the following conditions is true:
- insert() / update() / delete() is overwritten
- Is temp table
- Has database logging setup on that table
- Alert is set on that table
- Record level security is configured for that table






Comparing both of the methods above with the trace parser, you can see that:
- The .testSetOperation() has 10 database calls and takes 60.07ms
- The .testRowByRow() has 27 database calls and takes 184.28ms

Queries


In testQuery1(), 'Field1' is the unique index for table TEC_TraceDemoTable, any query with that as criteria will make it cache the record. 'Field5' is not unique index, query result with that criteria will not be cached.


Each query in the screenshot is explained by the comment next to it.







3 database call due to only one query can make use of the cached record.






Each query in the screenshot below is explained by the comment next to it.






1 database call due to 3 queries can make use of the cached record.







In .testQuery3(), both query has the same criteria, but with different field list.
First query only select 'ItemId' and 'InventDimId'.
Second query select all fields.







First query cost 0.36ms and second query cost 1.31ms.
By reducing the field list, it reduce the execution time.
Imagine if this is a validation section of an import process which imports 1 million records, reducing the field list will save 1,000,000ms, which translates to 16.67 minutes.






In .testQuery4(), both query has the same field list, but with different criteria.
I added a non-unique index in InventTrans table for 'InvoiceId'.
There's no index for field 'PickingRouteId'.


First query has the help from the index.
Second query doesn't have the help from any index.

With the help from index, the query takes 0.54ms.
Without the help from index, it takes 26.02ms.
This post focus on Trace Parser rather than on query tuning and indexing, so I'll skip that here.


Screenshot below shows how you can make use of Trace Parser to start looking into what needs to be optimized.







The SQL tab is where you can look for problematic query easily by sorting it in descending order.
Either sort it in descending by 'Inclusive Total (ms)' or 'Exclusive Total (ms)' column.







Summary of all 4 queries method.






Related (updated on 18/06/2012):

• Joining permanent table with temp table
  http://mybhat.blogspot.co.uk/2012/05/joining-permanent-table-with-temp-table.html
  
• Dynamics AX regular expression - match()
  http://mybhat.blogspot.co.uk/2012/06/dynamics-ax-regular-expression-match.html