Searching for Data in Microsoft Dynamics AX 2009: Part 3

Exclusive offer: get 50% off this eBook here
Microsoft Dynamics AX 2009 Programming: Getting Started

Microsoft Dynamics AX 2009 Programming: Getting Started — Save 50%

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

$29.99    $15.00
by Erlend Dalen | December 2009 | .NET BPEL Microsoft SOA Web Services

Read Part One of Searching for Data in Microsoft Dynamics AX 2009: Part 1 here.

Read Part Two of Searching for Data in Microsoft Dynamics AX 2009: Part 2 here.

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.

Microsoft Dynamics AX 2009 Programming: Getting Started Get to grips with Microsoft Dynamics AX 2009 development quickly to build reliable and robust business applications with this book and eBook
Published: December 2009
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

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 :

 

Microsoft Dynamics AX 2009 Programming: Getting Started Get to grips with Microsoft Dynamics AX 2009 development quickly to build reliable and robust business applications with this book and eBook
Published: December 2009
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Erlend Dalen

Erlend Dalen started working as a developer with the first version of Axapta in 1998. From 2000 he spent 2 years developing eCommerce, mobile, and integration solutions in Java for a Norwegian IT consultancy company. He has worked for Columbus IT since 2002, first as a senior developer in Norway and in USA and now as the technology manager of the Norwegian branch, where his responsibilities have been to implement new technology areas, creating an eCommerce solution for Dynamics AX, and being the technology solution architect in internal and customer projects.

Books From Packt

RESTful Java Web Services
RESTful Java Web Services

Joomla! 1.5 SEO
Oracle SOA Suite Developer's Guide

SOA Patterns with BizTalk Server 2009
SOA Patterns with BizTalk Server 2009

Programming Microsoft Dynamics NAV 2009
Programming Microsoft Dynamics NAV 2009

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Microsoft Office Live Small Business: Beginner’s Guide
Microsoft Office Live Small Business: Beginner’s Guide

Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

WCF Multi-tier Services Development with LINQ
WCF Multi-tier Services Development with LINQ

No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
H
5
K
C
a
f
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software