One important issue, not only for optimization but also for usability, is to select the correct data retrieval method based on what you would like to achieve.

Use queries when you want the users to be able to change the range of data to be retrieved and when the selection criteria are simple enough for the query to handle.

If the selection criteria are complex and there is no need for the users to be able to change the selection criteria, you should opt for a select statement.

If you would like to be able to use the query definition at multiple places, you should create a query in AOT instead of writing it in X++ every time you need to use it. It can also be easier to get a visual overview of a query created in AOT compared to a query written in X++.

Also, if the selection criteria are complex, there is no need to update or delete the data selected. If you would like to be able to use the same selection in many places in the application, you should consider creating a view in AOT instead of writing the select statement every time.

The first and perhaps the most important thing to do in order to optimize the data retrieval is actually very simple: you should only fetch the data that you need. This means that you should eliminate any select statements that you don't really need and you should also fetch only the fields that the program really needs.

In order to do this, you should never use the select statement like this unless you actually need all fields in the record(s) selected:

select carTable; // Is the same as select * from carTable

You should rather select only the fields needed from the table with the following command:

select CarBrand, Model from carTable;

This will reduce the amount of data that has to be transferred from the database and to the application layer and thus reduce the time needed to wait for the data to be transferred.

Another thing you need to consider is the use of indexes and perhaps even adding the missing indexes. This part could actually cover a book all by itself, but I'll try to explain the most important things you need to know about the use of indexes in AX.

As we already covered in Chapter 3, Storing Data, all tables should have a unique index that represents the primary key of the tables.

If a lot of data selection is done to the table using constraints other than the primary key, consider creating an index for those constraints as well. However, creating too many indexes on a table to optimize the speed when searching for data may slow down the operations of inserting, updating, and deleting data from the table as all of the indexes must be updated as well.

When using views, the data retrieved is limited to the fields in the Fields node of the view. This means that the field select can easily be narrowed down when joining several tables. Also, joining several tables will execute faster because the select statement is already compiled and exists in the database layer when the view is executed, as opposed to queries that have to compile the select statement at runtime.

There are many different ways of optimizing the retrieval of data from the database, of which the most obvious are discussed in the preceding sections. There are also other ways of optimization that haven't been discussed here.

The CacheLoopup property on the tables in AX specifies which type of caching is used for the table. If the wrong type of cache is used, it can give a significant decrease in performance when the table is accessed.

Find options such as firstonly, forcePlaceholders, and forceLiterals can also give a performance boost in certain occasions. Please refer to the SDK for more information regarding caching and find operations.