I’ve been meaning for a while to blog about AL language methods and how the get translate into t-sql. In this blog post I will analyze three AL language methods and see how they get translated into sql statements by the AL compiler. The AL functions we’re going to go over are: SetLoadFields, IsEmpty and DataTransfer data type and functions.
SetLoadFields()
As per Microsoft documentation, SetLoadFields sets the fields to be initially loaded when the record is retrieved from its data source. This will overwrite fields previously selected for initial load.
Let’s look at the following code snippet, from the documentation:
Put a breakpoint in the code above and start debugger. Open Run and Debug blade and Database Statistics.
Open SQL0-SQL9 and analyze the sql statements.
I identified the SQL statement behind FindSet executed after the SetLoadFields:
SELECT "27"."timestamp","27"."No_","27"."Standard Cost","27"."$systemId","27"."$systemCreatedAt","27"."$systemCreatedBy","27"."$systemModifiedAt","27"."$systemModifiedBy"
FROM "db_bcprodus_t82269781_20220713_08392211_9f86".dbo."CRONUS USA, Inc_$Item$437dbf0e-84ff-417a-965d-ed2bb9650972" "27" WITH(READUNCOMMITTED)
ORDER BY "No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)The system is generating and executing a query on Item table including the primary key field “No.”,
and the system fields: SystemId, SystemCreatedAt, SystemCreatedBy, SystemModifiedAt, SystemModifiedBy and of course, the field included in SetLoadFields: “Standard Code”.
But what if we are not including the SetLoadFields?
SELECT "27"."timestamp","27"."No_","27"."No_ 2","27"."Description","27"."Search Description","27"."Description 2","27"."Base Unit of Measure","27"."Price Unit Conversion","27"."Type","27"."Inventory Posting Group","27"."Shelf No_","27"."Item Disc_ Group","27"."Allow Invoice Disc_","27"."Statistics Group","27"."Commission Group","27"."Unit Price","27"."Price_Profit Calculation","27"."Profit _","27"."Costing Method","27"."Unit Cost","27"."Standard Cost","27"."Last Direct Cost",
...
,"27"."$systemId","27"."$systemCreatedAt","27"."$systemCreatedBy","27"."$systemModifiedAt","27"."$systemModifiedBy" FROM "db_bcprodus_t82269781_20220713_08392211_9f86".dbo."CRONUS USA, Inc_$Item$437dbf0e-84ff-417a-965d-ed2bb9650972" "27" WITH(READUNCOMMITTED) JOIN "db_bcprodus_t82269781_20220713_08392211_9f86".dbo."CRONUS USA, Inc_$Item$c526b3e9-b8ca-4683-81ba-fcd5f6b1472a" "27_e8" WITH(READUNCOMMITTED) ON ("27"."No_" = "27_e8"."No_") ORDER BY "No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)The system selects all fields in the Item table.
In comparison, with SetLoadFields the system spends 8 ms:
IsEmpty()
Let’s try this code snippet:
if Item.IsEmpty then
Message('No items');
And a closer look at the SQL Statement:
SELECT TOP 1 NULL FROM "db_bcprodus_t82269781_20220713_08392211_9f86".dbo."CRONUS USA, Inc_$Item$437dbf0e-84ff-417a-965d-ed2bb9650972" "27"
WITH(READUNCOMMITTED) OPTION (OPTIMIZE FOR UNKNOWN)“Select top 1 null” meaning is that the system is trying to select 1 random record from the table and if this is successful will fetch “NULL” fields i.e. no fields.
For comparison run “select top 1 null” on a table with records and on a record with no records.
DataTransfer data type and its methods
Microsoft documentation around DataTransfer has an example. I followed it and created 2 tables: Source and Dest and an upgrade codeunit to pass the values of some fields between the 2 tables:
table 50100 Source
{
DataClassification = CustomerContent;
fields
{
field(1;PK; Integer)
{
DataClassification = CustomerContent;
}
field(2; S1; Code[1])
{
DataClassification = CustomerContent;
}
field(3; S2; Code[1])
{
DataClassification = CustomerContent;
}
field(4; S3; Integer)
{
DataClassification = CustomerContent;
}
} ...
table 50101 Dest
{
DataClassification = CustomerContent;
fields
{
field(1;PK; Integer)
{
DataClassification = CustomerContent;
}
field(2; D1; Code[1])
{
DataClassification = CustomerContent;
}
field(3; D2; Code[1])
{
DataClassification = CustomerContent;
}
field(4; D3; Integer)
{
DataClassification = CustomerContent;
}
}...
And this is the upgrade codeunit:
codeunit 50100 “ABC Upgrade Shoe Size”
{
Subtype = Upgrade;
trigger OnUpgradePerCompany()
var
_s : Record Source;
_d : Record Dest;
_dt : DataTransfer;
begin
_dt.SetTables(Database::Source,Database::Dest);
_dt.AddFieldValue(_s.FieldNo(“S3”), _d.FieldNo(“D3”));
_dt.AddSourceFilter(_s.FieldNo(“S2”), ‘=%1’, ‘A’);
_dt.AddJoin(_s.FieldNo(“PK”), _d.FieldNo(“PK”));
_dt.CopyFields();
end;
}
I also inserted the data in the tables just like in the example in the documentation:
I identified the SQL function executed after CopyFields below:
DECLARE @RCount INT;
SELECT "50100"."PK","50100"."S3" INTO #MOVEFIELDS
FROM "db_bcprodus_t82269781_20220713_08392211_9f86".dbo."CRONUS USA, Inc_$Source$dbacf139-006b-4590-956d-1bd350f94a7c" "50100" WHERE
("50100"."S2"=@2)
UPDATE "50101"
SET
"50101"."D3"=#MOVEFIELDS."S3",
"50101"."$systemModifiedAt"=@0,
"50101"."$systemModifiedBy"=@1
FROM "db_bcprodus_t82269781_20220713_08392211_9f86".dbo."CRONUS USA, Inc_$Dest$dbacf139-006b-4590-956d-1bd350f94a7c" "50101"
JOIN #MOVEFIELDS ON
("50101"."PK"=#MOVEFIELDS."PK")
SET @RCount = IIF(@@ROWCOUNT < @RCount, @RCount, @@ROWCOUNT);
DROP TABLE IF EXISTS #MOVEFIELDS;
SELECT @RCount;The system is using a temporary table MOVEFIELDS to store the primary key and the field to copy for the records in the source table that satisfy the filter Source.S2 = ‘A’.
Last part of the SQL function is to copy the field S3 from MOVEFIELDS temporary table into the destination table. Also, system fields get updated, systemModifiedAt and systemModifiedBy.
Keep in mind, DataTransfer can only be used in upgrade codeunits. To detect the sql behind DataTransfer data type functionality we need to debug. Debugging upgrade codeunits is not difficult, but also not straightforward. If you need to review how to debug upgrade codeunits check Zhu’s step by step blog.
As we’ve seen, the process of identifying what sql statements get packaged and executed by the platform is relatively easy. Go ahead and see if you can identify what sql statements AL translates your favorite AL methods into.
Hope this helps!
One Response