Writing efficient SQL statements

Rumman Ansari   Software Engineer   2024-07-11 05:37:13   190  Share
Subject Syllabus DetailsSubject Details
☰ TContent
☰Fullscreen

Table of Content:

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

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 find method with different logic and queries and discuss each of them. The method will locate the CustGroup table record of the given customer account.

How to do it

  1. As methods are not allowed on extensions, to demonstrate this recipe we need to over layer a CustGroup table. Add a CustGroup table in your project, and create the following method:
    2. 
/// <summary> 
    ///  
    /// </summary> 
    /// <param name = "_custAccount"></param> 
    /// <param name = "_forupdate"></param> 
    /// <returns></returns> 
     public static CustGroup AnsarifindByCustAccount(CustAccount _custAccount, boolean _forupdate = false) 
    { 
      CustTable custTable; 
      CustGroup custGroup; 
 
      if (_custAccount) 
      { 
        select firstOnly CustGroup from custTable 
        where custTable.AccountNum == _custAccount; 
      } 
      if (custTable.CustGroup) 
      { 
        if (_forupdate) 
        { 
            custGroup.selectForUpdate(_forupdate); 
        } 
 
        select firstOnly custGroup where 
          custGroup.CustGroup == custTable.CustGroup; 
      } 
      return custGroup; 
    }
  2. In the same table, create another method with the following code snippet:
    3. 
  /// <summary> 
    ///  
    /// </summary> 
    /// <param name = "_custAccount"></param> 
    /// <param name = "_forupdate"></param> 
    /// <returns></returns> 
    public static CustGroup AnsarifindByCustAccount2( CustAccount _custAccount, boolean _forupdate = false) 
   { 
     CustTable custTable; 
     CustGroup custGroup; 
 
     if (_custAccount) 
     { 
       if (_forupdate) 
       { 
          custGroup.selectForUpdate(_forupdate); 
       } 
       select firstOnly custGroup exists 
       join custTable 
       where custGroup.CustGroup == custTable.CustGroup 
         && custTable.AccountNum == _custAccount; 
     } 
     return custGroup; 
   }

How it works

In this recipe, we have two different versions of the same method. Both methods are technically correct, but the second one is more efficient. Let's analyze each of them.

In the first method, we should pay attention to the following points:

  • Verify that the _custAccount argument is not empty; this will avoid the running of an unnecessary database query.
  • Use the firstOnly keyword in the first SQL statement to disable the effect of the read-ahead caching. If the firstOnly keyword is not present, the statement will retrieve a block of records, return the first one, and ignore the others. In this case, even though the customer account is a primary key and there is only one match, it is always recommended that you use the firstOnly keyword in the find() methods.
  • In the same statement, specify the field list--the CustGroup field--we want to retrieve, instructing the system not to fetch any other fields that we are not planning to use. In general, this can also be done on the Application Object Tree query objects by setting the Dynamic property of the Fields node to No in the query data sources and adding only the required fields manually. This can also be done in forms by setting the OnlyFetchActive property to Yes in the form's data sources.
  • Execute the selectForUpdate() method only if the _forupdate argument is set. Using the if statement is more efficient than calling the selectForUpdate() method with false.

The second method already uses all the discussed principles, plus an additional one:

  • Both the SQL statements are combined into one using an exists join. One of the benefits is that only a single trip is made to the database. Another benefit is that no fields are retrieved from the customer table because of the exists join. This makes the statement even more efficient.

There's more

To understand the preceding code in more depth, let's try to analyze the execution using one of the earlier recipes code. Carry out the following:

Add a new class in your project and write the following code:


class AnsariCustGroupAction 
    { 
      public static void main(Args args) 
      { 
        CustGroup       custGroup; 
        int64                   timeTaken; 
        System.DateTime         dateTimeNow; 
        System.TimeSpan         timeSpan; 
        utcdatetime             startDateTimeUTC; 
 
        startDateTimeUTC = DateTimeUtil::utcNow(); 
        CustGroup   = CustGroup::AnsariFindByCustAccount("10"); 
        // sleep(1000); // pause for 1000 milliseconds 
        dateTimeNow       =   System.DateTime::get_UtcNow(); 
        timeSpan          =  dateTimeNow.Subtract(utcDateTime2SystemDateTime(startDateTimeUTC)); 
        timeTaken         =   timeSpan.get_TotalMilliseconds(); 
 
        info(strFmt("Method 1 time%1", timeTaken )); 
 
        startDateTimeUTC = DateTimeUtil::utcNow(); 
        CustGroup   = CustGroup::AnsarifindByCustAccount2("10"); 
        // sleep(1000); // pause for 1000 milliseconds 
        dateTimeNow       =   System.DateTime::get_UtcNow(); 
        timeSpan          =   dateTimeNow.Subtract(utcDateTime2SystemDateTime(startDateTimeUTC)); 
        timeTaken         =   timeSpan.get_TotalMilliseconds(); 
        info(strFmt("Method 2 time%1", timeTaken)); 
      } 
    }

Now, set your class and project as startup object/project. Save all your code and run the solution. You will get the following results:

Analyze the execution
Figure: Analyze the execution

Method 1, where we used different if statements to validate some conditions clearly shows that it took 1203 ms, while Method 2, where we used if statement wisely to optimize the code and execution time, took 344 ms. This is how you must use efficient code during development to reduce the time taken and speed up your execution time for better performance.