AL Performance Standards
Performance is reviewed as part of every code acceptance process. This page defines the specific patterns reviewers evaluate and the standards that all AL code in this repository must meet.
For each finding during a performance review, the reviewer should describe the problem, the proposed fix, and weigh the pros and cons — not every optimization is worth the added complexity. Findings are classified as Fix (must correct), Fix if (conditional on context), or Accept (acknowledged, no action needed).
Database Access Patterns
SetLoadFields Correctness
SetLoadFields is "sticky" on a record variable — Init() and Reset() do not clear it. Verify that SetLoadFields is only applied to record variables used for read-only queries. If the same variable is later used for Insert, Modify, Get, or passed to functions that read from the database (e.g., ApplyCustomerTemplate), the partial field loading can silently corrupt data.
Safe pattern: Use separate variables for lookup and write operations.
SetLoadFields Completeness
All fields used in SetRange/SetFilter should be included in the SetLoadFields list. While BC runtime 12.0+ auto-includes filter fields in SQL, omitting them is misleading to developers reading the code and fragile across platform versions.
Redundant Database Calls
Look for duplicate FindFirst(), Get(), or FindSet() calls on the same record variable with unchanged filters. Each call is a database round-trip. Also check for Find('-') patterns that should be FindFirst() or FindSet().
FindFirst vs. FindSet
FindFirst— use when only one record is needed (existence checks, single-record lookups)FindSet— use when iterating withrepeat...until Next() = 0
Using FindSet for a single record or FindFirst inside a loop are both anti-patterns.
Unnecessary Reset() Calls
A freshly declared Record variable has no state. Calling Reset() on a new variable is dead code. Only use Reset() when reusing a variable that previously had filters applied.
Locking and Concurrency
LockTable() Scope
LockTable() acquires an exclusive lock on the entire table for the duration of the current transaction. Evaluate:
- Is the lock necessary? Could record-level locking via
Get+Modifysuffice? - Is the lock duration bounded? A table lock inside a loop with HTTP calls holds the lock for the entire iteration set.
- What tables are affected? Table-level locks on high-traffic tables (Customer, Item, Sales Header) are critical risks and must be justified explicitly.
Transaction Duration
Long-running transactions that include HTTP calls, blob uploads, or other I/O hold database locks for their entire duration. Look for opportunities to break work into micro-transactions with Commit() per iteration.
Tradeoff: Commit() inside a loop means partial batches persist if interrupted, but releasing locks prevents blocking other users. When using Commit() to break transactions, document the reason and the partial-completion behavior in a code comment so reviewers can evaluate the tradeoff.
Lock Contention Between API Pages and Background Jobs
If an API page and a Job Queue codeunit both operate on the same tables, verify that neither holds locks that would block the other during normal operation. This is a common source of lock timeout errors in production.
Index and Key Usage
Secondary Keys Must Match Filter Patterns
When SetRange/SetFilter is used on non-primary-key fields, verify that a secondary key exists covering those fields. For tables with growing record counts, missing indexes cause full table scans that degrade over time.
SetCurrentKey Alignment
SetCurrentKey overrides BC's automatic index selection. Verify that the specified key matches the filters being applied:
- A
SetCurrentKeyon the primary key when filtering on status fields forces a table scan even if a matching secondary key exists. - Conversely, adding a secondary key without updating
SetCurrentKeyproduces dead weight (write overhead with no read benefit).
Index Write Overhead
Every secondary key adds overhead to Insert, Modify, and Delete operations. For high-write, low-read tables, the cost may outweigh the benefit. Evaluate whether the table's access pattern justifies the index.
Record Operations
Insert() vs. Insert(true)
Insert() skips the OnInsert trigger, bypassing standard BC setup logic (No. Series, default fields, event subscribers). Use Insert(true) unless there is a specific, documented reason to skip triggers.
Tradeoff: Insert(true) is correct but slower; Insert() is faster but may produce incomplete records.
Modify() vs. Modify(true)
Same principle as Insert. Modify(true) fires OnModify and event subscribers. Skipping triggers can leave related data inconsistent.
Unnecessary Modify Calls
Look for patterns where a record is modified multiple times in sequence — e.g., insert, then immediately modify to apply a template, then modify again to re-apply a field. Each Modify is a database write. Consider whether field values can be set before a single Insert or Modify call.
Query Optimization Opportunities
Full-Table Loads for Single-Field Access
If code reads an entire Customer/Item/Sales record but only uses "No." or one other field, SetLoadFields should be applied. This is especially impactful on tables with many fields — the Customer table has 200+ fields in standard BC.
Repeated Lookups in Loops
If the same record is looked up inside a repeat...until loop (e.g., finding a Customer by phone number for each call log record), consider caching the result or restructuring the query.
CalcFields on FlowFields
CalcFields triggers a SQL aggregate query. Calling it inside a loop or on fields that aren't used is wasteful. Verify that CalcFields is only called when the FlowField value is actually needed.
Review Approach
When findings have tradeoffs, present them as a table:
| Finding | Proposed Fix | Pro | Con | Recommendation |
|---|---|---|---|---|
SetLoadFields bleeds into Insert path | Use separate record variables | Eliminates risk of partial field loading | Adds a second variable declaration | Fix — data correctness outweighs verbosity |
| Missing index on status fields | Add secondary key | Faster Job Queue queries as table grows | Write overhead on every Insert/Modify | Fix if table has > 1,000 records; skip for low-volume tables |
CalcFields called but value unused | Remove the call | One fewer SQL aggregate per record | None | Fix — no downside |
Not every optimization should be applied. The review should explicitly recommend Fix, Fix if [condition], or Accept for each finding, with justification.