3
R

SQL Command issued by the system needs to be refined not to refer to the extension table for better performance

Rama Mathanmohan on 1/13/2020 5:22:22 PM
SQL Command issued by BC needs to be refined so that when the AL code does not access fields from the extended table, it should only refer to base table. For example the following code should not refer to any sales line extensions as all the fields in the following codes are from the base application. However the current behaviour of the system is to refer to the extension table a well. Out test is showing this behaviour is 1.5 times slower than simply refereeing to the base table only. The observed SQL code is attached to the comments.

For better performance, please remove referring to the extension table in SQL.

codeunit 77005 "Speed Test"
{
trigger OnRun()
begin
FilterOnBaseField;
end;

local procedure "FilterOnBaseField"()
var
SalesLine: Record "Sales Line";
begin
SalesLine.SetCurrentKey("Document Type", "Type", "No.");
SalesLine.setrange("Document Type", SalesLine."Document Type"::Order);
salesLine.SetRange("Type", SalesLine."Type"::Item);
SalesLine.SetRange("No.", '10000');
If SalesLine.FindSet() then
repeat
Message(SalesLine."Document No.");
until SalesLine.Next() = 0;
end;
}
Category: Development
STATUS DETAILS
Completed
Ideas Administrator

ContactFirstName7 ContactLastName7 (administrator)

Thank you for your feedback. We released this feature as part of the functionality in Partial Records.

Please refer to the following link for more information: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-partial-records

 

Sincerely,

Kennie Nybo Pontoppidan
PM, Microsoft

Comments

R

R

RE: SQL Command issued by the system needs to be refined not to refer to the extension table for better performance

ContactFirstName7 ContactLastName7 on 1/13/2020 5:24:34 PM

SELECT "37"."timestamp",
"37"."Document Type",
"37"."Document No_",
"37"."Line No_",
"37"."Sell-to Customer No_",
"37"."Type",
"37"."No_",
"37"."Location Code",
/*Rest of the fields .... */
"37_e1"."K3Document Type",
"37_e1"."K3Outstanding Quantity",
"37_e1"."Status",
"37_e1"."K3 Type",
"37_e1"."K3 No_",
"37"."$systemId"
FROM "Cronusgb".dbo."Cronus UK$Sales Line$437dbf0e-84ff-417a-965d-ed2bb9650972" "37" WITH(READUNCOMMITTED)

JOIN "Cronusgb".dbo."Cronus UK$Sales Line$ef933c61-34d2-4d4e-ada4-6fdf714541ab" "37_e1" WITH(READUNCOMMITTED) ON("37"."Document Type" = "37_e1"."Document Type")
AND ("37"."Document No_" = "37_e1"."Document No_")
AND ("37"."Line No_" = "37_e1"."Line No_")

WHERE("37"."Document Type" = @0
AND "37"."Type" = @1
AND "37"."No_" = @2)
ORDER BY "Document Type" ASC,
"Type" ASC,
"No_" ASC,
"Variant Code" ASC,
"Drop Shipment" ASC,
"Location Code" ASC,
"Shipment Date" ASC,
"Document No_" ASC,
"Line No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50);

Category: Development