Application Design Standards
The Dynamics AX design standards consist of the following considerations:
- Code placement
- Performance optimization
- Using field groups in tables
- Auto property settings
Code placement is important as it affects the following:
- Maintainability (upgrade, extensibility, etc.)
The general guidelines are that the code should be placed in such a way that various calls to other layers are minimized, the operation is performed at the layer where it is least expensive, and the same code need not be written at several places (e.g. if business logic is written on a form then it needs to be written in the enterprise portal for the web client also). So we should not only think about the tier of placement in the three-tier architecture, but also about the best AOT (Application Object Tree) element type for a piece of code. Once these have been designed we need to think about the type of method or objects in the classes, etc.
Three-Tier Architecture Considerations
The three tiers in this architecture are dedicated for the following three types of jobs:
- Client—The Presentation layer—This is where the forms are stored. Place client‑specific classes and methods here.
- Object server—The Business application logic layer—Transaction-oriented database update jobs should be run here, close to the database.
- Database server—The Database layer—Utilize the power of the database server by using aggregate functions, joins, and other calculating features of the database management system.
Dynamics AX has a property, RunOn, for every AOT element, which indicates the layer where it should be executed i.e. Client, AOS, or Database server. This RunOn property may have one of three values i.e. Client, Called from, and Server.
Client: The object will live on the client.
Called from: The object will live at the tier where the object is created using the 'new' constructor.
Server: The object will live on the server.
Now we will discuss how to decide the RunOn property value.
The value of the RunOn property for a class will decide the location of the object created from that class.
- The value of RunOn property for a class will be same as the parent class if the parent class has a RunOn property other than Called from i.e. the RunOn property cannot be changed for a class if the parent class has as its value either Client or Server.
- If the parent class has Called from as its RunOn property value, it can be changed to Client or Server and if it is not changed it will retain the inherited value i.e. Called from.
- The Called from value of the RunOn property means the object will live at the tier where the code creating it (by calling the new constructor) is running.
Now we will discuss the execution place for various types of methods.
- Class static methods and table methods (except for the database methods) can have their default behavior.
- The execution place for a method can be changed to Server or Client by adding the Client or Server modifier keywords in the method declaration as shown below:
- server static boolean mymethod(): to make server the execution place.
- client static boolean mymethod(): to make client the execution place.
- client server static boolean mymethod(): to make called from the execution place.
The following table summarizes the execution place of various types of methods:
Can be changed
Class static methods
Runs by default at the same place where the associated class runs i.e. if the associated class has the RunOn property value as server then the class static method will also be executed at the server.
Class instance methods
Runs where the object of the class lives. The class objects live as described in the class RunOn property.
Table static methods
Table static methods have the RunOn property as Called from and hence by default they run where they are called.
Table instance methods
Table instance methods have the RunOn property as Called from and hence by default they run where they are called.
The standard methods Insert/doInsert, Update/doUpdate, and Delete/doDelete run on the Server where the data source is located.
GUI Objects and Reports
GUI objects always live on Client. GUI objects include the FormRun, FormDataSource, all FormControls, DialogBox, and OperationProgress objects.
Reports always live on Called from, which means the object will live at the tier where the code creating it (by calling the new constructor) is running.
Temporary tables instantiate and live at the tier where data is first inserted and it does not matter where they are declared. Since the placement of temporary tables is very critical for performance, temporary tables should live at the tier where they are used. If a table is utilized in more than one tier then it should live on the tier where the greatest number of inserts and updates are performed.
QueryRun has Called from as the default value of the RunOn property. The QueryRun should always be supplied from the same tier from where it was originally run.
If you want to create a new QueryRun in place of an old one, it should be created on the same tier where the old QueryRun was executed.
AOT Element Type Consideration
The following guidelines must be followed to decide the type of code container:
Write code in class when either:
Code is related to many tables.
Code is not related to any table.
Create class instance method when:
Working on the instance variable of the class.
Overriding is potentially useful.
Create class static method when:
Access to the class instance method is not required.
Overriding is not needed.
The functionality of the method is related to the class it is defined on.
The method needs to be executed on a different tier than the method's tier.
Write code in table method when:
It is strictly related to a table.
Create table instance method when:
It is supposed to handle one record at a time.
Create table static method when:
It is supposed to handle none, some, or all the records at a time.
Write code in global class when:
Code cannot be placed more logically in another class (or table).
Code is general purpose, tool extending, and application neutral.
Forms and reports
Coding on forms or reports should be avoided as far as possible i.e. except for the calls to classes and table methods that handle complex layout and business logic.
The edit and display methods must be avoided if they are placed in a table.
If code cannot be placed anywhere else, i.e. the presentation tier is most suitable, then the following guidelines should be observed:
Place the code at the data source or data source field level and not at the control level.
Call classes from buttons on forms by using menu items. For example, rather than writing a code on the form or report, code could be written in a class and the class could be called from the menu item.
Write code in maps when a limited number of connected fields needs to be grouped.
Do not place much code in views.
The performance optimization guidelines can be categorized into the following three categories:
- Database design
- AOS performance optimization
- General programming
The database design principles are based on the following considerations:
- Minimizing the database calls by caching
- Minimizing database transactions
- Wise index designing
- Using the select statement in an optimum way
- Performing transactions in the shortest time possible
Database access should be avoided whenever it is not absolutely necessary as retrieving database records from memory is far cheaper and faster. Recording database records in memory is known as caching. The following are the possible type of caching on the server:
- Record caching
- Entire table caching
- Result-set caching
Record caching is a type of performance enhancement technique in which one or a group of records is retrieved from the memory rather than the database. Retrieving a record from memory rather than database significantly improves the data access. Record caching can be enabled only when the following conditions are satisfied:
- The CacheLookup property in the table should be enabled by selecting the values notITTS, Found, or FoundAndEmpty.
- The table has a unique index; either use the primary index or the first unique index. The index based on RecId (known as RecId index) does not qualify as a caching index.
The retrieved records can be placed in cache if the following conditions are met:
- The table is cached i.e. the above conditions are met.
- The select statement used to read the record uses an equal operator (= =) on the caching key.
- All the fields in the record are retrieved.
A record is looked for when the following conditions are met:
- The table is cached.
- The select statement used to read the record uses an equal operator (= =) on caching key.
- The select statement is either inside or outside TTS, but the value of the caching property for the table is not NotITTS and the select is not forupdate.
The following table summarizes the different types of caching mechanism:
No data will be cached or retrieved from the cache.
This value of CacheLookup property is used when:
Tables are frequently updated e.g. transaction tables.
It is very critical to read fresh data.
All select queries that retrieved at least one result will be cached.
All successful select queries based on caching key are cached for this type of caching.
All select queries are returned from cache if the record exists there.
A select forupdate in TTS will always read from the database and replace the record in cache.
This value of the CacheLookup property is typically used for static tables like ZipCodes where the record usually exists.
All select queriesbased on caching keys are cached, even those select queries, which do not return data.
All caching keys selects are returned from caching if the record exists or is marked as non-existing, and if it is neither marked as non-existing nor retrieving any result it will check the database and update the cache accordingly.
A select forupdate in TTS will always read from the database and replace the record in cache.
This value of the CacheLookup property is typically used for tables where registering non-existing keys is also important e.g. discount table.
A copy of table is created as temporary table.
All selects against the table will be performed on the copy.
Joins that include the table will only be performed against the copy when all tables participating in the join are EntireTable cached. Otherwise a database join is performed.
Operations that change data (insert, update, and delete) are performed against the database as well as against the copy.
The reread method will retrieve the data from database and update the copy data as well.
The Microsoft Dynamics AX Object Server thin client will regard an EntireTable cached table as FoundAndEmpty cached as well, and will therefore build a recordCache locally when accessing the table.
This value of the CacheLookup property is typically used for tables that are not supposed to be modified frequently.
The RecordViewCache is useful for caching tables that are not of static nature, or contain so many records that the other caching methods would be impractical.
This type of caching can be available through the RecordViewCache class. The RecordViewCache is instantiated using X++ select with a where clause that defines the result set. Technically the RecordViewCache can be instantiated using X++ select but it will create a copy of table in memory, which may be an inefficient use of memory.
The following rules apply to the instantiating X++ select:
- It may not be a join.
- It must be noFetch.
- The table may not be temporary.
- When running a Dynamics AX Object Server thin client, instantiation must be on the server.
The limitations of the result-set caching are as follows:
- The RecordViewCache is not shared between Dynamics AX clients.
- The RecordViewCache is deactivated as soon as the RecordViewCache object goes out of scope or is destroyed.
In some cases result-set caching may be dangerous and hence only careful use is recommended. The following facts about result-set caching will be helpful in deciding the use of result-set caching.
- The database is simultaneously updated with the RecordViewCache.
- Updating the key of a row that did not qualify the result set at the time of instantiation will not result in the row being included in the cache.
- Inserts are always included in the RecordViewCaches for that table. It is definitely an advantage; however, care should be taken when inserting a large number of rows in a table and at the same time having a RecordViewCache on the same table as it will prolong the cache update time.
- A delete will remove the row from RecordViewCaches on that table but not the table.
- A Delete_from will invalidate RecordViewCaches on that table.
As mentioned above the RecordViewCache can go out of synchronization and hence we may need to re-synchronize it again. X++ has a method reread, which retrieves the data from database and updates the cached copy.
Index design is very critical as far as database performance optimization is concerned. In general indexes are stored in a separate file or table to that of the data table. Thus searches are performed through a small set of columns (indexed columns only) and then the data is ultimately retrieved from the table itself by the database using the index. The objectives are as follows.
Primary keys or indexes are generally used to uniquely identify a record. The original intention of primary and foreign keys was that of the representation and enforcement of referential integrity between related tables. Primary keys are now used as the most important index for a table and do not necessarily have referring foreign keys in other tables. Primary keys are usually attached to the data space of the table itself but not always. This is database product specific.
Non-primary indexes are indexes constructed from one or more table columns. Non‑primary indexes may or may not include the primary index column or columns. The purpose of non-primary indexes is to improve database access performance. These indexes will be created as a separate file within the database. Thus when searching these indexes a small number of columns is loaded into memory for searching. These indexes have virtual memory addresses into the data space of the table, thus allowing rapid access between index space and table space.
Note that the memory or cache space required for an index is much smaller than that of a data table since the row length of an index table is smaller. The reason behind this is that indexes will have a fewer columns in comparison to the data table. Thus in the case of page swapping in and out of memory or cache and disk, indexes load many more pages into memory at once since they are smaller than data tables and thus indexes can be traversed much more rapidly that data table spaces. Different databases handle this in different ways.
There are different types of indexes available.
Indexes can be unique or non-unique. Unique indexes are generally used as primary keys (not always) and non-unique indexes contain duplications that are generally used for database access.
Clustered indexes are generally data table space forms of hashing or btree algorithms. A clustered index clusters or groups the actual data table rows of the table where the actual data rows reside in the leaf pages of the index.
A non-clustered index sets the leaf pages of the index as pointers to the data pages containing the rows in the table.
Any type of indexes can be ordered as ascending or descending. Ascending indexes are an usual practice.
Now it is obvious that indexing does not make every database operation fast; operations other than read operations are even slowed because of indexing due to the increased efforts required to update index files in addition to the table.
What to Index
To unleash the maximum benefits from indexing while avoiding or minimizing the disadvantages of indexing, what to index or what not to index should be carefully decided. The following are rules of thumb:
Use indexes where frequent queries are performed:
- Columns are used in joins.
- Columns are used in where clauses or groupby.
- Columns are used in order-by clauses.
- The group-by clause can be enhanced by indexing when the range of values being grouped is small in relation to the number of rows in the table selected.
Indexes will degrade performance of inserts and updates, sometimes substantially. Following are a few such cases:
- Tables with a small number of rows
- Static tables
- Columns with a wide range of values
- Tables changed frequently and with a low amount of data access
- Columns not used in data access query select statements
- Tables having no fixed width or numeric data column to index or indexing not done on that column
Types of Indexes
There are several types of indexes and each has its own merits and demerits. Clustered indexes are indexes made in the table itself and not in a separate index file and hence with this type of indexes the entire table is sorted as per the indexed column. This is the primary difference between clustered and non-clustered indexes. In non-clustered indexes, the index is created on one or more column and then column value it referenced to record in table. So in non-clustered indexes are primarily make two operations i.e. locating key in index and then locating referenced record in table unlike directly searching record in clustered indexes. Due to this fact clustered indexes are a better choice than non-clustered indexes and hence it is recommended to have a clustered index if only one index is required or that the most frequently used column or primary key should be a clustered index.
Clustered indexes also have a few disadvantages. When an update action changes the value of the indexed column, it will essentially require two actions i.e. delete from previous position and then insert at next position as per the updated value.
A composite index is an index on two or more columns, which can be either clustered or non-clustered. Usually composite indexes are not advised, except in the following cases:
- To cover a query: To cover all columns in a query so that the database will not search for the reference in database and just scanning the column will serve the purpose.
- To meet the specific (complex) search criteria e.g. the primary key itself is a combination of two or more columns.
Some tips for indexing:
Table column selection
A column that can be easily indexed should be chosen.
Numeric values are easier to index than strings and hence indexes on integer values are much faster.
If an index needs to be created on a string field it should be fixed width and not varchar, etc.
Never create indexes based on dates.
Dynamics AX has an automatically generated RecID for most of the tables. Indexing on that column is advisable if another column does not qualify.
Numbered columns (to make composite key) to be included in index
Try minimizing the use of composite keys. If no other single column qualifies, RecId can be a good choice for indexing.
The number of columns in an index should be minimized so that a search criterion is as simple as possible.
A maximum of three columns are recommended for a composite key.
Number of indexes
There is no limit to the maximum number of indexes on a table. However, it is recommended that indexes should be limited to only those columns that are frequently used in the where clause of select queries.
Too many indexes will slow the overall operation as one insert or update operation will result in many insert or update operations due to an insert or update being required in each index files beside table.
Index column size
The column or combination of columns on which an index is based is known as the key. The key size should be as small as possible to unleash the benefits of indexing in performance optimization. The key size can be reduced by:
Using the minimum number of columns in the index
Using the minimum data size of the column used as index key
Using numeric keys instead of other data types such as varchar, strings, etc.
Distinct index keys
Index keys should be chosen so that the duplicated values for the key are minimal. The best case is that the key is unique.
The following are the best practices regarding the use of select statements:
- Use joins instead of multiple nested while selects.
- Use field lists where applicable.
- Use select or aggregate functions where applicable.
- Use delete_from (instead of while, select, ... delete()).
- Select from the cache where possible.
The key principles for the best practices related to database transactions are that the number of database transactions should be minimized and those that are needed should be conducted in minimal time and deadlock must be avoided. Hence the following best practices can be derived:
- Never wait for a user interaction inside a transaction.
- If several operations must be performed before data returns to a consistent state, perform all the operations in one transaction.
- Make transactions as short and small as possible to avoid deadlocks and large rollback logs.
- Avoid deadlocks or lengthy locks. Explicitly select records to be updated in a specific order within the table (preferably the order of the key), each time, throughout the application. Select records for update from different tables in the same order, each time, throughout the application.
- Do not write or update if the record has not been changed.
- To avoid lengthy locks on commonly used records the following best practices are further recommended:
- Use Optimistic Concurrency Control (OCC).
- Update locks on central, commonly used (updated) records that represent bottlenecks in the application.
- Try to avoid updating commonly used records inside transactions.
- Structure the design so that you can use inserts instead of updates. Inserts do not lock objects.
- Place the select statement for updates as close to the ttsCommit statement as possible, to reduce the amount of time records are locked.
Use Joins in Forms
The use of join in forms can offer distinct advantages in comparison to use of display methods that contains select queries. In one call all records can be fetched for a form showing approximately twenty grid lines. An additional advantage of using join is that it is very easy to sort, filter, and find on the joined fields.
AOS Performance Optimization
The key principle behind AOS optimization is to minimize the number of calls between the client and the server. The amount of data transported in each call is secondary to the number of calls. A few of best practices are:
- Achieve select discount.
- Use containers to reduce the client or server calls.
Achieve Select Discount
Whenever a select statement needs to be executed from a data source located at another tier, the returned record has certain minimum size, which means multiple records can be fetched in a single call and hence less round trip calls to AOS are needed for a given number of records. This is phenomenon known as 'Select Discount'.
If only one record needs to be fetched, the number of records can be minimized to the first record only.
Using Field Groups in Tables
The field groups are an important feature in Dynamics AX design, which is not only used to synchronise forms and reports with the concerned tables (while adding, deleting, or modifying any field of a table), but also for many other purposes such as caching and performance optimization. The following are a few advantages of using field groups in a table:
- IntelliMorph can automatically update the layout of all related forms and reports whenever any change is made to the field groups.
- It is easy to upgrade new forms and reports that have identical field groups to the standard table i.e. the custom objects in standard field groups will also be updated in the upgraded version.
- The sequence of fields can be used to determine the sequence of fields in forms and reports and hence the same order of fields can be assured in all concerned forms and reports related to a table.
- Identical field groups on tables and forms help in AOS optimization.
Maintaining Auto Property Settings
In Dynamics AX many properties should have their values set to Auto, which can enable the application object to accommodate any changes automatically as per the kernel's interpretation about various things such as locale, etc. Some advantages of the Auto property settings are as follows:
- Auto selection of date and time format as per the locale settings of the client
- Auto selection of currency code
- Form or report layout control as per the rights available to the logged-in user
In this article we discussed the the Application Design Standards best practices for Dynamics AX. The second part will discuss the Shared Standards and AOT Object Standards.