Display and edit methods are used on forms to display data that must be derived or calculated based on other information in the underlying table. These methods can be written on either the table or the form. By default, these methods are calculated one by one, and if there is a need to go to the server when one of these methods runs, as there usually is, each function goes to the server individually. The fields associated with these methods are recalculated every time a refresh is triggered on the form, which can occur when a user edits fields, uses menu items, or presses F5. Such a technique is expensive in both round trips and the number of calls that it places to the database from the Application Object Server (AOS).

Caching cannot be performed for display and edit methods that are declared on the data source for a form because the methods require access to the form metadata. If possible, you should move these methods to the table. For display and edit methods that are declared on a table, use the FormDataSource.cacheAddMethod method to enable caching. This method allows the form’s engine to calculate all the necessary fields in one round-trip to the server and then cache the results. To use cacheAddMethod, in the init method of a data source that uses display or edit methods, call cacheAddMethod on that data source and pass in the method string for the display or edit method. For example, look at the SalesLine data source of the SalesTable form. In the init method, you will find the following code:

public void init()
{
    super();
    salesLine_ds.cacheAddMethod(tableMethodStr(SalesLine, invoicedInTotal), false);
    salesLine_ds.cacheAddMethod(tableMethodStr(SalesLine, deliveredInTotal), false);
    salesLine_ds.cacheAddMethod(tableMethodStr(SalesLine, itemName), false);
    salesLine_ds.cacheAddMethod(tableMethodStr(SalesLine, timeZoneSite), true);
}

If you were to remove this code with comments, each display method would be computed for every operation on the form data source, increasing the number of round-trips to the AOS and the number of calls to the database server. For more information about cacheAddMethod, see http://msdn.microsoft.com/en-us/library/formdatasource.cacheaddmethod.aspx.

In Microsoft Dynamics AX 2009, Microsoft made a significant investment in the infrastructure of cacheAddMethod. In previous releases, this method worked only for display fields and only on form load. Beginning with Microsoft Dynamics AX 2009, the cache is used for both display and edit fields, and it is used throughout the lifetime of the form, including for reread, write, and refresh operations. It also works for any other method that reloads the data behind the form. With all of these methods, the fields are refreshed, but the kernel now refreshes them all at once instead of individually. In Microsoft Dynamics AX 2012, these features have been extended by another newly added feature—declarative display method caching.

You can use the declarative display method caching feature to add a display method to the display method cache by setting the CacheDataMethod property on a form control to Yes. Figure 13-1 shows the CacheDataMethod property.

Figure 13-1. The CacheDataMethod property.

The values for the new property are Auto, Yes, and No, with the default value being Auto. Auto equates to Yes when the data method is hosted on a read-only form data source. This primarily applies to list pages. If the same data method is bound to multiple controls on a form, if at least one of them equates to Yes, the method is cached.

RunBase classes form the basis for most business logic in Microsoft Dynamics AX. RunBase provides much of the basic functionality necessary to execute a business process, such as displaying a dialog box, running the business logic, and running the business logic in batches.

When business logic executes through the RunBase framework, the logic flows as shown in Figure 13-2.

Figure 13-2. The RunBase communication pattern.

Most of the round-trip problems of the RunBase framework originate with the dialog box. For security reasons, the RunBase class should be running on the server because it accesses a large amount of data from the database and writes it back. But a problem occurs when the RunBase class is marked to run on the server. When the RunBase class runs on the server, the dialog box is created and driven from the server, causing excessive round-trips.

To avoid these round-trips, mark the RunBase class to run on Called From, meaning that it will run on either tier. Then mark either the construct method for the RunBase class or the menu item to run on the server. Called From enables the RunBase framework to marshal the class back and forth between the client and the server without having to drive the dialog box from the server, which significantly reduces the number of round-trips. Keep in mind that you must implement the pack and unpack methods in a way that allows this serialization to happen.

For an in-depth guide to implementing the RunBase framework to handle round-trips optimally between the client and the server, refer to the Microsoft Dynamics AX 2009 white paper, “RunBase Patterns,” at http://www.microsoft.com/en-us/download/details.aspx?id=19517.

Microsoft Dynamics AX has a data caching framework on the client that can help you greatly reduce the number of times the client goes to the server. In Microsoft Dynamics AX, the cache operates across all of the unique keys in a table. Therefore, if a piece of code accesses data from the client, the code should use a unique key if possible. Also, you need to ensure that all unique keys are marked as such in the Application Object Tree (AOT). You can use the Best Practices tool to ensure that all of your tables have a primary key. For more information about the Best Practices tool, see Chapter 2.

Setting the CacheLookup property correctly is a prerequisite for using the cache on the client. Table 13-1 shows the possible values for CacheLookup. These settings are discussed in greater detail in the Caching section later in this chapter.

An index can be cached only if the where clause contains column names that are unique. The unique index join cache is a new feature that is discussed later in this chapter (see The unique index join cache in the Transaction performance section later in this chapter). This cache supports 1:1 relations only. In other words, caching won’t work if a 1:n join is present or if the query is a cross-company query. In Microsoft Dynamics AX 2012, even if range operations are in the query, caching is supported so long as there is a unique key lookup in the query.

A cache that is set to EntireTable stores the entire contents of a table on the server, but the cache is treated as a Found cache on the client. For tables that have only one row for each company, such as parameter tables, add a key column that always has a known value, such as 0. This allows the client to use the cache when accessing these tables. For an example of the use of a key column in Microsoft Dynamics AX, see the CustParameters table.

Temporary tables can be a common source of both client callbacks and calls to the server. Unlike regular table buffers, temporary tables are located on the tier on which the first record was inserted. For example, if a temporary table is declared on the server and the first record is inserted on the client, while the rest of the records are inserted on the server, all access to that table from the server happens on the client.

It’s best to populate a temporary table on the server because the data that you need is probably coming from the database. Still, you must be careful when you want to iterate through the data to populate a form. The easiest way to achieve this efficiently is to populate the temporary table on the server, serialize the entire table to a container, and then read the records from the container into a temporary table on the client.

Avoid joining inMemory temporary tables with regular database tables whenever possible, because the AOS will first fetch all of the data in the database table of the current company and then combine the results in memory. This is an expensive, time-consuming process.

Try to avoid the type of code shown in the following example:

public static server void ImMemTempTableDemo()
{
    RealTable rt;
    InMemTempTable tt;
    int i;

    // Populate temp table
    ttsBegin;
    for (i=0; i<1000; i++)
    {
        tt.Value = int2str(i);
        tt.insert();
    }
    ttsCommit;
    // Inefficient join to database table. If the temporary table is an inMemory
    // temp table, this join causes 1,000 select statements on the database table and with
    // that, 1,000 round-trips to the database.

    select count(RecId) from tt join rt where tt.value == rt.Value;
    info(int642str(tt.Recid));
}

If you decide to use inMemory temporary tables, indexing them correctly for the queries that you plan to run on them will improve performance significantly. There is one difference compared to indexing for queries against regular tables: the fields must be in the same order as in the query itself. For example, the following query will benefit significantly from an index on the AccountMain, ColumnId, and PeriodCode fields in the TmpDimTransExtract table:

SELECT SUM(AmountMSTDebCred) FROM TmpDimTransExtract WHERE ((AccountMain>=N'11011201' AND
AccountMain<=N'11011299')) AND ((ColumnId = 1)) AND ((PeriodCode = 1))

You can use TempDB temporary tables to replace inMemory temporary table structures easily. TempDB temporary tables have the following advantages over InMemory temporary tables:

To create a TempDB temporary table, set the TableType property to TempDB, as shown in Figure 13-3.

Figure 13-3. Use the TableType property to create a TempDB temporary table.

If you use TempDB temporary tables, don’t populate them by using line-based operations, as shown in the following example:

public static server void SQLTempTableDemo1()
{
    SQLTempTable tt;
    int i;

    // Populate temporary table; this will cause 1,000 round-trips to the database

    ttsBegin;
    for (i=0; i<1000; i++)
    {
        tt.Value = int2str(i);
        tt.insert();
    }
    ttsCommit;
}

Instead, use set-based operations. The following example shows how to use a set-based operation to create an efficient join to a database table:

public static server void SQLTempTableDemo2()
{
    RealTable rt;
    SQLTempTable tt;

    // Populate the temporary table with only one round-trip to the database.

    ttsBegin;
    insert_recordset tt (Value)
        select Value from rt;
    ttsCommit;

    // Efficient join to database table causes only one round-trip. If the temporary table
    // is an inMemory temp table, this join would cause 1,000 select statements on the
    // database table.

    select count(RecId) from tt join rt where tt.value == rt.Value;
    info(int642str(tt.Recid));
}

A client callback occurs when the client places a call to a server-bound method and the server then places a call to a client-bound method. These calls can happen for two reasons. First, they occur if the client doesn’t send enough information to the server during its call or if the client sends the server a client object that encapsulates the information. Second, they occur when the server is either updating or accessing a form.

To eliminate the first kind of callback, ensure that you send all of the information that the server needs in a serializable format, such as packed containers or value types (for example, int, str, real, or boolean). When the server accesses these types, it doesn’t need to go back to the client the way that it does if you use an object type.

To eliminate the second type of callback, send any necessary information about the form to the method, and manipulate the form only when the call returns, instead of directly from the server. One of the best ways to defer operations on the client is by using the pack and unpack methods. With pack and unpack, you can serialize a class to a container and then deserialize it at the destination.

To ensure the minimum number of round-trips between the client and the server, group calls into one static server method and pass in the state necessary to perform the operation.

The NumberSeq::getNextNumForRefParmId method is an example of a static server method that is used for this purpose. This method call contains the following line of code:

return NumberSeq::newGetNum(CompanyInfo::numRefParmId()).num();

If this code ran on the client, it would cause four remote procedure call (RPC) round-trips: one for newGetNum, one for numRefParmId, one for num, and one to clean up the NumberSeq object that was created. By using a static server method, you can complete this operation in one RPC round-trip.

Another common example of grouping calls into chunks occurs when the client performs transaction tracking system (TTS) operations. Frequently, a developer writes code similar to that in the following example:

ttsBegin;
record.update();
ttsCommit;

You can save two round-trips if you group this code into one static server call. All TTS operations are initiated only on the server. To take advantage of this, do not invoke the ttsbegin and ttscommit call from the client to start the database transaction when the ttslevel is 0.

The global methods buf2con and con2buf are used in X++ to convert table buffers into containers, and vice versa. New functionality has been added to these methods, and they have been improved to run much faster than in previous versions of Microsoft Dynamics AX.

Converting table buffers into containers is useful if you need to send the table buffer across different tiers; (for example, between the client and the server). Sending a container is better than sending a table buffer because containers are passed by value and table buffers are passed by reference. Passing objects by reference across tiers causes a high number of RPC calls and degrades the performance of your application. Referencing objects that were created on different tiers causes an RPC call every time the other tier invokes one of the instance methods of the remote object. To improve performance, you can eliminate a callback by creating local copies of the table buffers, using buf2con to pack the table and con2buf to unpack it.

The following example shows a form running on the client and transferring data to the server for updating. The example illustrates how to transfer a buffer efficiently with a minimum number of RPC calls:

public void updateResultField(Buf2conExample   clientRecord)
{
    container   packedRecord;

    // Pack the record before sending to the server

    packedRecord = buf2Con(clientRecord);

    // Send packed record to the server and container with the result

    packedRecord = Buf2ConExampleServerClass::modifyResultFromPackedRecord(packedRecord);

    // Unpack the returned container into the client record.

    con2Buf(packedRecord, clientRecord);
    Buf2conExample_ds.refresh();
}

Modify the data on the server tier and then send a container back:

public static server container  modifyResultFromPackedRecord(container _packedRecord)
{
    Buf2conExample recordServerCopy = con2Buf(_packedRecord);
    Buf2ConExampleServerClass::modifyResult(recordServerCopy);
    return buf2Con(recordServerCopy);
}
public static server void modifyResult(Buf2conExample  _clientTmpRecord)
{
    int n = _clientTmpRecord.A;
    _clientTmpRecord.Result = 0;
    while (n > 0)
    {
        _clientTmpRecord.Result = Buf2ConExampleServerClass::add(_clientTmpRecord);
        n--;
    }
}

A set-based operation such as insert_recordset, update_recordset, or delete_from is not downgraded to a record-based operation on a subtype or supertype table unless a condition that would cause the operation to be downgraded is met. Both an insert_recordset and update_recordset can update or insert all qualifying records into the specified table and all subtype and supertype tables, but not into any derived tables. The delete_from operator is treated differently because it deletes all qualifying records from the current table and its subtype and supertype tables to guarantee that the record is deleted completely from the database. For more information about the conditions that cause a downgrade, review the following sections.

The insert_recordset operator enables the insertion of multiple records into a table in one round-trip to the database. The following X++ code illustrates the use of insert_recordset. The code copies entries for one item in the InventTable table and the InventSum table into a temporary table for future use:

static void CopyItemInfo(Args _args)
{
    InventTable                inventTable;
    InventSum                  inventSum;
    InsertInventTableInventSum insertInventTableInventSum;

    // insert_recordset uses only one round-trip for the copy operation.
    // A record-based insert would need one round-trip per record in InventSum.

    ttsBegin;
    insert_recordset insertInventTableInventSum (ItemId,AltItemId,PhysicalValue,PostedValue)
        select ItemId,AltItemid from inventTable where inventTable.ItemId == '1001'
        join PhysicalValue,PostedValue from inventSum
        where inventSum.ItemId == inventTable.ItemId;
    ttsCommit;
    select count(RecId) from insertInventTableInventSum;
    info(int642str(insertInventTableInventSum.RecId));

    // Additional code to use the copied data.
}

The round-trip to the database involves the execution of three statements in the database:

This approach has a tremendous performance advantage over inserting the records one by one, as shown in the following X++ code, which addresses the same scenario:

static void CopyItemInfoLineBased(Args _args)
{
    InventTable                inventTable;
    InventSum                  inventSum;
    InsertInventTableInventSum insertInventTableInventSum;

    ttsBegin;
    while select ItemId,Altitemid from inventTable where inventTable.ItemId == '1001'
        join PhysicalValue,PostedValue from inventSum
        where inventSum.ItemId == inventTable.ItemId
    {
        InsertInventTableInventSum.ItemId        = inventTable.ItemId;
        InsertInventTableInventSum.AltItemId     = inventTable.AltItemId;
        InsertInventTableInventSum.PhysicalValue = inventSum.PhysicalValue;
        InsertInventTableInventSum.PostedValue   = inventSum.PostedValue;
        InsertInventTableInventSum.insert();
    }
    ttsCommit;

    select count(RecId) from insertInventTableInventSum;
    info(int642str(insertInventTableInventSum.RecId));

    // ... Additional code to use the copied data
}

If the InventSum table contains 10 entries for which ItemId equals 1001, this scenario would result in one round-trip for the select statement and an additional 10 round-trips for the inserts, totaling 11 round-trips.

The insert_recordset operation can be downgraded from a set-based operation to a record-based operation if any of the following conditions is true:

The Microsoft Dynamics AX run time automatically handles the downgrade and internally executes a scenario similar to the while select scenario shown in the preceding example.

public void insert()
{
    super();
}

Unless a table is cached by using the EntireTable setting, you can avoid the downgrade caused by the other conditions mentioned earlier. The record buffer contains methods that turn off the checks that the run time performs when determining whether to downgrade the insert_recordset operation:

The following X++ code, which includes the call to skipDataMethods(true), ensures that the insert_recordset operation is not downgraded because the insert method is overridden on the InventSize table:

static void CopyItemInfoskipDataMethod(Args _args)
{
    InventTable                inventTable;
    InventSum                  inventSum;
    InsertInventTableInventSum insertInventTableInventSum;

    ttsBegin;

    // Skip override check on insert.

    insertInventTableInventSum.skipDataMethods(true);
    insert_recordset insertInventTableInventSum (ItemId,AltItemId,PhysicalValue,PostedValue)
        select ItemId,Altitemid from inventTable where inventTable.ItemId == '1001'
        join PhysicalValue,PostedValue from inventSum
        where inventSum.ItemId == inventTable.ItemId;
    ttsCommit;

    select count(RecId) from insertInventTableInventSum;
    info(int642str(insertInventTableInventSum.RecId));

    // ... Additional code to use the copied data
}

If you override the insert method, use the cross-reference system to determine whether any X++ code calls skipDataMethods(true). If you don’t, the X++ code might fail to execute the insert method. Moreover, when you implement calls to skipDataMethods(true), ensure that data inconsistency will not result if the X++ code in the overridden insert method doesn’t execute.

You can use skip methods only to influence whether the insert_recordset operation is downgraded. If you call skipDataMethods(true) to prevent a downgrade because the insert method is overridden, use the Microsoft Dynamics AX Trace Parser to make sure that the operation has not been downgraded. The operation is downgraded if, for example, the database log is configured to log inserts into the table. In the previous example, the overridden insert method on the InventSize table would be executed if the database log were configured to log inserts into the InventSize table, because the insert_recordset operation would then revert to a while select scenario in which the overridden insert method would be called. For more information about the Trace Parser, see the section Performance monitoring tools later in this chapter.

Since the Microsoft Dynamics AX 2009 release, the insert_recordset operator has supported literals. Support for literals was introduced primarily to support upgrade scenarios in which the target table is populated with records from one or more source tables (using joins) and one or more columns in the target table must be populated with a literal value that doesn’t exist in the source. The following code example illustrates the use of literals in insert_recordset:

static void CopyItemInfoLiteralSample(Args _args)
{
    InventTable                inventTable;
    InventSum                  inventSum;
    InsertInventTableInventSum insertInventTableInventSum;
    boolean                    flag = boolean::true;

    ttsBegin;
    insert_recordset insertInventTableInventSum
(ItemId,AltItemId,PhysicalValue,PostedValue,Flag)
        select ItemId,altitemid from inventTable where inventTable.ItemId == '1001'
        join PhysicalValue,PostedValue,Flag from inventSum
        where inventSum.ItemId == inventTable.ItemId;
    ttsCommit;

    select firstonly ItemId,Flag from insertInventTableInventSum;
    info(strFmt('%1,%2',insertInventTableInventSum.ItemId,insertInventTableInventSum.Flag));
    // ... Additional code to utilize the copied data
}

The behavior of the update_recordset operator is similar to that of the insert_recordset operator. This similarity is illustrated by the following piece of X++ code, in which all rows that have been inserted for one ItemId are updated and flagged for further processing:

static void UpdateCopiedData(Args _args)
{

    InventTable                inventTable;
    InventSum                  inventSum;
    InsertInventTableInventSum insertInventTableInventSum;

    // Code assumes InsertInventTableInventSum is populated.

    // Set-based update operation.
    ttsBegin;
    update_recordSet insertInventTableInventSum setting Flag = true
    where insertInventTableInventSum.ItemId == '1001';
    ttsCommit;
}

The execution of update_recordset results in one statement being passed to the database—which in Transact-SQL uses syntax similar to UPDATE <table> <SET> <field and expression list> WHERE <predicates>. As with insert_recordset, update_recordset provides a tremendous performance improvement over the record-based version that updates each record individually. This improvement is shown in the following X++ code, which serves the same purpose as the preceding example. The code selects all of the records that qualify for update, sets the new description value, and updates the record:

static void UpdateCopiedDataLineBased(Args _args)
{

    InventTable                inventTable;
    InventSum                  inventSum;
    InsertInventTableInventSum insertInventTableInventSum;

    // ... Code assumes InsertInventTableInventSum is populated

    ttsBegin;
    while select forUpdate InsertInventTableInventSum
    where insertInventTableInventSum.ItemId == '1001'
    {
        insertInventTableInventSum.Flag = true;
        insertInventTableInventSum.update();
    }
    ttsCommit;
}

If ten records qualify for the update, one select statement and ten update statements are passed to the database, rather than the single update statement that would be passed with update_recordset.

The update_recordset operation can be downgraded if specific methods are overridden or if Microsoft Dynamics AX is configured in specific ways. The update_recordset operation is downgraded if any of the following conditions is true:

The Microsoft Dynamics AX run time automatically handles the downgrade and internally executes a scenario similar to the while select scenario shown in the earlier example.

As with the insert_recordset operator, you can avoid a downgrade unless the table is cached by using the EntireTable setting. The record buffer contains methods that turn off the checks that the run time performs when determining whether to downgrade the update_recordset operation:

As explained earlier, use the skip methods with caution. Again, using the skip methods only influences whether the update_recordset operation is downgraded to a while select operation. If the operation is downgraded, database logging, alerting, and execution of overridden methods occur even though the respective skip methods have been called.

Microsoft Dynamics AX supports inner and outer joins in update_recordset. The support for joins in update_recordset enables an application to perform set-based operations when the source data is fetched from more than one related data source.

The following example illustrates the use of joins with update_recordset:

static void UpdateCopiedDataJoin(Args _args)
{
    InventTable                inventTable;
    InventSum                  inventSum;
    InsertInventTableInventSum insertInventTableInventSum;

    // ... Code assumes InsertInventTableInventSum is populated
    // Set-based update operation with join.

    ttsBegin;
    update_recordSet insertInventTableInventSum setting Flag = true,
    DiffAvailOrderedPhysical = inventSum.AvailOrdered - inventSum.AvailPhysical
    join InventSum where inventSum.ItemId == insertInventTableInventSum.ItemId &&
    inventSum.AvailOrdered > inventSum.AvailPhysical;
    ttsCommit;
}

The delete_from operator is similar to the insert_recordset and update_recordset operators in that it passes a single statement to the database to delete multiple rows, as shown in the following code:

static void DeleteCopiedData(Args _args)
{
    InventTable                inventTable;
    InventSum                  inventSum;
    InsertInventTableInventSum insertInventTableInventSum;

    // ... Code assumes InsertInventTableInventSum is populated
    // Set-based delete operation

    ttsBegin;
    delete_from insertInventTableInventSum
    where insertInventTableInventSum.ItemId == '1001';
    ttsCommit;
}

This code passes a statement to Microsoft SQL Server in a syntax similar to DELETE <table> WHERE <predicates> and performs the same actions as the following X++ code, which uses record-by-record deletes:

static void DeleteCopiedDataLineBased(Args _args)
{

    InventTable                inventTable;
    InventSum                  inventSum;
    InsertInventTableInventSum insertInventTableInventSum;

    // ... Code assumes InsertInventTableInventSum is populated

    ttsBegin;
    while select forUpdate insertInventTableInventSum
    where insertInventTableInventSum.ItemId == '1001'
    {
        insertInventTableInventSum.delete();
    }
    ttsCommit;
}

Again, the use of delete_from is preferable for performance because a single statement is passed to the database, instead of the multiple statements that the record-by-record version parses.

As with the insert_recordset and update_recordset operations, the delete_from operation can be downgraded—and for similar reasons. A downgrade occurs if any of the following conditions is true:

A downgrade also occurs if delete actions are defined on the table. The Microsoft Dynamics AX run time automatically handles the downgrade and internally executes a scenario similar to the while select operation shown in the earlier example.

You can avoid a downgrade caused by these conditions unless the table is cached by using the EntireTable setting. The record buffer contains methods that turn off the checks that the run time performs when determining whether to downgrade the delete_from operation, as follows:

The preceding descriptions about the use of the skip methods, the no-skipping behavior in the event of downgrade, and the concurrency model for the update_recordset operator are equally valid for the use of the delete_from operator.

In addition to the set-based operators, you can use the RecordInsertList and RecordSortedList classes when inserting multiple records into a table. When the records are ready to be inserted, the Microsoft Dynamics AX run time packs multiple records into a single package and sends it to the database. The database then executes an individual insert operation for each record in the package. This process is illustrated in the following example, in which a RecordInsertList object is instantiated, and each record to be inserted into the database is added to the RecordInsertList object. When all records are inserted into the object, the insertDatabase method is called to ensure that all records are inserted into the database.

static void CopyItemInfoRIL(Args _args)
{
    InventTable                      inventTable;
    InventSum                        inventSum;
    InsertInventTableInventSumRT     insertInventTableInventSumRT;
    RecordInsertList                 ril;

    ttsBegin;
    ril = new RecordInsertList(tableNum(InsertInventTableInventSumRT));

    while select ItemId,AltItemid from inventTable where inventTable.ItemId == '1001'
    join PhysicalValue,PostedValue from inventSum
    where inventSum.ItemId == inventTable.ItemId
    {
        insertInventTableInventSumRT.ItemId        = inventTable.ItemId;
        insertInventTableInventSumRT.AltItemId     = inventTable.AltItemId;
        insertInventTableInventSumRT.PhysicalValue = inventSum.PhysicalValue;
        insertInventTableInventSumRT.PostedValue   = inventSum.PostedValue;
        // Insert records if package is full
        ril.add(insertInventTableInventSumRT);
    }

    // Insert remaining records into database

    ril.insertDatabase();
    ttsCommit;

    select count(RecId) from insertInventTableInventSumRT;
    info(int642str(insertInventTableInventSumRT.RecId));

    // Additional code to use the copied data.
}

Based on the maximum buffer size that is configured for the server, the Microsoft Dynamics AX run time determines the number of records in a buffer as a function of the size of the records and the buffer size. If the buffer is full, the records in the RecordInsertList object are packed, passed to the database, and inserted individually on the database tier. This check is made when the add method is called. When the insertDatabase method is called from application logic, the remaining records are inserted with the same mechanism.

Using these classes has an advantage over using while select: fewer round-trips are made from the AOS to the database because multiple records are sent simultaneously. However, the number of INSERT statements in the database remains the same.

You can rewrite the preceding example by using the RecordSortedList class instead of RecordInsertList, as shown in the following X++ code:

public static server void CopyItemInfoRSL()
{
    InventTable                  inventTable;
    InventSum                    inventSum;
    InsertInventTableInventSumRT insertInventTableInventSumRT;
    RecordSortedList             rsl;

    ttsBegin;
    rsl = new RecordSortedList(tableNum(InsertInventTableInventSumRT));
    rsl.sortOrder(fieldNum(InsertInventTableInventSumRT,PostedValue));

    while select ItemId,AltItemid from inventTable where inventTable.ItemId == '1001'
    join PhysicalValue,PostedValue from inventSum
    where inventSum.ItemId == inventTable.ItemId
    {
        insertInventTableInventSumRT.ItemId        = inventTable.itemId;
        insertInventTableInventSumRT.AltItemId     = inventTable.AltItemId;
        insertInventTableInventSumRT.PhysicalValue = inventSum.PhysicalValue;
        insertInventTableInventSumRT.PostedValue   = inventSum.PostedValue;

        //No records will be inserted.
        rsl.ins(insertInventTableInventSumRT);
    }

    //All records are inserted in database.
    rsl.insertDatabase();
    ttsCommit;

    select count(RecId) from insertInventTableInventSumRT;
    info(int642str(insertInventTableInventSumRT.RecId));

    // Additional code to utilize the copied data
}

When the application logic uses a RecordSortedList object, the records aren’t passed and inserted in the database until the insertDatabase method is called.

Both RecordInsertList objects and RecordSortedList objects can be downgraded in application logic to record-by-record inserts, in which each record is sent in a separate round-trip to the database and the INSERT statement is subsequently executed. A downgrade occurs if the insert method or the aosValidateInsert method is overridden, or if the table contains fields of the type container or memo. However, no downgrade occurs if the database log is configured to log inserts or alerts that are set to be triggered by the insert event on the table. One exception is if logging or alerts have been configured and the table contains CreatedDateTime or ModifiedDateTime columns—in this case, record-by-record inserts are performed. The database logging and alerts occur on a record-by-record basis after the records have been sent and inserted into the database.

When instantiating the RecordInsertList object, you can specify that the insert and aosValidateInsert methods be skipped. You can also specify that the database logging and eventing be skipped if the operation isn’t downgraded.

Often, code is not transferred to a set-based operation because the logic is too complex. However, an if condition, for example, can be placed in the where clause of a query. If you have a scenario that requires an if/else decision, you can achieve this with two queries, such as two update_recordsets. Necessary information from other tables can be obtained through joins instead of being looked up in a find operation. In Microsoft Dynamics AX 2012 insert_recordset and TempDB temporary tables help to extend the possibilities of transferring code into set-based operations.

Some things still might seem difficult to transfer to a set-based operation, such as performing calculations on the columns in a select statement. For this reason, Microsoft Dynamics AX 2012 offers a feature for views that is called computed columns, and you can use this feature to transfer even fairly complex logic into set-based operations. Computed columns can also provide performance advantages when used as an alternative to display methods on read-only data sources. Imagine the following task: Find all customers who bought products for more than $100,000 and all customers who bought products for more than $1,000,000. Those customers are treated as VIP customers who then get certain rebates.

In earlier versions of Microsoft Dynamics AX, the X++ code to set these values would have looked like the following example:

public static server void demoOld()
{
    SalesLine  sl;
    CustTable  ct;
    vipparm    vp;
    int64      total;

    vp = vipparm::find();
    ttsBegin;

    // One + n round-trips per Customer Account in the salesline table.
    while select CustAccount, sum(SalesQty), sum(SalesPrice) from sl group by sl.CustAccount
    {

    // Necessary to select for update causing n additional round-trips.
    ct = CustTable::find(sl.CustAccount,true);
    ct.VIPStatus = 0;

        if((sl.SalesQty*sl.SalesPrice)>=vp.UltimateVIP)
            ct.VIPStatus = 2;
        else if((sl.SalesQty*sl.SalesPrice)>=vp.VIP)
            ct.VIPStatus = 1;

     // Another n round-trips for the update.
        if(ct.VIPStatus != 0)
            ct.update();
    }
    ttsCommit;
}

You could replace this code easily with two direct Transact-SQL statements to make it far more effective. The direct Transact-SQL statements would look like the following:

UPDATE CUSTTABLE SET VIPSTATUS = 2 FROM (SELECT CUSTACCOUNT,SUM(SALESQTY)*SUM(SALESPRICE) AS
TOTAL,VIPSSTATUS = CASE
 WHEN SUM(SALESQTY)*SUM(SALESPRICE) > 1000000 THEN 2
 WHEN SUM(SALESQTY)*SUM(SALESPRICE) > 100000  THEN 1
 ELSE 0 END
 FROM SALESLINE GROUP BY CUSTACCOUNT) AS VC WHERE VC.VIPSTATUS = 2 and CUSTTABLE.ACCOUNTNUM =
VC.CUSTACCOUNT and DATAAREAID = N'CEU'

In Microsoft Dynamics AX 2012, with the help of computed columns, you can replace this code with two set-based statements.

To create these statements, you first need to create an AOT query because views themselves cannot contain a group by statement. Further, you need a parameter table that holds the information about who counts as a VIP customer for each company (Figure 13-4). Then, you need to join this information together so that it is available at run time.

The code for the computed column is shown here:

private static server str compColQtyPrice()
{
    str sReturn,sQty,sPrice,ultimateVIP,VIP;
    Map m = new Map(Types::String,Types::String);
    sQty        = SysComputedColumn::returnField(tableStr(mySalesLineView),
                                                 identifierStr(SalesLine_1),
                                                 fieldStr(SalesLine,SalesQty));
    sPrice      = SysComputedColumn::returnField(tableStr(mySalesLineView),
                                                 identifierStr(SalesLine_1),
                                                 fieldStr(SalesLine,SalesPrice));
    ultimateVIP = SysComputedColumn::returnField(tableStr(mySalesLineView),
                                                 identifierStr(Vipparm_1),
                                                 fieldStr(vipparm,ultimateVIP));
    VIP         = SysComputedColumn::returnField(tableStr(mySalesLineView),
                                                 identifierStr(Vipparm_1),
                                                 fieldStr(vipparm,VIP));
    m.insert(SysComputedColumn::sum(sQty)+'*'+SysComputedColumn::sum(sPrice)+
             ' > '+ultimateVIP,int2str(VipStatus::UltimateVIP));
    m.insert(SysComputedColumn::sum(sQty)+'*'+SysComputedColumn::sum(sPrice)+
             ' > '+VIP ,int2str(VipStatus::VIP));
    return SysComputedColumn::switch('',m,'0'),
}

Figure 13-4. Creating the parameter table and the initial query.

The next step is to add the parameter table to a view and create the necessary computed column, as shown in Figure 13-5.

Figure 13-5. Creating the view and the computed column.

The view in SQL Server looks like this:

SELECT T1.CUSTACCOUNT AS CUSTACCOUNT,T1.DATAAREAID AS DATAAREAID,1010 AS RECID,T2.DATAAREA
ID
AS DATAAREAID#2,T2.VIP AS VIP,T2.ULTIMATEVIP AS ULTIMATEVIP,(CAST ((CASE  WHEN SUM(T1.
SALESQTY)*SUM(T1.SALESPRICE) > T2.ULTIMATEVIP THEN 2 WHEN SUM(T1.SALESQTY)*SUM(T1.SALES
PRICE) >
T2.VIP THEN 1 ELSE 0 END) AS NVARCHAR(10))) AS VIPSTATUS FROM SALESLINE T1 CROSS JOIN VIPP
ARM T2
GROUP BY T1.CUSTACCOUNT,T1.DATAAREAID,T2.DATAAREAID,T2.VIP,T2.ULTIMATEVIP

Now you can change the record-based update code used earlier to effective, working set-based code:

public static server void demoNew()
{
    mySalesLineView mySLV;
    CustTable       ct;
    ct.skipDataMethods(true);
    update_recordSet ct setting VipStatus = VipStatus::UltimateVIP
    join mySLV where ct.AccountNum == mySLV.CustAccount &&
    mySLV.VipStatus == int2str(enum2int(vipstatus::UltimateVIP));
    update_recordSet ct setting VipStatus = VipStatus::VIP
    join mySLV where ct.AccountNum == mySLV.CustAccount &&
    mySLV.VipStatus == int2str(enum2int(vipstatus::VIP));
}

Executing the code shows the difference in timing:

public static void main(Args _args)
{
    int tickcnt;
    DemoClass::resetCusttable();
    tickcnt = WinAPI::getTickCount();
    DemoClass::demoOld();
    info('Line based' + int2str(WinAPI::getTickCount()-tickcnt));
    DemoClass::resetCusttable();
    tickcnt = WinAPI::getTickCount();
    DemoClass::demoNew();
    info('Set based' + int2str(WinAPI::getTickCount()-tickcnt));
}

The execution time of the operation is as follows:

Note that this code ran on demo data. Imagine running similar code on an actual database with hundreds of thousands of sales orders and customers.

Another example that might seem tricky to transfer to a set-based operation is if you need to use aggregation and group by in queries, because the update_recordset operator does not support this. You can work around this issue by using TempDB temporary tables and a combination of insert_recordset and update_recordset.

The following example first populates a table and then updates the values in it based on a group by and sum operations in a statement. Note that deleting and populating the data takes longer than the actual execution of the later insert_recordset and update_recordset statements.

public static server void PopulateTable()
{
    MyUpdRecordsetTestTable MyUpdRecordsetTestTable;
    int myGrouping,myKey,mySum;
    RecordInsertList   ril = new RecordInsertList(tablenum(MyUpdRecordsetTestTable));

    delete_from MyUpdRecordsetTestTable;

    for(myKey=0;myKey<=100000;myKey++)
    {
        MyUpdRecordsetTestTable.Key     = myKey;
        if(myKey mod 10 == 0)
        {
          myGrouping += 10;
          mySum     += 10;
        }
        MyUpdRecordsetTestTable.fieldForGrouping    = myGrouping;
        MyUpdRecordsetTestTable.theSum              = mySum;
        ril.add(MyUpdRecordsetTestTable);
    }
    ril.insertDatabase();
}

Combine TempDB temporary tables, insert_recordset, and update_recordset to update the table:

public static void InsertAndUpdate()
{
    MyUpdRecordsetTestTable MyUpdRecordsetTestTable;
    MyUpdRecordsetTestTableTmp MyUpdRecordsetTestTableTmp;
    int tc;

    tc = WinAPI::getTickCount();
    insert_recordset MyUpdRecordsetTestTableTmp(fieldForGrouping,theSum)
    select fieldForGrouping,sum(theSum) from MyUpdRecordsetTestTable
    Group by MyUpdRecordsetTestTable.fieldForGrouping;
    info("Time needed: " + int2str(WinAPI::getTickCount()-tc));

    tc = WinAPI::getTickCount();
    update_recordSet MyUpdRecordsetTestTable setting theSum = MyUpdRecordsetTestTableTmp.t
heSum
    join MyUpdRecordsetTestTableTmp
    where MyUpdRecordsetTestTable.fieldForGrouping == MyUpdRecordsetTestTableTmp.
fieldForGrouping;
    info("Time needed: " + int2str(WinAPI::getTickCount()-tc));
}

When this code ran on demo data, the execution time of the operation was as follows:

You can set up three types of record caching on a table by setting the CacheLookup property on the table definition: Found, FoundAndEmpty, and NotInTTS. An additional value (besides None) is EntireTable—a set-based caching option. These settings were introduced briefly in the section Caching and indexing, earlier in this chapter, and are discussed in greater detail in this section.

The three types of record caching are fundamentally the same. The differences are found in what is cached and when cached values are flushed. For example, the Found and FoundAndEmpty caches are preserved across transaction boundaries, but a table that uses the NotInTTS cache doesn’t use the cache when the cache is first accessed inside a transaction scope. Instead, the cache is used in consecutive select statements unless a forupdate keyword is applied to the select statement. (The forupdate keyword forces the run time to look up the record in the database because the previously cached record wasn’t selected with the forupdate keyword applied.)

The following X++ code example illustrates when the cache is used inside a transaction scope when a table uses the NotInTTS caching mechanism. The AccountNum field is the primary key. The code comments indicate when the cache is used. In the example, the first two select statements after the ttsbegin command don’t use the cache. The first statement doesn’t use the cache because it’s the first statement inside the transaction scope; the second doesn’t use the cache because the forupdate keyword is applied to the statement.

static void NotInTTSCache(Args _args)
{
    CustTable custTable;

    select custTable                          // Look up in cache. If record
        where custTable.AccountNum == '1101'; // does not exist, look up
                                              // in database.

    ttsBegin;                                 // Start transaction.

    select custTable                          // Cache is invalid. Look up in
        where custTable.AccountNum == '1101'; // database and place in cache.

    select forupdate custTable                // Look up in database because
        where custTable.AccountNum == '1101'; // forupdate keyword is applied.

    select custTable                          // Cache will be used.
        where custTable.AccountNum == '1101'; // No lookup in database.

    select forupdate custTable                // Cache will be used because
        where custTable.AccountNum == '1101'; // forupdate keyword was used
                                              // previously.

    ttsCommit;                                // End transaction.

    select custTable                          // Cache will be used.
        where custTable.AccountNum == '1101';
}

If the table in the preceding example had been set up with Found or FoundAndEmpty caching, the cache would have been used when the first select statement was executed inside the transaction, but not when the first select forupdate statement was executed.

For all three caching mechanisms, the cache is used only if the select statement contains equal-to (==) predicates in the where clause that exactly match all of the fields in the primary index of the table or any one of the unique indexes that is defined for the table. Therefore, the PrimaryIndex property on the table must be set correctly on one of the unique indexes that is used when accessing the cache from application logic. For all other unique indexes, without any additional settings in metadata, the kernel automatically uses the cache if it is already present.

The following X++ code examples show when the Microsoft Dynamics AX run time will try to use the cache. The cache is used only in the first select statement; the remaining three statements don’t match the fields in the primary index, so instead, the statements perform lookups in the database.

static void UtilizeCache(Args _args)
{
    CustTable custTable;

    select custTable                                     // Will use cache because only
        where custTable.AccountNum == '1101';            // the primary key is used as
                                                         // predicate.

    select custTable;                                    // Cannot use cache because no
                                                         // "where" clause exists.

    select custTable                                     // Cannot use cache because
        where custTable.AccountNum > '1101';             // equal-to (==) is not used.

    select custTable                                     // Will use cache even if
        where custTable.AccountNum == '1101'             // where clause contains more
        &&    custTable.CustGroup  == '20';              // predicates than the primary
                                                         // key. This assumes that the rec
ord
                                                         // have been successfully cached
                                                         // before. Please see the next sa
mple.
}

The following example illustrates how the improved caching mechanics in Microsoft Dynamics AX 2012 work when the where clause of the query contains more than just the unique index key columns:

static void whenRecordDoesGetCached(Args _args)
{
    CustTable custTable,custTable2;

    // Using Contoso demo data
    // The following select statement will not cache using the found cache because the loo
kup
    // will not return a record.
    // It would cache the record if the cache setting was FoundAndEmpty.

    select custTable
        where custTable.AccountNum == '1101'
        &&    custTable.CustGroup  == '20';

    // Following query will cache the record.

    select custTable
        where custTable.AccountNum == '1101';

    // Following will be cached too as the lookup will return a record.

    select custTable2
        where custTable2.AccountNum == '1101'
        &&    custTable2.CustGroup  == '10';

        // If you rerun the job, everything will come from the cache.
}

The following X++ code example shows how unique index caching works in the Microsoft Dynamics AX run time. The InventDim table in the base application has InventDimId as the primary key and a combination of keys (inventBatchId, wmsLocationId, wmsPalletId, inventSerialId, inventLocationId, configId, inventSizeId, inventColorId, and inventSiteId) as the unique index on the table.

static void UtilizeUniqueIndexCache(Args _args)
{
    InventDim InventDim;
    InventDim inventdim2;

    select firstonly * from inventdim2;

    // Will use the cache because only the primary key is used as predicate

    select inventDim
    where inventDim.InventDimId == inventdim2.InventDimId;
    info(enum2str(inventDim.wasCached()));

    // Will use the cache because the column list in the where clause matches that of a un
ique
    // index
    // for the InventDim table and the key values point to same record as the primary key
 fetch

    select inventDim
     where inventDim.inventBatchId == inventDim2.inventBatchId
     && inventDim.wmsLocationId    == inventDim2.wmsLocationId
     && inventDim.wmsPalletId      == inventDim2.wmsPalletId
     && inventDim.inventSerialId   == inventDim2.inventSerialId
     && inventDim.inventLocationId == inventDim2.inventLocationId
     && inventDim.ConfigId         == inventDim2.ConfigId
     && inventDim.inventSizeId     == inventDim2.inventSizeId
     && inventDim.inventColorId    == inventDim2.inventColorId
     && inventDim.inventSiteId     == inventDim2.inventSiteId;
    info(enum2str(inventDim.wasCached()));

    // Cannot use cache because the where clause does not match the unique key list or pri
mary
    // key.

    select firstonly inventDim
    where inventDim.inventLocationId== inventDim2.inventLocationId
     && inventDim.ConfigId          == inventDim2.ConfigId
     && inventDim.inventSiteId      == inventDim2.inventSiteId;
    info(enum2str(inventDim.wasCached()));
}

The Microsoft Dynamics AX run time ensures that all fields in a record are selected before they are cached. Therefore, if the run time can’t find the record in the cache, it always modifies the field list to include all fields in the table before submitting the SELECT statement to the database. The following X++ code illustrates this behavior:

static void expandingFieldList(Args _args)
{
    CustTable custTable;

    select CreditRating  // The field list will be expanded to all fields.
        from custTable
        where custTable.AccountNum == '1101';
}

Expanding the field list ensures that the record fetched from the database contains values for all fields before the record is inserted into the cache. Even though the performance when fetching all fields is inferior compared to the performance when fetching a few fields, this approach is acceptable because in subsequent use of the cache, the performance gain outweighs the initial loss of populating it.

The Microsoft Dynamics AX run time creates and uses caches on both the client tier and the server tier. The client-side cache is local to the Microsoft Dynamics AX client, and the server-side cache is shared among all connections to the server, including connections coming from Microsoft Dynamics AX Windows clients, web clients, the .NET Business Connector, and any other connection.

The cache that is used depends on the tier that the lookup is made from. If the lookup is executed on the server tier, the server-side cache is used. If the lookup is executed on the client tier, the client first looks in the client-side cache. If no record is found in the client-side cache, it executes a lookup in the server-side cache. If no record is found, a lookup is made in the database. When the database returns the record to the server and sends it on to the client, the record is inserted into both the server-side cache and the client-side cache.

If caching was set in Microsoft Dynamics AX 2009, the client stored up to 100 records per table, and the AOS stored up to 2,000 records per table. In Microsoft Dynamics AX 2012, you can configure the cache by using the Server Configuration form (System Administration > Setup > Server Configuration). For more information, see the section Performance configuration options later in this chapter.

Scenarios that perform multiple lookups on the same records and expect to find results in the cache can suffer performance degradation if the cache is continuously full—not only because records won’t be found in the cache because they were removed based on the aging scheme, forcing a lookup in the database, but also because of the constant scanning of the tree to remove the oldest records. The following X++ code shows an example in which all SalesTable records are iterated through twice: each loop looks up the associated CustTable record. If this X++ code were executed on the server and the number of lookups for CustTable records was more than 2,000 (assuming that the cache was set to 2,000 records on the server), the oldest records would be removed from the cache and the cache would no longer contain all CustTable records when the first loop ended. When the code iterates through the SalesTable records again, the records might not be in the cache, and the run time would go to the database to look up the CustTable records. The scenario, therefore, would perform much better with fewer than 2,000 records in the database.

static void AgingScheme(Args _args)
{
    SalesTable salesTable;
    CustTable  custTable;

    while select salesTable order by CustAccount
    {
        select custTable         // Fill up cache.
            where custTable.AccountNum == salesTable.CustAccount;

        // More code here.
    }

    while select salesTable order by CustAccount
    {
        select custTable         // Record might not be in cache.
            where custTable.AccountNum == salesTable.CustAccount;

        // More code here.
    }

}

Before the Microsoft Dynamics AX run time searches for, inserts, updates, or deletes records in the cache, it places a mutually exclusive lock that isn’t released until the operation is complete. This lock means that two processes running on the same server can’t perform insert, update, or delete operations in the cache at the same time. Only one process can hold the lock at any given time, and the remaining processes are blocked. Blocking occurs only when the run time accesses the server-side cache. So although the caching possibilities supported by the run time are useful, you should use them only when appropriate. If you can reuse a record buffer that is already fetched, you should do so. The following X++ code shows the same record fetched multiple times. The subsequent fetch operations use the cache, even though it could have used the first fetched record buffer.

static void ReuseRecordBuffer(Args _args)
{
    CustTable    custTable;
    CurrencyCode myCustCurrency;
    CustGroupId  myCustGroupId;
    PaymTermId   myCustPaymTermId;

    // Bad coding pattern

    myCustGroupId = custTable::find('1101').CustGroup;
    myCustPaymTermId = custTable::find('1101').PaymTermId;
    myCustCurrency = custTable::find('1101').Currency;

    // The cache will be used for these lookups, but it is much more
    // efficient to reuse the buffer, because even cache lookups are not "free."
    // Good coding pattern:

    custTable        = CustTable::find('1101'),
    myCustGroupId    = custTable.CustGroup;
    myCustPaymTermId = custTable.PaymTermId;
    myCustCurrency   = custTable.Currency;
}

The unique index join cache is new to Microsoft Dynamics AX 2012 and allows caching of subtype and supertype tables, one-to-one relation joins with a unique lookup, or a combination of both. A key constraint with this type of cache is that you can look up only one record through a unique index and you can join only over unique columns.

The following example illustrates all three possible variations:

public static void main(Args args)
{
    SalesTable      header;
    SalesLine       line;
    DirPartyTable   party;
    CustTable       customer;
    int             i;

        // subtype, supertype table caching

        for (i=0 ; i<1000; i++)
            select party where party.RecId == 5637144829;

        // 1:1 join data caching

        for (i=0 ; i<1000; i++)
            select line
            join header
            where line.RecId == 5637144586
               && line.SalesId == header.SalesId;

        // Combination of subtype, supertype, and 1:1 join caching

        for (i=0 ; i<1000; i++)
            select customer
            join party
            where customer.AccountNum == '4000'
               && customer.Party == party.RecId;
}

In addition to using the three caching methods described so far—Found, FoundAndEmpty, and NotInTTS—you can set a fourth caching option, EntireTable, on a table. EntireTable enables a set-based cache. It causes the AOS to mirror the table in the database by selecting all records in the table and inserting them into a temporary table when any record from the table is selected for the first time. The first process to read from the table can therefore experience a longer response time because the run time reads all records from the database. Subsequent select queries then read from the EntireTable cache instead of from the database.

A temporary table is usually local to the process that uses it, but the EntireTable cache is shared among all processes that access the same AOS. Each company (as defined by the DataAreaId field) has an EntireTable cache, so two processes requesting records from the same table but from different companies use different caches, and both could experience a longer response time to instantiate the entire-table cache.

The EntireTable cache is a server-side cache only. When the run time requests records from the client tier on a table that is EntireTable cached, the table behaves like a Found cached table. If a request for a record is made on the client tier that qualifies for searching the record cache, the client first searches the local Found cache. If the record isn’t found, the client calls the AOS to search the EntireTable cache. When the run time returns the record to the client tier, it inserts the record into the client-side Found cache. The EntireTable cache on the server side uses a Found cache in addition when unique key lookups are made.

The EntireTable cache isn’t used in the execution of a select statement that joins a table that is EntireTable cached to a table that isn’t EntireTable cached. In this situation, the select statement is passed to the database. However, when select statements are made that access only a single table that is EntireTable cached, or when joining other tables that are EntireTable cached, the EntireTable cache is used.

The Microsoft Dynamics AX run time flushes the EntireTable cache when records are inserted, updated, or deleted in the table. The next process that selects records from the table suffers degraded performance because it must reread the entire table into the cache. In addition to flushing its own cache, the AOS that executes the insert, update, or delete also informs other AOS instances in the same installation that they must flush their caches of the same table. This prevents old and invalid data from being cached for too long. In addition to this flushing mechanism, the AOS flushes all EntireTable caches every 24 hours.

Because of the flushing that results when modifying records in a table that has been EntireTable cached, avoid setting up EntireTable caches on frequently updated tables. Rereading all records into the cache results in a performance loss, which could outweigh the performance gain achieved by caching records on the server tier and avoiding round-trips to the database tier. You can overwrite the EntireTable cache setting on a specific table at run time when you configure Microsoft Dynamics AX.

Even if the records in a table are fairly static, you might achieve better performance by not using an EntireTable cache if the table has a large number of records. Because an EntireTable cache uses temporary tables, it changes from an in-memory structure to a file-based structure when the table uses more than 128 kilobytes (KB) of memory. This results in performance degradation during record searches. The database search engines have also evolved over time and are faster than the ones implemented in the Microsoft Dynamics AX run time. It might be faster to let the database search for the records than to set up and use an EntireTable cache, even though a database search involves round-trips to the database tier. In Microsoft Dynamics AX 2012, you can configure the amount of memory an entire table can consume before it changes to a file-based structure. To do so, go to System Administration > Setup > System > Server Configuration.

A RecordViewCache object is implemented as a linked list that allows only a sequential search for records. When you use the cache to store a large number of records, search performance is degraded because of this linked-list format. Therefore, you should not use it to cache more than 100 records. Weigh the use of the cache against the extra time spent fetching the records from the database, which uses a more optimal search algorithm. In particular, consider the time required when you search for only a subset of records; the Microsoft Dynamics AX run time must continuously match each record in the cache against the more granular where clause in the select statement because no indexing is available for the records in the cache.

You can use the RecordViewCache class to establish a set-based cache from X++ code. You initiate the cache by writing the following X++ code:

select nofetch custTrans where custTrans.accountNum == '1101';
recordViewCache = new RecordViewCache(custTrans);

The records to cache are described in the select statement, which must include the nofetch keyword to prevent the selection of the records from the database. The records are selected when the RecordViewCache object is instantiated with the record buffer passed as a parameter. Until the RecordViewCache object is destroyed, select statements will execute on the cache if they match the where clause defined when the cache was instantiated. The following X++ code shows how to instantiate and use the cache:

public static void main(Args _args)
{
    InventTrans     inventTrans;
    RecordViewCache recordViewCache;
    int countNone, countSold, countOrder;

    // Define records to cache.

    select nofetch inventTrans
       where inventTrans.ItemId == '1001';

    // Cache the records.

    recordViewCache = new RecordViewCache(InventTrans);

    // Use the cache.

    while select inventTrans
        index hint ItemIdx
        where inventTrans.ItemId == '1001' && inventTrans.StatusIssue == StatusIss
ue::OnOrder
    {
        countOrder++;

        //Additional code here

    }

    // This block of code needs to be executed only after the first while select statement
 and
    // before the second while select statement.

    // Additonal code here

    // Uses the cache again.

    while select  inventTrans
        index hint ItemIdx
        where inventTrans.ItemId == '1001' && inventTrans.StatusIssue == StatusIssue::Sold
    {
        countSold++;
        //Additional code here
    }
    info('OnOrder Vs Sold = '+int2str(countOrder) + ' : ' + int2str(countSold));
}

The cache can be instantiated only on the server tier. The select statement can contain only equal-to (==) predicates in the where clause and is accessible only by the process instantiating the cache object. If the table buffer used for instantiating the cache object is a temporary table or if it uses EntireTable caching, the RecordViewCache object isn’t instantiated.

If the table that is cached in the RecordViewCache object is also cached on a per-record basis, the run time can use both caches. If a select statement is executed on a Found cached table and the select statement qualifies for lookup in the Found cache, the run time performs a lookup in this cache first. If no record is found and the select statement also qualifies for lookup in the RecordViewCache object, the run time uses the RecordViewCache object and updates the Found cache after retrieving the record.

Inserts, updates, and deletions of records that meet the cache criteria are reflected in the cache at the same time that the data manipulation language (DML) statements are sent to the database. Records in the cache are always inserted at the end of the linked list. A hazard associated with this behavior is that an infinite loop can occur when application logic iterates through the records in the cache and at the same time inserts new records that meet the cache criteria.

Changes to records in a RecordViewCache object can’t be rolled back. If one or more RecordViewCache objects exist, if the ttsabort operation executes, or if an error is thrown that results in a rollback of the database, the RecordViewCache objects still contain the same information. Therefore, any instantiated RecordViewCache object that is subject to modification by application logic should not have a lifetime longer than the transaction scope in which it is modified. The RecordViewCache object must be declared in a method that isn’t executed until after the transaction has begun. In the event of a rollback, the object and the cache are both destroyed.

Microsoft Dynamics AX 2012 provides two mechanisms that you can use to cache global variables to improve performance: SysGlobalObjectCache (SGOC) and SysGlobalCache. SGOC is new for Microsoft Dynamics AX 2012 and is an important performance feature.

SGOC is a global cache that is located on the AOS, and not just a session-based cache. You can use this cache to reduce round-trips to the database or to store intermediate calculation results. The data that is stored from one user connection is available for all users. For more information about the SGOC, see the entry “Using SysGlobalObjectCache (SGOC) and understanding its performance implications” on the Microsoft Dynamics AX Performance Team blog (http://blogs.msdn.com/b/axperf/archive/2011/12/29/using-sysglobalobjectcache-sgoc-and-understanding-it-s-performance-implications.aspx).

SysGlobalCache uses a map to save information that is purely session-based. However, there are certain client/server considerations if you use this form of caching. If you use SysGlobalCache by means of the ClassFactory class, global variables can exist either on the client or on the server. If you use SysGlobalCache directly, it runs on the tier from which it is called. If you use SysGlobalCache by means of the Info class or the Application class, it resides on both tiers, causing a performance penalty because of increased round-trips between the client and server. For more information, see “Using Global Variables” at http://msdn.microsoft.com/en-us/library/aa891830.aspx.

With batch bundling, you create a static number of tasks and split the work among these tasks by grouping the work items into bundles. The workload distribution between each task should be as equal as possible. Each worker thread processes a bundle of work items before picking up the next bundle. This pattern works well if all of the tasks take roughly the same amount of time to process in each bundle. In an ideal situation, each worker thread is actively doing the same amount of work. But in scenarios where the workload is variable because of data composition or differences in server hardware, this approach is not the most efficient. In these scenarios, the last few threads might take longer to complete because they are processing larger bundles than the others.

You can find a code example illustrating batch bundling in the AOT at ClassesFormletterServiceBatchTaskManagercreateFormletterParmDataTasks().

With individual task modeling, parallel processing is achieved by creating a separate task for each work item so that there is a one-to-one mapping between the task and the work item. This eliminates the need for preallocation. Because each work item is independently handled by a worker thread, workload distribution is more consistent. This approach eliminates the problem of a number of large work items being bundled together and eventually increasing the response time for the batch.

This pattern is not necessarily suitable for processing a large number of work items because you will end up with a large number of batch tasks. The overhead on the batch framework to maintain a large number of tasks is high because the batch framework must check several conditions, dependencies, and constraints whenever a set of tasks is completed and a new set of tasks must be picked up for execution from the ready state.

You can find a code example that illustrates this pattern on the Microsoft Dynamics AX Performance Team blog (http://blogs.msdn.com/b/axperf/archive/2012/02/25/batch-parallelism-in-ax-part-ii.aspx).

One issue with bundling is the uneven distribution of workload. You can address that by using individual task modeling, but that can produce high overhead on the batch framework. Top picking is another batching technique that can address the problem of uneven workload distribution. However, it causes the same problem as individual task modeling with a large number of work items.

.With top picking, a static number of tasks are created—just as in bundling—and preallocation is unnecessary—just as in individual task modeling. Because no preallocation is performed, the pattern does not rely on the batch framework to separate the work items, but you do need to maintain a staging table to track the progress of the work items. Maintaining the staging table has its own overhead, but that overhead is much lower than the overhead of the batch framework. After the staging table is populated, the worker threads start processing by fetching the next available item from the staging table and continue until no work items are left. This means that no worker threads are idle while other worker threads are overloaded. To implement top picking, you use the PESSIMISTICLOCK hint along with the READPAST hint. Used together, these hints enable worker threads to fetch the next available work item without being blocked.

You can find a code example that illustrates this pattern on the Microsoft Dynamics AX Performance Team blog (http://blogs.msdn.com/b/axperf/archive/2012/02/28/batch-parallelism-in-ax-part-iii.aspx).

Microsoft Dynamics AX 2012 includes several new features for Trace Parser, which can help you understand a performance problem quickly.

Before taking a trace, run the process that you want to trace at least once to avoid seeing metadata loading in the trace file. This is called tracing in a warm state and is recommended because it helps you to focus on the real performance issue and not on metadata loading and caching. Then you can prepare everything so that the amount of time between starting the trace and executing the process you want to trace is as short as possible.

In Microsoft Dynamics AX 2009, you had to set tracing options in multiple places. In Microsoft Dynamics AX 2012, there are only three places to set options. In addition, there is only one trace file for both the client and the server.

You can start a trace in three ways:

The following sections describe each method in detail.

As mentioned earlier, you must be logged on as an administrator to use the Tracing Cockpit. Table 13-2 describes the options that are available in the Tracing Cockpit.

To start a trace from the Tracing Cockpit, do the following:

Figure 13-11. The Tracing Cockpit.

To start a trace in Windows Performance Monitor, do the following:

You can use the xClassTrace class from the Tracing Cockpit to start and stop a trace. To trace the Sales Form letter logic, see the following sample in ClassesSalesFormLetter:

// Add
xClassTrace xCt = new xClassTrace();

// to the variable declaration.
// ...code...

        if (salesFormLetter.prompt())
        {
            xClassTrace::start("c:\temp\test1.etl");
            xClassTrace::logMessage("test1");
            xCt.beginMarker("marker"); // Add markers at certain points of a trace to
                                       // increase trace readability. You can add
                                       // multiple markers per trace.

            salesFormLetter.run();

            xCt.endMarker("marker");
            xClassTrace::stop();

            outputContract  = salesFormLetter.getOutputContract();
            numberOfRecords = outputContract.parmNumberOfOrdersPosted();
        }

// ...code...

In the call to xClassTrace::start, you can use multiple parameters to specify the events to trace or whether you want to use circular logging, among other things. To find out which keyword equals which parameter, put a breakpoint in the class SysTraceCockpitcontrollerstartTracing and start a trace from the Tracing Cockpit with various events selected.

To import a trace, open the Microsoft Dynamics AX Trace Parser, and then click Import Trace. (You can also use the Open Trace form to import a trace file.) It is possible to import multiple trace files at once.

After you load the trace files into the Trace Parser, you can analyze your trace files through built-in views.

When you open a trace from the Overview tab, you see a summary that gives you a high-level understanding of where the most time is spent within the trace.

On the Overview tab, select a session. If you took the trace, select your session. If you received the trace file from someone else, select the session of the person who took the trace. When you select a session, you’ll see an overview similar to Figure 13-12, but for that session only. To return to the summary for all sessions, select the Show Summary Across All Sessions check box.

Figure 13-12. Trace overview.

After selecting a session in the drop-down list, you can search and review the trace through the X++ methods and RPC calls or the SQL queries, or you can review the call tree of the session. It’s best to start looking for quick improvements by sorting by total exclusive duration. Then, break the process down by sorting by total inclusive duration for detailed tuning. You can jump to the Call Tree view from the X++ methods and RPC calls and from the SQL view.

Use the X++/RPC view to understand patterns in your trace, as shown in Figure 13-13.

Figure 13-13. X++/RPC view.

SQL view (see Figure 13-14) gives you a quick overview of which queries were executed and how long the execution and data retrieval took.

Figure 13-14. SQL view.

Call Tree view (see Figure 13-15) is particularly helpful for identifying expensive loops and other costly patterns.

Figure 13-15. Call Tree view.

This section provides information about how to troubleshoot a few of the common issues with tracing.

Tracing won’t start If tracing doesn’t start, make sure that the user who is running the trace is a member of the Administrators or Performance Log Users group.

Tracing causes performance problems If you run a trace from a client that is located on an AOS, you will get one trace file. If the client is not on the AOS, you will get two files: one on the client computer and one on the AOS. If you run more than one client tracing session simultaneously, the system will slow down because tracing is processing- and space-intensive in this situation. It is recommended that you not turn on tracing on an AOS instance that is supporting a workload of multiple clients.

Trace doesn’t produce meaningful data If X++ code is running as CIL, a trace might not produce meaningful results. Table 13-3 lists scenarios that might cause tracing problems and describes possible mitigations.