Searching for Data in Microsoft Dynamics AX 2009: Part 3

Get to grips with Microsoft Dynamics AX 2009 development quickly to build reliable and robust business applications with this book and eBook

Exists join

An exists join does pretty much the same as the inner join, except one important thing; it does not fetch the records from the joined table. This means that the RentalTable variable cannot be used within the while loop in the following example, as it will never have any data:

static void selectExistsJoin(Args _args)
{
CarTable carTable;
RentalTable rentalTable;
;
while select carTable
exists join rentalTable
where rentalTable.CarId == carTable.CarId
{
info(strfmt("CarId %1 has a matching record in rentalTable",
CarTable.CarId));
}
}

Executing this Job will result in the following output to the Infolog:

Microsoft Dynamics AX 2009 Programming: Getting Started

NotExists join

Obviously the notexists join is the opposite of the exists join. This means that it will return all records from the main table where there does not exist a record in the joined table as described by the where clause. This means that the following example will produce the opposite result from the previous example:

static void selectNotExistsJoin(Args _args)
{
CarTable carTable;
RentalTable rentalTable;
;
while select carTable
notexists join rentalTable
where rentalTable.CarId == carTable.CarId
{
info(strfmt("CarId %1 does not has a matching record in
rentalTable", CarTable.CarId));
}
}

Executing this job will result in the following output to the Infolog:

Microsoft Dynamics AX 2009 Programming: Getting Started

Writing aggregate select statements

In many cases, you would like to write select statements that return aggregate data like the sum or average of a field in a set of data. You can also use the count aggregate option to count the number of records in a table matching a where statement (if any). The minof and maxof options can be used in the same way to find the minimum or maximum value of a field in a record set that corresponds to the where statement.

These examples show how the different aggregate options can be used:

  • sum
    static void selectSumMileage(Args _args)
    {
    CarTable carTable;
    ;
    select sum(Mileage) from carTable;
    info(strfmt("The total mileage of all cars is %1",
    carTable.Mileage));
    }

    Executing this Job will result in the following output to the Infolog:

    Microsoft Dynamics AX 2009 Programming: Getting Started

  • avg
    static void selectAvgModelYear(Args _args)
    {
    CarTable carTable;
    ;
    select avg(ModelYear) from carTable;
    info(strfmt("The average ModelYear is %1",
    carTable.ModelYear));
    }

    Executing this Job will result in the following output to the Infolog:

    Microsoft Dynamics AX 2009 Programming: Getting Started

  • count
    static void selectCountRentals(Args _args)
    {
    RentalTable rentalTable;
    ;
    select count(recId) from rentalTable;
    info(strfmt("There are %1 rentals registerred in the system",
    rentalTable.RecId));
    }

    Executing this Job will result in the following output to the Infolog:

    Microsoft Dynamics AX 2009 Programming: Getting Started

  • minof and maxof
    static void selectCountRentalsPerCustomer(Args _args)
    {
    RentalTable rentalTable;
    ;
    // Normal while select to loop data
    info ("Using while select:");
    // The result of the count operation is put
    // into the recId field of the tableBuffer
    // since it is an integerfield.
    while select count(recId) from rentalTable
    group by rentalTable.CustAccount
    {
    info(strfmt(" Customer %1 has rented cars %2 times",
    rentalTable.CustAccount, rentalTable.RecId));
    }
    // Looping the rentalTable cusrsor using the next command
    info ("Using next command:");
    select count(recId) from rentalTable
    group by rentalTable.CustAccount;
    while (rentalTable.RecId)
    {
    info(strfmt(" Customer %1 has rented cars %2 times",
    rentalTable.CustAccount, rentalTable.RecId));
    next rentalTable;
    }
    }

                                                                          

    Executing this Job will result in the following output to the Infolog:

    Microsoft Dynamics AX 2009 Programming: Getting Started

  • Group by
    In many cases, aggregate options are used together with the group by parameter in order to list the aggregate for each subpart of a table. In the next example we will find the number of rentals for each customer that has rented cars. I will also demonstrate how to use the next command together with the select statement instead of the while select statement to loop through the records in the result. You will most often see the while select statement being used in standard AX, but in case you see the next command, you will know it does the same as a while select statement. The following example shows how the group by aggregate option can be used:
    select carTable; // Is the same as select * from carTable

    Executing this Job will result in the following output to the Infolog:

    Microsoft Dynamics AX 2009 Programming: Getting Started

Optimizing the data retrieval

There can be several different steps that you, as a developer, should take in order to optimize the process of fetching data from the database to the application. I will try to cover the most important things for you to keep in mind here.

Using the correct data retrieval method

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 multiple places, you should create a query in the 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 the AOT compared to a query written in X++.

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

Field selects

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 need. You should only fetch those fields that the program really needs.

In order to do this you should not use the select statement such as the following unless you actually need all fields in the record(s) selected:

select CarBrand, Model from carTable;

Rather, you should select only the fields needed from the table as follows:

select CarBrand, Model from carTable;

This will reduce the amount of data that has to be transferred from the database to the application layer; thus reducing the time consumed for the data transfer.

Indexing

Another thing you need to consider is how to use indexes. Additionally, you may also need to consider 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.

All tables should have a unique index that represents the primary key of the tables.

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

Using views to optimize the data retrieval

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

Other ways to improve data retrieval

There are many different ways of optimizing data retrieval from the database. The most obvious were discussed in the previous sections.

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

Options such as firstonly, forcePlaceholders, and forceLiterals can also give a performance boost on certain occasions.

Please refer to the SDK for more information regarding caching and find operations.

Summary

In this article you learned how to write different kind of select statements in order to retrieve data from the database, and how to create queries and views. We also went through the reasons for choosing which of the data retrieval methods to use in different scenarios. We also looked at how to optimize the data retrieval, so that each transfer of data from the database to the application only contains the data necessary for the further operations in the application, and making sure that the correct index was used to find the data.

[ 1 | 2 | 3 ]

 

If you have read this article you may be interested to view :

 

Books to Consider

comments powered by Disqus
X

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free