After a bit of a delay, here is the latest Microsoft Dynamics NAV design pattern, brought to you by the NAV Design Patterns team.
This pattern shows how the new query object type introduced in Microsoft Dynamics NAV 2013 allows you to replace costly loops when inspecting data from two or more tables.
One of the core operations in a relational database is joining two or more tables. For example, you might need to extract all sales lines in the database together with information regarding the related sales header. This requires joining the Sales Header and Sales Line tables using Sales Header No. as the connecting field.
The join operation has traditionally been done in C/AL by record looping. When Microsoft Dynamics NAV 2013 introduced the query object, it allowed us to produce a data set that is the result of a join operation between two or more tables. This simplifies the problem of finding related records in two tables linked through a foreign key.
1. Two or more tables that contain records linked through a foreign key: Table 1, Table 2, Table n.
2. A query object Query X, that joins Table 1, Table 2, etc. based on the connecting key.
3. A processing codeunit that loops through the query records (or any other code-bearing object).
1. Run the query on the connected tables.
2. Loop through the records returned by the query.
3. Process the records.
The following diagram illustrates the elements of the pattern.
The Bank Acc. Reconciliation Line table (274) and the Bank Account Ledger Entry table (271) are connected through the Bank Account No. field. Identify the matching pairs of records based on having the same remaining amount and transaction date.
The classic C/AL approach is to:
1. Set the necessary filters on the left table, i.e. table 274.
2. Loop through the filtered records.
3. For each record in the filter, find the related records in the right table (table 271) and set the required filters on it.
4. For each pair of records from the left and right table, decide if they are a solution and if so, apply them to each other.
BankAccRecLine@1005 : Record 274;
BankAccLedgerEntry@1006 : Record 271;
BankAccEntrySetReconNo@1007 : Codeunit 375;
BankAccRecLine.SETRANGE(Type,BankAccRecLine.Type::"Bank Account Ledger Entry");
IF BankAccRecLine.FINDSET THEN
BankAccLedgerEntry.SETRANGE("Bank Account No.",BankAccRecLine."Bank Account No.");
BankAccLedgerEntry.SETRANGE("Statement Status",BankAccLedgerEntry."Statement Status"::Open);
IF BankAccLedgerEntry.FINDSET THEN
IF (BankAccRecLine.Difference = BankAccLedgerEntry."Remaining Amount") AND (BankAccRecLine."Transaction Date" = BankAccLedgerEntry."Posting Date") THEN BankAccEntrySetReconNo.ApplyEntries(BankAccRecLine,BankAccLedgerEntry, Relation::"One-to-One");
UNTIL BankAccLedgerEntry.NEXT = 0;
UNTIL BankAccRecLine.NEXT = 0;
The new query-based approach involves:
1. Define a query that returns the full filtered join of tables 271 and 274.
3. For each query record, decide if it represents a solution and then connect the two table records that formed it through an application.
BankRecMatchCandidates@1001 : Query 1252;
BankRecMatchCandidates.SETRANGE(Rec_Line_Bank_Account_No,BankAccReconciliation."Bank Account No.");
IF NOT BankRecMatchCandidates.OPEN THEN
Read more on NAV Design Patterns Wiki...
Bogdan Sturzoiu, at Microsoft Development Center Copenhagen
How is this translated on the sql side compared to the nested loop? In terms of performances which approach is creating less load on the server?
When using an example you should use one that actually works.
Where does the BankAccReconcillation record variable come from?
In the classic C/AL approach there is no initial filtering on any bank account no. or statement no. Why is it necessary to filter on these in the query solution?
When reading more on NAV Design Patterns Wiki the WHILE part is also incorrect:
WHILE BankRecMatchCandidates.READ DO
Where do the record variables BankAccRecLine and BankAccLedgerEntry come from? The query does not return these!
A performance comparison would be great. Sadly I am using queries not as much as i would like to do. Mostly because the customer has to purchase the queries separately.
I updated the code sample on the NAV Design Patterns Wiki, a few code lines were missing. Both implementations should be from the perspective of the Bank Acc. Reconciliation (the header record), hence the filtering to narrow down the Bank Rec Line - Bank Ledger Entry pairs.
@Gianluca: Using the SQL Server Profiler, or the Dynamics NAV application profiler (available at navappprofiler.codeplex.com), we can see that the query call in COD1252 translates into one SELECT statement (SELECT ISNULL(""Bank_Acc_Reconciliation_Line"".""Bank Account No_"",@2) AS ""Rec_Line_Bank_Account_No"",ISNULL(""Bank_Acc_Reconciliation_Line"".""Statement No_"",@2) AS ""Rec_Line_Statement_No"",ISNULL(""Bank_Acc_Reconciliation_Line"".""Statement Line No_"",@3) AS "", etc - the full statement is pretty long).
So there is only one select statement being executed. The iterative approach, used in COD1250, requires different select statements for the left and right side of the query, and then an in-memory C/AL loop.
Comparing the performance requires, of course, having code that does exactly the same, but to give you an idea, when running the two similar matching processes in COD1250 (iterative) and COD1252 (query), I got a 3:1 duration ratio. This is consistent with an example I've seen in charts, where the query based ones were around 4 times faster (both running on Cust. Ledger Entry table).
Again, this is purely empirical, as I haven't done a structured analysis. But the mere fact that you only talk to SQL server once should reduce the load.