Writing efficient SQL statements

In Dynamics AX, SQL statements can often become performance bottlenecks. Therefore, it is very important to understand how Dynamics AX handles database queries and follow all the best practice recommendations in order to keep your system healthy.

In this recipe, we will discuss some of the best practices to use when writing database queries. For demonstration purposes, we will create a sample method with several scenarios and will discuss each of them. The method will locate the CustGroup table record of the given customer account.

How to do it...

Carry out the following steps in order to complete this recipe:

1. 1. In the AOT, locate the CustGroup table and create the following method:

   public static CustGroup findByCustAccount(
   CustAccount _custAccount,
   boolean _forupdate = false)
   {
   CustTable custTable;
   CustGroup custGroup;
   if (_custAccount)
   {
   select firstOnly CustGroup from custTable
   where custTable.AccountNum == _custAccount;
   }
   if (custTable.CustGroup)
   {...