Introduction

The partial records feature introduced by Microsoft in earlier runtimes revolutionizes query performance against SQL backend in Dynamics 365. It offers granular control over requested fields, optimizing database reads for enhanced efficiency. In this article, we delve into the nuances of this feature and its implications for developers. Specifically around the use of SetLoadFields.

Understanding Partial Records

Partial records automatically include primary key fields, filtered fields, and system fields (SystemCreatedAt, SystemCreatedBy, SystemModifiedAt, SystemModifiedBy) in the result set. However, it’s noteworthy that specifying a field already in the primary key excludes all other fields, a detail that I did not find in the documentation.

Documentation Gaps

While Microsoft’s documentation provides comprehensive insights, it overlooks the specificity of selecting fields already in the primary key. This detail becomes crucial in scenarios like retrieving specific data points from tables with extensive fields.

Example

Consider retrieving the last line number from a table with numerous fields. While the line number is part of the primary key, specifying it in the SetLoadFields call excludes all other fields, streamlining query execution for optimal performance.

Take the following fictitious table as an example:

table 50001 "Complaint Line"
{
    Caption = 'Complaint Line';
    DataClassification = CustomerContent;
    DrillDownPageId = "Complaint Lines";
    LookupPageId = "Complaint Lines";

    fields
    {
        field(1; "Document No."; Code[20)
        {
            Caption = 'Document No.';
	    TableRelation = "Complaint Header".Code;
        }
        field(2; "Line No."; Integer)
        {
            Caption = 'ID';
        }
        field(3; Description; Text[100])
        {
            Caption = 'Description';
        }
        field(4; "Complaint Type"; Enum "Complaint Types")
        {
            Caption = 'Complaint Type';
        }
        field(5; Agent; Code[20])
        {
            Caption = 'Agent';
        }
        field(7; "Target Close Date"; Date)
        {
            Caption = 'Target Close Date';
        }
	<lots more fields>
    }

    keys
    {
        key(PK; "Document No.", "Line No.")
        {
            Clustered = true;
        }
    }
}

This table is related to a Complaint Header table, via the Document No. field. Say we have a method that, filters the Complaint Line table to get the last line number (where it exists), this might look something like:

procedure GetNextLineNo(ComplaintHeader: Record "Complaint Header"): Integer
var
	ComplaintLine: Record "Sales Line";
begin
	ComplaintLine.SetRange("Document No.", ComplaintHeader."No.");
	if ComplaintLine.FindLast() then
		exit(ComplaintLine."Line No." + 10000);

	exit(10000);
end;

When this method is called, we would get SQL something like the following (note the lots more fields would be all the other fields for the table):

SELECT
    TOP (1) "50001"."timestamp",
    "50001"."Document No_",
    "50001"."Line No_",
    "50001"."Description",
    "50001"."Complaint Type",
    "50001"."Agent",
    "50001"."Target Close Date",
    <lots more fields>
    "50001"."$systemId",
    "50001"."$systemCreatedAt",
    "50001"."$systemCreatedBy",
    "50001"."$systemModifiedAt",
    "50001"."$systemModifiedBy"
FROM
    "default".dbo."CRONUS USA, Inc_$Complaint Line$afe53e44-9b7c-46a9-a2d3-d349b99a0472" "50001" WITH(READUNCOMMITTED)
WHERE
    ("50001"."Document No_" = @0)
ORDER BY
    "Document No_", "Line No_" DESC OPTION(FAST 50)

Now if we update the GetNextLineNo method to include the SetLoadFields call on the “Line No.” field as follows:

procedure GetNextLineNo(ComplaintHeader: Record "Complaint Header"): Integer
var
	ComplaintLine: Record "Sales Line";
begin
	ComplaintLine.SetLoadFields("Line No.");
	ComplaintLine.SetRange("Document No.", ComplaintHeader."No.");
	if ComplaintLine.FindLast() then
		exit(ComplaintLine."Line No." + 10000);

	exit(10000);
end;

We now get a more concise query, which looks like:

SELECT
    TOP (1) "50001"."timestamp",
    "50001"."Document No_",
    "50001"."Line No_",
    "50001"."$systemId",
    "50001"."$systemCreatedAt",
    "50001"."$systemCreatedBy",
    "50001"."$systemModifiedAt",
    "50001"."$systemModifiedBy"
FROM
    "default".dbo."CRONUS USA, Inc_$Complaint Line$afe53e44-9b7c-46a9-a2d3-d349b99a0472" "50001" WITH(READUNCOMMITTED)
WHERE
    ("50001"."Document No_" = @0)
ORDER BY
    "Document No_", "Line No_" DESC OPTION(FAST 50)

Conclusion

Notice that the Description, Complaint Type, Agent, Target Close Date and <lots more fields> are no longer included in the request. Now this could already be clear for others reading the Microsoft Learn article; however, for me I didn’t immediately pick up on this use case.

Understanding and leveraging the partial records feature is paramount for maximizing query performance in Dynamics 365 development. By strategically selecting fields and optimizing database reads, developers can unlock the full potential of their applications.