Display and edit fields are used on forms to display data that must be derived or calculated based on other information in the table. They can be written on either the table or the form. By default, these fields are calculated one by one, and if there is any need to go to the server during one of these methods, as there usually is, each of these functions goes to the server individually. These fields are recalculated every time a refresh is triggered on the form, which can originate from editing fields, using menu items, or the user requesting a form refresh. Such a technique is expensive both from a round-tripping perspective as well as in the number of calls it places to the database from the Application Object Server (AOS).

For display and edit fields declared in the form’s Data Source, no caching can be performed because the fields need access to the form metadata. If possible, you should move these methods to the table. For display and edit fields declared on a table, you have to use FormDataSource.CacheAddMethod to enable caching. (See http://msdn.microsoft.com/en-us/library/aa893273.aspx for a detailed definition of display methods.) This method allows the form’s engine to calculate all the necessary fields in one round-trip to the server and then to cache the results from this call. To use cacheAddMethod, in the init method of a Data Source that uses display or edit methods, call cacheAddMethod on that Data Source, passing in the method string for the display or edit method. For an example of this, look at the SalesTable form’s SalesLine Data Source. In the init method, you find the following code.

public void init()
{


    super();


    salesLine_ds.cacheAddMethod(tablemethodstr(SalesLine, invoicedInTotal));


    salesLine_ds.cacheAddMethod(tablemethodstr(SalesLine, deliveredInTotal));


    salesLine_ds.cacheAddMethod(tablemethodstr(SalesLine, reservedPhysicalInSalesUnit));


    salesLine_ds.cacheAddMethod(tablemethodstr(SalesLine, reservedOnOrderInSalesUnit));


    salesLine_ds.cacheAddMethod(tablemethodstr(SalesLine, onOrderInSalesUnit));


    salesLine_ds.cacheAddMethod(tablemethodstr(SalesLine, qualityOrderStatusDisplay));


}

If this code is commented out, each of these display methods is computed for every operation on the form Data Source, thus increasing the number of round-trips to the server as well as the number of calls to the database server.

For Dynamics AX 2009, Microsoft made a significant investment in the CacheAddMethod infrastructure. In previous releases, this worked only for display fields, and only on form load. In Dynamics AX 2009, the cache is used for both display and edit fields, and is used throughout the lifetime of the form, including reread, write, refresh, and any other method that reloads the data behind the form. On all these methods, the fields are refreshed, but the kernel now refreshes them all at once rather than individually, as it did in previous versions of Dynamics AX.

RunBase classes form the basis for most business logic inside of Dynamics AX. RunBase provides much of the basic functionality needed to perform a business process, such as displaying a dialog, running the business logic, and running the business logic in batches. When business logic executes through RunBase, the logic flows as shown in Figure 12-1.

Figure 12-1. RunBase communication pattern

Most of the round-trip problems of RunBase originate with the dialog. The RunBase class should be running on the server for security reasons as well as for the fact that it is accessing lots of data from the database and writing it back. A problem occurs when the RunBase class itself is marked to run on the server. When the RunBase class is running on the server, the dialog is created and driven from the server, causing an immense number of round-trips between the client and the server.

To avoid excessive round-trips, mark the RunBase class to run on Called From, meaning that it will run on either tier, and 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 from the server, significantly reducing the round-trips needed across the application. Keep in mind that you must implement the pack and unpack methods in a way that allows this serialization to happen.

For an example of the RunBase class, examine the SalesFormLetter class in the base application. For an in-depth guide to implementing RunBase to optimally handle round-trips between the client and the server, refer to the Microsoft Dynamics AX 2009 White Paper, "RunBase Patterns," which you can find at the Microsoft Download Center.

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 previous releases of Dynamics AX, this cache operated only on primary keys. In Dynamics AX 2009, this cache has been moved and now operates across all the unique keys in a table. Therefore, if a piece of code is accessing data from the client, the code should use a unique key if possible. Also, you need to ensure that all keys that are unique are marked as such in the Application Object Tree (AOT). You can use the Best Practices tool to ensure that all your tables have a primary key.

Properly setting the CacheLookup property is a prerequisite for using the cache on the client. Table 12-1 shows the values that CacheLookup can have.

When caching is set, the client stores up to 100 records per table, and the AOS stores up to 2000 records per table.

Index caching works only if the where clause has column names that are unique. In other words, caching won’t work if a join is present, if the query is a cross-company query, or if any range operations are in the query. Therefore, if you’re checking whether a query record that has a particular primary key and some other attribute exists, search the database only by primary key. For an example of this, refer to xDataArea.isVirtualCompany.

static boolean isVirtualCompany(DataAreaId dataAreaId)
{
    DataArea dataArea1;

    boolean fRetVal;
    ;
    fRetVal=FALSE;
    select Id,isVirtual from dataArea1 where dataArea1.Id == dataAreaId;
        if(dataArea1.Id && dataArea1.isVirtual==1)
    {
        fRetVal=TRUE;
    }
    return fRetVal;
}

Notice that this code queries the database by ID and then the primary key, and then it checks the virtual company in memory. This operates at around the same speed but allows the query to hit the cache and the result of the query to be cached on both the client and server tiers.

EntireTable caches store 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 per company, such as parameter tables, you should 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. An example of the use of a key column in the base application (i.e., Dynamics AX 2009 without any customizations) is CustParameters table.

Temp tables can be a common source of both client callbacks and calls to the server. Unlike regular table buffers, temp tables reside on the tier on which the first record was inserted. For example, if a temp table is declared on the server, the first record is inserted on the client, and 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 temp table on the server because the data you need is probably coming from the database; still, you must be careful when your intent is to iterate over the data to populate a form. The easiest way to achieve this efficiently is to populate the temp table on the server, serialize the entire table down to a container, and then read all the records from the container back into a temp table on the client.

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

To remove the first kind of call, ensure that you send all the information the server needs in a serializable format, such as a packed container, record buffers, or value types (e.g., int, str, real, boolean). When the server accesses these types, it doesn’t need to go back to the client, as it does if you use an object type.

To remove the form’s logic, just send any necessary information about the form into the method, and manipulate the form only when the call returns instead of directly from inside the server. One of the best ways to defer operations on the client is with pack and unpack. By utilizing pack and unpack, you can serialize a class down to a container and then deserialize it on the other side.

To ensure the minimum number of round-trips between the client and the server, chunk them into one, static server method and pass in all the state needed to perform the operation.

One static server method you can use is NumberSeq::getNextNumForRefParmId. This method call is a static server call that contains the following line of code.

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

Had this code run on the client, it would have caused 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 chunking occurs when the client is doing Transaction Tracking System (TTS) operations. Frequently, a developer writes code similar to the following.

Ttsbegin;
Record.update();
TTSCommit

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

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 as the code copies sizes from one item to another item. The item to which the sizes are copied is selected from inventTable.

static void CopySizes(Args _args)
{
    InventSize  inventSizeTo;
    InventSize  inventSizeFrom;
    InventTable inventTable;
    ;
    ttsbegin;
    insert_recordset inventSizeTo (ItemId, InventSizeId, Description, Name)
        select itemId from inventTable
            where inventTable.ItemId == '1000'
        join inventSizeId, description, name from inventSizeFrom
            where inventSizeFrom.ItemId == '1002';
    ttscommit;
}

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 as the preceding X++ code.

static void CopySizes(Args _args)
{
    InventSize  inventSizeTo;
    InventSize  inventSizeFrom;
    InventTable inventTable;
    ;
    ttsbegin;
    while select itemId from inventTable
            where inventTable.ItemId == '1000'
        join inventSizeId, description, name from inventSizeFrom
            where inventSizeFrom.ItemId == '1002'
    {
        inventSizeTo.ItemId         = inventTable.ItemId;
        inventSizeTo.InventSizeId   = inventSizeFrom.InventSizeId;
        inventSizeTo.Description    = inventSizeFrom.Description;
        inventSizeTo.Name           = inventSizeFrom.Name;
        inventSizeTo.insert();
    }
    ttscommit;
}

If 10 sizes were copied, this scenario would result in 1 round-trip caused by the select statement and an additional 10 round-trips caused by the inserts, totaling 11 round-trips.

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

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

Important

When the Dynamics AX application runtime checks for overridden methods, it determines only whether the methods are implemented. It doesn’t determine whether the overridden methods contain only the default X++ code. A method is therefore considered to be overridden by the application runtime even though it contains the following X++ code.

public void insert() { super; }

Any set-based insert is then downgraded. You need to remember to delete such a method to avoid the downgrade, with its performance ramifications.

If a table is not entire-table cached, however, you can avoid any downgrade caused by the previously mentioned functionality. The record buffer contains methods that turn off the checks that the application runtime 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 CopySizes(Args _args)
{
    InventSize  inventSizeTo;
    InventSize  inventSizeFrom;
    InventTable inventTable;
    ;
    ttsbegin;
    inventSizeTo.skipDataMethods(true); // Skip override check on insert.
    insert_recordset inventSizeTo (ItemId, InventSizeId, Description, Name)
        select itemId from inventTable
            where inventTable.ItemId == '1000'
        join inventSizeId, description, name from inventSizeFrom
            where inventSizeFrom.ItemId == '1002';
    ttscommit;
}

You must use skip methods with extreme caution because they can lead to the logic in the insert method not being executed, events not being raised, and potentially, the database log not being written to. If you override the insert method, you should use the cross-reference system to determine whether any X++ code calls skipDataMethods(true). If you don’t, the X++ code could fail to execute the insert method. Moreover, when you implement calls to skipDataMethods(true), make sure that not executing the X++ code in the overridden insert method won’t lead to data inconsistency.

Skip methods can be used only to influence whether the insert_recordset operation is downgraded. If a call to skipDataMethods(true) is implemented to prevent downgrading because the insert method is overridden, the overridden version of the insert method will eventually be executed if the operation is still downgraded. The operation would be downgraded, if, for example, the database log had been 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 get called.

Dynamics AX 2009 introduces support for literals in insert_recordset. The literal support for insert_recordset 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 need to be populated with a literal value that doesn’t exist in the source. The following code example illustrates the usage of literals in insert_recordset.

static void InsertRecordSetLiteralExample(Args _args)
{
    CusttTable customer;
    CustTable custTable;
    boolean flag =  boolean::false;
    ;

    ttsbegin;
    insert_recordset
        customer
            (
                Name,
                Active
            )
    select
        Name,
        flag
    from
        custTable;
    ttscommit;
}

The behavior of the update_recordset operator is very similar to that of the insert_recordset operator. This similarity is illustrated by the following piece of X++ code, in which all sizes for an item are updated with a new description.

static void UpdateSizes(Args _args)
{
    InventSize  inventSize;
    ;
    ttsbegin;
    update_recordset inventSize
        setting Description = 'This size is for item 1000'
        where inventSize.itemId == '1000';
    ttscommit;
}

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

static void UpdateSizes(Args _args)
{
    InventSize  inventSize;
    ;
    ttsbegin;
    while select forupdate inventSize
        where inventSize.itemId == '1000'
    {
        inventSize.Description = 'This size is for item 1000';
        inventSize.update();
    }
    ttscommit;
}

If 10 records qualified, 1 select statement and 10 update statements would be parsed to the database rather than the single update statement that would be parsed if you used update_recordset.

The update_recordset operation can be downgraded if specific methods have been overridden or the the Dynamics AX application is configured. The update_recordset operation is downgraded if any of the following conditions is true:

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

You can avoid any downgrade caused by the previously mentioned functionality unless the table is entire-table cached. The record buffer contains methods that turn off the checks that the application runtime performs when determining whether to downgrade the update_recordset operation.

As we explained earlier, you should use the skip methods with great caution, and you should take the same precautions before using the skip methods in combination with the update_recordset operation. Again, using the skip methods only influences whether the update_recordset operation is downgraded to a while select scenario. If the operation is downgraded, database logging, alerting, and execution of overridden methods occurs even though the respective skip methods have been called.

Tip

If an update_recordset operation is downgraded to a while select scenario, the select statement uses the concurrency model specified at the table level. You can apply the optimisticlock and pessimisticlock keywords to the update_recordset statements and enforce a specific concurrency model to be used in case of downgrade.

Dynamics AX 2009 supports inner and outer joins in update_recordset. Previous versions of Dynamics AX supported only exists and not exists joins. 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 usage of joins with update_recordset.

static void UpdateRecordsetJoinsExample(Args _args)
{
    NewTaxes taxTable;
    Orders ordersTable;
    ;
    ttsbegin;
    UPDATE_RECORDSET
        ordersTable
    SETTING
        Total = ordersTable.Total + ( ordersTable.Total * ( taxTable.TaxPercent / 100 ) )
    JOIN
        taxTable
    WHERE
        ordersTable.CountryId == taxTable.CountryId
    ;
    ttscommit;
}

The delete_from operator is similar to the insert_recordset and update_recordset operators in that it parses a single statement to the database to delete multiple rows. The following X++ code shows the deletion of all sizes for an item.

static void DeleteSizes(Args _args)
{
    InventSize  inventSize;
    ;
    ttsbegin;
    delete_from inventSize
        where inventSize.itemId == '1000';
    ttscommit;
}

This code parses a statement to SQL Server in a similar syntax to DELETE <table> WHERE <predicates> and executes the same scenario as the following X++ code, which uses record-by-record deletes.

static void DeleteSizes(Args _args)
{
    InventSize  inventSize;
    ;
    ttsbegin;
    while select forupdate inventSize
        where inventSize.itemId == '1000'
    {
        inventSize.delete();
    }
    ttscommit;
}

Again, the use of delete_from is preferred with respect to performance because a single statement is parsed to the database, rather than the multiple statements that the record-by-record version parses.

Like the downgrading insert_recordset and update_recordset operations, the delete_from operation could also be downgraded, and for similar reasons. Downgrade occurs if any of the following is true:

Downgrade also occurs if delete actions are defined on the table. The Dynamics AX application runtime automatically handles the downgrade and internally executes a scenario similar to the while select scenario shown in the preceding example.

You can avoid downgrades caused by the previously mentioned functionality, unless the table is entire-table cached. The record buffer contains methods that turn off the checks that the application runtime performs when determining whether to downgrade the delete_from operation. Here are the skip descriptions:

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.

Note

The record buffer also contains a skipDeleteMethod method. Calling the methods as skipDeleteMethod(true) has the same effect as calling skipDataMethods(true). It invokes the same Dynamics AX application runtime logic, so you can use skipDeleteMethod in combination with insert_recordset and update_recordset, although it might not improve the readability of the X++ code.

In addition to the set-based operators, Dynamics AX also allows you to use the RecordInsertList and RecordSortedList classes when inserting multiple records into a table. When the records are ready to be inserted, the Dynamics AX application runtime packs multiple records into a single package and sends it to the database. The database executes individual inserts 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 CopySizes(Args _args)
{
    InventSize          inventSizeTo;
    InventSize          inventSizeFrom;
    InventTable         inventTable;
    RecordInsertList    recordInsertList;
    ;
    ttsbegin;
    recordInsertList = new RecordInsertList(tableNum(InventSize));
    while select itemId from inventTable
            where inventTable.ItemId == '1000'
        join inventSizeId, description, name from inventSizeFrom
            where inventSizeFrom.ItemId == '1002'
    {
        inventSizeTo.ItemId         = inventTable.ItemId;
        inventSizeTo.InventSizeId   = inventSizeFrom.InventSizeId;
        inventSizeTo.Description    = inventSizeFrom.Description;
        inventSizeTo.Name           = inventSizeFrom.Name;
        recordInsertList.add(inventSizeTo); // Insert records
                                            // if package is full.
    }
    recordInsertList.insertDatabase();      // Insert remaining records
                                            // into database.
    ttscommit;
}

Based on the Server Configuration buffer size, the Dynamics AX application runtime determines the number of records in a buffer as a function of the size of the records and buffer size. If the buffer is filled up, the records in the RecordInsertList object are packed, parsed 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 the 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.

Note

Because the timing of insertion into the database depends on the size of the record buffer and the package, you shouldn’t expect a record to be selectable from the database until the insertDatabase method has been called.

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

static void CopySizes(Args _args)
{
    InventSize          inventSizeTo;
    InventSize          inventSizeFrom;
    InventTable         inventTable;
    RecordSortedList    recordSortedList;
    ;
    ttsbegin;
    recordSortedList = new RecordSortedList(tableNum(InventSize));
    recordSortedList.sortOrder(fieldNum(InventSize, ItemId),
                               fieldNum(InventSize, InventSizeId));
    while select itemId from inventTable
            where inventTable.ItemId == '1000'
        join inventSizeId, description, name from inventSizeFrom
            where inventSizeFrom.ItemId == '1002'
    {
        inventSizeTo.ItemId         = inventTable.ItemId;
        inventSizeTo.InventSizeId   = inventSizeFrom.InventSizeId;
        inventSizeTo.Description    = inventSizeFrom.Description;
        inventSizeTo.Name           = inventSizeFrom.Name;
        recordSortedList.ins(inventSizeTo); //No records will be inserted.
    }
    recordSortedList.insertDatabase();//All records are inserted in database.
    ttscommit;
}

When the application logic uses a RecordSortedList object, the records aren’t parsed and inserted in the database until the insertDatabase method is called. The number of round-trips and INSERT statements executed is the same as for the RecordInsertList object.

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. Downgrading occurs if the insert method or the aosValidateInsert method is overridden, or if the table contains fields of type container or memo. Downgrading doesn’t occur if the database log is configured to log inserts or alerts that have been 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 eventing occurs 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.

You can set up three types of record caching on a table by setting the CacheLookup property on the table definition:

One additional value (besides None) is EntireTable, which is a set-based caching option we describe later in this section.

The three record-caching possibilities are fundamentally the same. The differences lie 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 first accessed inside a transaction scope—it uses it in consecutive select statements, unless a forupdate keyword is applied to the select statement. The following X++ code example describes when the cache is used inside and outside a transaction scope, when a table uses the NotInTTS caching mechanism, and when the AccountNum field is the primary key. The code comments indicate when the cache is or isn’t used. In the example, it appears that the first two select statements after the ttsbegin command don’t use the cache. The first 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. The use of the forupdate keyword forces the application runtime to look up the record in the database because the previously cached record wasn’t selected with the forupdate keyword applied.

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 had been set up with Found or FoundAndEmpty caching in the preceding example, the cache would have been used when executing the first select statement inside the transaction, but not when the first select forupdate statement was executed.

Note

By default, all Dynamics AX system tables are set up using a Found cache. This cannot be changed.

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 the fields in the primary index of the table or any one of the unique indexes defined for the table. The PrimaryIndex property on the table must therefore be set correctly on one of the unique indexes 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. Support for unique-index-based caching is a new feature in Dynamics AX 2009.

The following X++ code examples show when the Dynamics AX application runtime will try to use the cache and when it won’t. The cache is used only in the first select statement; the remaining three statements don’t match the fields in the primary index, so they will all 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 not use cache because
        where custTable.AccountNum == '1101'    // where clause contains more
        &&    custTable.CustGroup  == '20';     // predicates than the primary
                                                // key.
}

Note

The RecId index, which is always unique on a table, can be set as the PrimaryIndex in the table’s properties. You can therefore set up caching using the RecId field.

The following X++ code examples show how unique-index caching works in the Dynamics AX application runtime. InventDim 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;
    ;
    select inventDim                                // Will use cache because only
    where inventDim.inventDimId == '00000001_082';  // the primary key is used as
                                                    // predicate.

    select inventDim                                // Will use cache
     where inventDim.inventBatchId == ''            // because the column list in
     && inventDim.wmsLocationId    == ''            // the "where" clause
     && inventDim.wmsPalletId      == ''            // match that of a unique
     && inventDim.inventSerialId   == ''            // index for table inventdim
     && inventDim.inventLocationId == '400'         // and the key values point to
     && inventDim.ConfigId         == '01'          // same record as the primary
     && inventDim.inventSizeId     == ''            // key fetch (inventDimId ==
     && inventDim.inventColorId    == ''            // '00000001_082').
     && inventDim.inventSiteId     == '4';          //

    select inventDim                                // Cannot use cache because
    where inventDim.inventLocationId== '400'        // where clause does not
     && inventDim.ConfigId         == '01'          // match the unique key list
     && inventDim.inventSiteId     == '4';          // or primary key.
}

The Dynamics AX application runtime ensures that all fields on a record are selected before they are cached. The application runtime therefore always changes a field list to include all fields on the table before submitting the SELECT statement to the database when it can’t find the record in the cache. 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';
}

If the preceding select statement doesn’t find a record in the cache, it expands the field to contain all fields, not just the creditRating field. This ensures that the fetched record from the database contains values for all fields before it is inserted into the cache. Even though performance when fetching all fields is inferior compared to performance when fetching a few fields, this approach is acceptable because in subsequent use of the cache, the performance gain outweighs the performance loss from populating it.

Tip

You can disregard the use of the cache by calling the disableCache method on the record buffer with a Boolean true parameter. This method forces the application runtime to look up the record in the database, and it also prevents the application runtime from expanding the field list.

The Dynamics AX application runtime creates and uses caches on both the client tier and the server tier. The client-side cache is local to the rich client, and the server-side cache is shared among all connections to the server, including connections coming from rich clients, Web clients, .NET Business Connector, and any another connection.

The cache used depends on which tier the lookup is made from. If the lookup is made on the server tier, the server-side cache is used. If the lookup is executed from the client tier, the client first looks in the client-side cache; if it doesn’t find anything, it makes a lookup in the server-side cache. If there is still no record, a lookup is made in the database. When the database returns the record to the server and on to the client, the record is inserted into both the server-side cache and the client-side cache.

The caches are implemented using AVL trees (which are balanced binary trees), but the trees aren’t allowed to grow indefinitely. The client-side cache can contain a maximum of 100 records for a given table in a given company, and the shared server-side cache can contain a maximum of 2000 records. When a new record is inserted into the cache and the maximum is reached, the application runtime removes approximately 5 to 7 percent of the oldest records by scanning the entire tree.

Note

You can’t change the maximum number of records to be cached in metadata or from the X++ code.

Scenarios that repeat lookups on the same records and expect to find the records 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 looped twice, and each loop looks up the associated CustTable record. If this X++ code were executed on the server and the number of CustTable record lookups was more than 2000, the oldest records would be removed from the cache, and the cache wouldn’t contain all CustTable records when the first loop ended. When the code loops through the SalesTable records again, the records might not be in the cache, and the selection of the CustTable record would continue to go to the database to look up the record. The scenario would therefore perform much better with fewer than 2000 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.
    }

}

Important

If you test code on small databases, you can’t track repeat lookups only by tracing the number of statements parsed to the database. When you execute such code in a production environment, you can encounter severe performance issues because this scenario doesn’t scale very well.

Before the Dynamics AX application runtime 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 application runtime accesses the server-side cache. So although the caching possibilities the application runtime supports are useful features, you shouldn’t abuse them. If you can reuse a record buffer that is already fetched, you should do so. The following X++ code shows the same record fetched twice. The second fetch uses the cache even though it could have used the first fetched record buffer. When you execute the following X++ code on the server tier, the process might get blocked when the application runtime searches the cache.

static void ReuseRecordBuffer(Args _args)
{
    CustTable custTable;
    ;
    select custTable
        where custTable.AccountNum == '1101';

    // Some more code, which does not change the custTable record.

    select custTable                           // The cache will be used, but
        where custTable.AccountNum == '1101';  // blocking might occur.
                                               // Reuse the record buffer
                                               // instead.
}

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 could therefore experience a longer response time because the application runtime reads all records from the database. Subsequent select queries then read from the entire-table cache instead of from the database.

A temporary table is usually local to the process that uses it, but the entire-table cache is shared among all processes that access the same AOS. Each company (as defined by the DataAreaId field) has an entire-table 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 entire-table cache is a server-side cache only. When requesting records from the client tier on a table that is entire-table cached, the table behaves as 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 entire-table cache. When the application runtime returns the record to the client tier, it inserts the record into the client-side Found cache.

The entire-table cache isn’t used when executing a select statement by which an entire-table-cached table is joined to a table that isn’t entire-table cached. In this situation, the entire select statement is parsed to the database. However, when select statements are made that access only the single entire-table cached table, or when joining other entire-table cached tables, the entire-table cache is used.

The Dynamics AX application runtime flushes the entire-table cache when records are inserted, updated, or deleted in the table. The next process, which selects records from the table, suffers a degradation in 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 AOSs in the same installation that they must flush their caches on the same table. This prevents old and invalid data from being cached for too long in the entire Dynamics AX application environment. In addition to this flushing mechanism, the AOS flushes all the entire-table caches every 24 hours.

Because of the flushing that results when modifying records in a table that has been entire-table cached, you should avoid setting up entire-table 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 entire-table cache setting on a specific table at run time when you configure the Dynamics AX application.

Even if the records in a table are fairly static, you might achieve better performance by not using the entire-table cache if the table has a large number of records. Because the entire-table 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 Dynamics AX application runtime. It might be faster to let the database search for the records than to set up and use an entire-table cache, even though a database search involves round-trips to the database tier.

The RecordViewCache class allows you to establish a set-based cache from the 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 parsed as a parameter. Until the RecordViewCache object is destroyed, select statements will execute on the cache if they match the where clause defined when it was instantiated. The following X++ code shows how the cache is instantiated and used.

static void RecordViewCache(Args _args)
{
    CustTrans       custTrans;
    RecordViewCache recordViewCache;
    ;
    select nofetch custTrans                       // Define records to cache.
        where custTrans.AccountNum == '1101';

    recordViewCache = new RecordViewCache(custTrans); // Cache the records.

    select firstonly custTrans                        // Use cache.
        where custTrans.AccountNum == '1101' &&
              custTrans.CurrencyCode == 'USD';
}

The cache can be instantiated only on the server tier. The defined 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 it uses EntireTable caching, the RecordViewCache object isn’t instantiated.

The records are stored in the cache as a linked list of records. Searching therefore involves a sequential search of the cache for the records that match the search criteria. When defining select statements to use the cache, you can specify a sort order. When a sort order is specified, the Dynamics AX application runtime creates a temporary index on the cache, which contains the requested records sorted as specified in the select statement. The application runtime iterates the temporary index when it returns the individual rows. If no sorting is specified, the application runtime merely iterates the linked list.

If the table cached in the RecordViewCache is also record-cached, the application runtime 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 application runtime performs a lookup in this cache first. If nothing is found and the select statement also qualifies for lookup in the RecordViewCache, the runtime uses the RecordViewCache and updates the Found cache after retrieving the record.

Inserts, updates, and deletes 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 is iterating the records in the cache and at the same time inserting new records that meet the cache criteria. An infinite loop is shown in the following X++ code example, in which a RecordViewCache object is created containing all custTable records associated with CustGroup ‘20’. The code iterates each record in the cache when executing the select statement, but because each cached record is duplicated and still inserted with CustGroup ‘20’, the records are inserted at the end of the cache. Eventually, the loop fetches these newly inserted records as well.

static void InfiniteLoop(Args _args)
{
    CustTable       custTable;
    RecordViewCache recordViewCache;
    custTable       custTableInsert;
    ;
    select nofetch custTable                 // Define records to cache.
        where custTable.CustGroup == '20';
    recordViewCache = new RecordViewCache(custTable); // Instantiate cache.

    ttsbegin;
    while select custTable                  // Loop over cache.
        where custTable.CustGroup == '20'
    {
        custTableInsert.data(custTable);
        custTableInsert.AccountNum = 'dup'+custTable.AccountNum;
        custTableInsert.insert();      // Will insert at end of cache.
                                       // Records will eventually be selected.
    }
    ttscommit;
}

To avoid the infinite loop, simply sort the records when selecting them from the cache, which creates a temporary index that contains only the records in the cache from where the records were first retrieved. Any inserted records are therefore not retrieved. This is shown in the following example, in which the order by operator is applied to the select statement.

static void FiniteLoop(Args _args)
{
    CustTable       custTable;
    RecordViewCache recordViewCache;
    custTable       custTableInsert;
    ;
    select nofetch custTable                 // Define records to cache.
        where custTable.CustGroup == '20';
    recordViewCache = new RecordViewCache(custTable); // Instantiate cache.

    ttsbegin;
    while select custTable                  // Loop over a sorted cache.
        order by CustGroup                  // Create temporary index.
        where custTable.CustGroup == '20'
    {
        custTableInsert.data(custTable);
        custTableInsert.AccountNum = 'dup'+custTable.AccountNum;
        custTableInsert.insert();       // Will insert at end of cache.
                                        // Records are not inserted in index.
    }
    ttscommit;
}

Changes made 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. Any instantiated RecordViewCache object that is subject to modification by the application logic should therefore 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.

As described earlier, the 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, a performance degradation in search occurs because of this linked-list format. You should weigh the use of the cache against the extra time spent fetching the records from the database where the database uses a more optimal search algorithm. Consider the time hit especially when you search only for a subset of the records; the application runtime 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.

However, for small sets of records, or for situations in which the same records are looped multiple times, RecordViewCache offers a substantial performance advantage compared to fetching the same records multiple times from the database.

In this section, we provide information on how to troubleshoot the two most common issues you encounter while tracing.

Sometimes a trace that appears to be running in the configuration utility doesn’t look like it’s running in Windows. Here’s why: When a trace file reaches its size limit, Windows stops the trace. The configuration utility interface doesn’t synchronize with Windows until Stop Trace is clicked. Additionally, the Event Tracing for Windows framework drops the trace and the trace events when the disk has insufficient space. So make sure that there is more free space than the value set in tracemaxfilesize in the registry.

If you run more than one client-tracing session simultaneously, don’t be surprised when your system slows down. Tracing is processing intensive and space intensive. We recommend that you don’t turn on tracing for more than one client at a time.

You can import traces collected on either the client or the server by downloading the Dynamics AX Trace Parser. Detailed documentation for importing traces is available with the tool.

Once you load the trace files into the Trace Parser, they are available with ready-made analysis views. Figure 12-2 shows the Open Trace Database view.

Figure 12-2. Trace listings view

When you open a trace from the trace listings view, you see a summary view with many search capabilities and an integrated view of the code as the call tree is analyzed.

Figure 12-3 shows a sample trace summary view.

Figure 12-3. Sample trace summary view