15.3 Performance Profiler, SetLoadFields & Query Optimization

15.3 Performance Profiler, SetLoadFields & Query Optimization

Optimizing code performance and database access is a core competency evaluated on the MB-820 exam. In a cloud ERP environment, inefficient AL code and unoptimized SQL statements cause slow user response times, lock contention, and high compute consumption. Developers must master the built-in profiling tools, apply partial record optimizations, utilize efficient index cursors, and control transaction locking boundaries.

1. Business Central Performance Profiling Tools

Business Central provides two complementary performance profiling mechanisms: the In-Client Performance Profiler (Web Client) and the AL Performance Profiler in Visual Studio Code.

+-----------------------------------------------------------------------+
|                   IN-CLIENT PERFORMANCE PROFILER                      |
|                                                                       |
|   1. User opens 'Performance Profiler' page in Web Client.            |
|   2. Clicks 'Start' to begin recording the active session.            |
|   3. Executes slow business action (e.g., Post Sales Order).          |
|   4. Clicks 'Stop' -> Web Client renders Call Tree & Top Time by App. |
|   5. Clicks 'Download' -> Generates an '.alcpuprofile' capture file.  |
+-----------------------------------┬-----------------------------------+
                                    │ (Import .alcpuprofile)
                                    ▼
+-----------------------------------------------------------------------+
|                VISUAL STUDIO CODE AL PROFILER VIEWS                   |
|                                                                       |
|   - Top Down View (Call Tree hierarchy with Self Time vs Total Time)  |
|   - Bottom Up View (Hot Path list sorted by most expensive methods)   |
|   - Flame Graph Visualization (Call stack width = execution time)     |
|   - Direct Source Code Line Profiling (Time spent per AL code line)   |
+-----------------------------------------------------------------------+

1. The In-Client Web Profiler

• Accessibility: Available directly within the Business Central Web Client (Help & Support -> Analyze Performance / Performance Profiler).
• Non-Technical User Friendly: Functional consultants and end users can record slow business operations in production without installing developer tools.
• Call Tree Analysis: Displays the exact hierarchy of executed objects, total time spent per extension (attributing performance cost to specific AppSource or Per-Tenant extensions), and top time-consuming AL procedures.
• Profile Export: The recorded trace can be downloaded as an .alcpuprofile file and shared with developers.

2. VS Code AL Performance Profiler & Snapshot Profiling

• Snapshot Profiling: Developers initialize a snapshot debugging session in launch.json ("mode": "snapshot"), record the user session, and download the execution profile.
• Analysis Views: When an .alcpuprofile file is opened in VS Code, the AL Language extension renders:
  • Top Down View: Hierarchical call tree showing caller-to-callee relationships.
  • Bottom Up View: Inverted call stack grouping execution time by leaf procedures to highlight hot paths.
  • Flame Graph: Visual representation where horizontal width corresponds to execution duration.
  • Line-Level Attribution: Highlights individual lines of AL code with execution counts and self-time.

2. Partial Records Optimization: SetLoadFields

In standard AL table operations, invoking Rec.FindSet() or Rec.Get() instructs the Data Access Layer to load all fields in the table schema across the network, automatically executing SQL LEFT OUTER JOIN statements against every installed companion table (table extensions).

The Partial Records API

The Partial Records API allows developers to restrict database I/O to only the fields required for the specific business calculation:

local procedure CalculateTotalCustomerBalance(): Decimal
var
    Customer: Record Customer;
    TotalBalance: Decimal;
begin
    // 1. Specify only the fields needed for the calculation
    Customer.SetLoadFields("No.", "Balance (LCY)");
    
    if Customer.FindSet() then
        repeat
            TotalBalance += Customer."Balance (LCY)";
        until Customer.Next() = 0;
        
    exit(TotalBalance);
end;

Partial Records Methods

• Rec.SetLoadFields([Field1, Field2, ...]): Replaces the active load field list on the record variable. Primary key fields are always loaded automatically and do not need to be specified.
• Rec.AddLoadFields([Field3, ...]): Appends additional fields to an already configured partial record load list.
• Rec.AreFieldsLoaded([Field1, ...]): Returns a Boolean indicating whether all specified fields are currently present in the in-memory record buffer.
• Just-In-Time (JIT) Loading: If AL code attempts to read a field that was not included in SetLoadFields, the runtime transparently executes a background SQL query (a JIT fetch) to retrieve the remaining fields. While functionally safe, excessive JIT fetches cause "chatty" database round-trips and degrade performance.

3. Record Navigation & Iteration Optimization

Iterating over database records in AL requires selecting optimal cursors, buffering parameters, and key sorting.

1. FindSet Buffering Parameters

The FindSet() method accepts two optional boolean parameters:

Record.FindSet(ForUpdate: Boolean, UpdateKey: Boolean)

2. Method Selection Guide: FindFirst vs. FindSet vs. IsEmpty

3. Avoiding CalcFields Inside Large Loops

Invoking Rec.CalcFields(FlowField) inside a loop over 50,000 ledger lines executes an individual SQL aggregation subquery on every single iteration (creating an N+1 query problem). To optimize:

• Use Query objects (QueryType = Normal) to perform bulk SQL SUM and GROUP BY operations in a single database round-trip.
• Ensure appropriate SumIndexField Technology (SIFT) keys are defined on the underlying ledger tables so the database engine reads pre-aggregated index values instead of scanning physical rows.

4. SQL Concurrency, Locking & ReadIsolation

High-volume cloud ERP systems require strict isolation management to prevent deadlocks and lock timeouts (RT0012).

1. Explicit ReadIsolation Levels

AL allows developers to set the transaction isolation level on individual Record variables using the ReadIsolation property/method:

Customer.ReadIsolation := IsolationLevel::ReadUncommitted;
Customer.SetRange("Blocked", Customer.Blocked::" ");
TotalActiveCustomers := Customer.Count();

| Isolation Level | Dirty Reads Allowed? | Locking Behavior | Best Use Case | | :--- | :--- | :--- | | ReadUncommitted | Yes | Does not acquire shared read locks; never blocked by concurrent write transactions. | Tile Cues, Role Center counters, analytical reports, background telemetry queries. | | ReadCommitted | No | Default read level. Reads only committed data. | Standard transactional lookups and validation checks. | | RepeatableRead | No | Holds shared read locks until transaction completes. | Strict ledger consistency where concurrent updates must be blocked. | | UpdLock | No | Places update locks on rows immediately upon reading. | Pre-locking ledger entries prior to posting to completely prevent deadlocks. |

2. Minimizing Transaction Duration

• Postpone Writes: Perform all external REST API calls, user confirmation dialogs (Confirm), and in-memory calculations before issuing the first Rec.Insert(), Rec.Modify(), or Rec.Delete().
• Eliminate UI in Transactions: Never invoke modal pages or Confirm() dialogs while a database write transaction is open; if the user steps away from their computer, all locked database tables remain frozen across the entire company.