skill-performance
AL performance optimization patterns for Business Central. Use when optimizing queries with SetLoadFields, working with FlowFields and CalcFields, profiling codeunits, or resolving performance issues.
javiarmesto · 已有静态检查记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add javiarmesto/ALDC-AL-Development-Collection --skill skill-performance技能说明
name: skill-performance description: AL performance optimization patterns for Business Central. Use when optimizing queries with SetLoadFields, working with FlowFields and CalcFields, profiling codeunits, or resolving performance issues.
Skill: AL Performance Optimization
Purpose
Identify, analyze, and fix performance bottlenecks in AL code: inefficient queries, FlowField issues, loop anti-patterns, and data-volume problems in Business Central.
When to Load
This skill should be loaded when:
- A page, report, or batch process is slow or timing out
- CPU profiling is needed to identify hotspots
- A static triage of the codebase is requested for performance issues
- A feature involves large-dataset processing or high-frequency code paths
- AL0896 (circular FlowField) errors appear
- Architecture review requires performance analysis of a new design
Core Patterns
For BC29 query or isolated-storage concurrency work, read the query/storage
sections of AL18 capability checks.
Confirm supported declarations and test transaction behavior before changing
isolation; DataScope and transaction isolation are different concepts.
Pattern 1: SetLoadFields + Early Filtering
Always filter before finding, and load only needed fields. Order matters.
// ✅ Correct: SetRange first, SetLoadFields before Find
Item.SetRange("Third Party Item Exists", false);
Item.SetLoadFields("Item Category Code", Description);
if Item.FindSet() then
repeat
// Only "Item Category Code" and Description loaded from DB
until Item.Next() = 0;
// ❌ Wrong: SetLoadFields after SetRange (ignored), loads all fields
Item.SetLoadFields("Item Category Code");
Item.SetRange("Third Party Item Exists", false);
Item.FindFirst();
// ❌ Wrong: No filter — full table scan
procedure GetCustomersByCity(CityFilter: Text): Integer
var
Customer: Record Customer;
Count: Integer;
begin
if Customer.FindSet() then // loads entire Customer table
repeat
if Customer.City = CityFilter then
Count += 1;
until Customer.Next() = 0;
end;
// ✅ Correct: Filter pushed to DB
procedure GetCustomersByCity(CityFilter: Text): Integer
var
Customer: Record Customer;
begin
Customer.SetRange(City, CityFilter);
Customer.SetRange(Blocked, Customer.Blocked::" ");
exit(Customer.Count());
end;
Pattern 2: Set-Based Aggregation (CalcSums / CalcFields)
Avoid manual loops for aggregation — push the sum to the database.
// ❌ Loop accumulation — N rows fetched and processed in AL
procedure GetTotalSales(CustomerNo: Code[20]): Decimal
var
Entry: Record "Cust. Ledger Entry";
Total: Decimal;
begin
Entry.SetRange("Customer No.", CustomerNo);
if Entry.FindSet() then
repeat
Total += Entry.Amount;
until Entry.Next() = 0;
exit(Total);
end;
// ✅ CalcSums — single aggregation query at DB level
procedure GetTotalSales(CustomerNo: Code[20]): Decimal
var
Entry: Record "Cust. Ledger Entry";
begin
Entry.SetRange("Customer No.", CustomerNo);
Entry.CalcSums(Amount);
exit(Entry.Amount);
end;
For FlowFields accessed outside a page context, always call CalcFields before reading:
Customer.SetLoadFields("Balance (LCY)");
Customer.Get(CustomerNo);
Customer.CalcFields("Balance (LCY)"); // required — not auto-calculated in code
Pattern 3: Temporary Tables / Dictionary / List
Pre-load data once, then process in-memory multiple times.
// ✅ Temporary table — structured record data, multi-pass processing
procedure ProcessSalesData(var TempSalesLine: Record "Sales Line" temporary)
var
SalesLine: Record "Sales Line";
begin
SalesLine.SetLoadFields("No.", Quantity, "Unit Price", Amount);
if SalesLine.FindSet() then
repeat
TempSalesLine := SalesLine;
TempSalesLine.Insert();
until SalesLine.Next() = 0;
// Process in-memory — zero additional DB hits
ApplyDiscounts(TempSalesLine);
CalculateTotals(TempSalesLine);
end;
// ✅ Dictionary — key-value lookup cache
procedure CacheCustomerNames(): Dictionary of [Code[20], Text]
var
Customer: Record Customer;
Cache: Dictionary of [Code[20], Text];
begin
Customer.SetLoadFields("No.", Name);
if Customer.FindSet() then
repeat
Cache.Add(Customer."No.", Customer.Name);
until Customer.Next() = 0;
exit(Cache);
end;
// ✅ List — simple value collection
procedure GetBlockedCustomerNos(): List of [Code[20]]
var
Customer: Record Customer;
Result: List of [Code[20]];
begin
Customer.SetRange(Blocked, Customer.Blocked::All);
Customer.SetLoadFields("No.");
if Customer.FindSet() then
repeat
Result.Add(Customer."No.");
until Customer.Next() = 0;
exit(Result);
end;
Pattern 4: Batch Processing and Avoiding Nested DB Calls
Move database operations outside loops; batch writes.
// ❌ Nested DB call inside loop — O(n²) database hits
if MainTable.FindSet() then
repeat
OtherTable.SetRange(Field, MainTable.Field);
if OtherTable.FindSet() then // DB call per iteration
repeat
// process
until OtherTable.Next() = 0;
until MainTable.Next() = 0;
// ✅ Pre-load into temp table, then join in memory
if OtherTable.FindSet() then
repeat
TempOther := OtherTable;
TempOther.Insert();
until OtherTable.Next() = 0;
if MainTable.FindSet() then
repeat
if TempOther.Get(MainTable.Field) then
// process — no DB hit
until MainTable.Next() = 0;
Batch writes — collect changes, write once:
// ✅ Calculate all values first, then single Modify
procedure UpdateCustomerStats(CustomerNo: Code[20])
var
Customer: Record Customer;
TotalBalance: Decimal;
LastPaymentDate: Date;
begin
CalculateCustomerTotals(CustomerNo, T
阅读完整来源 ↗