NAV TechDays 2016- Grouping Data in Microsoft Dynamics NAV C/AL

0 Shares

In November I attended NAV TechDays 2016 in Antwerp and one of my favorite sessions was the one held by Gunnar Gestsson about migrating to events. You can see and download the recording following this link from his blog.

I liked a lot the efficiency test on 4 grouping methods that he made in the last part of the session, so I decided to write this post about it.

Basically, what we want to achieve is to group the data from Customer table by a condition and insert the result in a new table. Which is the most efficient way of doing this ? Gunnar presented 4 methods and I will explain each of them starting with the least effective and ending with the most effective one.

For example we want to insert into the “Customer Grouping Test” table the “Customer Country/Region Code” and the number of Customers from each region, like in the below screen:

The first method is the slowest one and we can call it “the legacy method” because this is the “old way” of doing things. In this method we loop through the Customer table and if the “Country/Region Code” does not exist in the “Grouping” table, we insert a new record, if it exists, we only update the “No Of Records” from that region. This is slow and not recommended because we have a lot of read and write operations against the database:

LOCAL Legacy Method()
Grouping.DELETEALL;
WITH Customer DO BEGIN
  FINDSET;
  REPEAT
    IF NOT Grouping.GET("Country/Region Code") THEN BEGIN
      Grouping.INIT;
      Grouping."Country/Region Code" := "Country/Region Code";
      Grouping.Description := FIELDCAPTION("Country/Region Code");
      Grouping."METHOD USED" := 'Legacy';
      Grouping.INSERT;
    END;
    Grouping."No Of Customer Entries" += 1;
    Grouping."Percent %" := Grouping."No Of Customer Entries" / Customer.COUNT * 100;
    Grouping.MODIFY;
  UNTIL NEXT = 0;
END;

In the second method we insert the grouped data in a temporary table, we read it from the memory (not from the database as we did in the first method) with the “Get” function and at the end we copy all the grouped data from the temporary table to the real table. This is more efficient than the “Legacy method” and it’s very useful in a lot of situations:

LOCAL Temp Table Method()
Grouping.DELETEALL;
WITH Customer DO BEGIN
  FINDSET;
  REPEAT
    IF NOT TempGrouping.GET("Country/Region Code") THEN BEGIN
      TempGrouping.INIT;
      TempGrouping."Country/Region Code" := "Country/Region Code";
      TempGrouping.Description := FIELDCAPTION("Country/Region Code");
      TempGrouping."METHOD USED" := 'Temp Table';
      TempGrouping.INSERT;
    END;
    TempGrouping."No Of Customer Entries" += 1;
    TempGrouping."Percent %" := TempGrouping."No Of Customer Entries" / Customer.COUNT * 100;
    TempGrouping.MODIFY;
  UNTIL NEXT = 0;
END;

IF TempGrouping.FINDSET THEN
  REPEAT
    Grouping := TempGrouping;
    Grouping.INSERT;
  UNTIL TempGrouping.NEXT= 0;

For the third method we use a Query. We simply create a new query with the needed data and then we read the data from the query and insert each row to the table. This method is very fast, faster than the first 2 methods when we work with large datasets:

LOCAL Query Method()
Grouping.DELETEALL;

GroupCustQuery.OPEN;
WHILE GroupCustQuery.READ DO BEGIN
  Grouping.INIT;
  Grouping."Country/Region Code" := GroupCustQuery.Country_Region_Code;
  Grouping.Description := Customer.FIELDCAPTION("Country/Region Code");
  Grouping."No Of Customer Entries" := GroupCustQuery.Count_ ;
  Grouping."Percent %" := GroupCustQuery.Count_ / Customer.COUNT * 100;
  Grouping."METHOD USED" := 'Query';
  Grouping.INSERT;
END;

–> The Query looks like this:

The last method is by using “a trick” in the C/AL code so that very few read and write operations are done and many records are skipped. We first need to Sort the Customer variable by the “Country/Region Code” and then we find the first customer record. Next, we use a repeat in order to loop through the customers. Inside the repeat, we first filter the Customer Record on the first “Country/Region Code” and then we insert the data related to this region into the “Grouping” table. In the end we go to the last record from the filtered Customer record and we release the filter we set on the “Country/Region Code” field. By doing this last step, the next record in the repeat loop will be the next “Country/Region Code”. This way we skip all the “duplicate” records. The “Skip” method is faster even than the “Query method”, so this demonstrates that sometimes the most efficient way of doing things is to be creative and write “smart code”:

LOCAL Skip Method()
Grouping.DELETEALL;
NoOfCust := Customer.COUNT;

WITH Customer DO BEGIN
  SETCURRENTKEY("Country/Region Code");
  FINDFIRST;
  REPEAT
    SETRANGE("Country/Region Code", "Country/Region Code");
    Grouping.INIT;
    Grouping."Country/Region Code" := "Country/Region Code";
    Grouping.Description := FIELDCAPTION("Country/Region Code");
    Grouping."No Of Customer Entries" := COUNT;
    Grouping."Percent %" := COUNT / NoOfCust * 100;
    Grouping."METHOD USED" := 'Skip';
    Grouping.INSERT;
    FINDLAST;
    SETRANGE("Country/Region Code");
  UNTIL NEXT = 0;
END;

You can see now the OnRun trigger of the Codeunit that I created to test this example presented at NAV TechDays 2016. This code calls all functions and tracks how long it takes for each method to complete the operation:

OnRun()
StartTime:= CURRENTDATETIME;
"Legacy Method";
EndTime:=CURRENTDATETIME;
TimeOfFunctions[1] := EndTime-StartTime;

StartTime:= CURRENTDATETIME;
"Temp Table Method";
EndTime:=CURRENTDATETIME;
TimeOfFunctions[2] := EndTime-StartTime;

StartTime:= CURRENTDATETIME;
"Query Method";
EndTime:=CURRENTDATETIME;
TimeOfFunctions[3] := EndTime-StartTime;

StartTime:= CURRENTDATETIME;
"Skip Method";
EndTime:=CURRENTDATETIME;
TimeOfFunctions[4] := EndTime-StartTime;

MESSAGE('Done Legacy in: %1\Done Temp in: %2\Done Query in: %3\Done Skip in %4',
TimeOfFunctions[1],TimeOfFunctions[2],TimeOfFunctions[3],TimeOfFunctions[4]);