[ Send this article ]
Your message has been sent.
How would you like to send this article:
[ Save this article ]
Save this article to your account for easy access.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Complete the form below to send this article, Searching for Data in Microsoft Dynamics AX 2009: Part 3, to a friend (or to yourself). We will never share your details (or your friend's) with anyone. For more information, read our Privacy Policy.
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:
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:
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:
- 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:
- 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:
- 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:
- 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:
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.
If you have read this article you may be interested to view :
- Working with Microsoft Dynamics AX and .NET: Part 2
- Working with Microsoft Dynamics AX and .NET: Part 1
- Customizing Headers and Footers with MS Office Live Small Business
- Searching for Data in Microsoft Dynamics AX 2009: Part 1
- Searching for Data in Microsoft Dynamics AX 2009: Part 2
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
|
|
Post new comment