ExamFinch.com My Study

Study guide

15.3 Performance Profiler, SetLoadFields & Query Optimization

Practice this exam

Microsoft Dynamics 365 Business Central Developer MB-820

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Business Central Architecture & Platform Foundation

1.1 Business Central Architecture & Extensions Model 1.2 Customization Lifecycle: Cloud (SaaS) vs. On-Premises 1.3 Base Application, System Application & AppSource

2 Chapter 2: Development Environment & Configuration

2.1: Development Environment Setup: VS Code, AL: Go! Projects & Sandboxes 2.2: AL Configuration Files: app.json & launch.json Parameters 2.3: Multi-Root Workspaces & Project Dependencies

3 Chapter 3: Extension Lifecycle: Debugging, Deployment, Multilanguage & Maintenance

3.1 Debugging & Snapshot Debugging in Visual Studio Code 3.2 Extension Deployment & Schema Synchronization Modes 3.3 Multilanguage Development with XLIFF & Translation Files 3.4 Extension Maintenance, Deprecation & Breaking Changes

4 Chapter 4: Tables, Table Extensions & Data Modeling

4.1: Building Tables, Fields, Keys & Data Types 4.2: Table Extensions & Table Modifications 4.3: FlowFields, FlowFilters & Table Relations 4.4: Enums, Enum Extensions & Extensible Types

5 Chapter 5: Pages, Page Extensions & Role Centers

5.1: Page Types, Anatomy & Control Structure 5.2: Building Card, List & Document Pages 5.3: Page Extensions & Page Customizations 5.4: Role Centers, Headlines & Cue Data Modeling

6 Chapter 6: Reports & Document Generation

6.1 Report Architecture, DataItems & Dataset Design 6.2 Report Layouts: RDLC, Word, Excel & Multilanguage 6.3 Document Reports & Request Page Programming 6.4 Processing-Only Reports & Report Substitutions

7 Chapter 7: XMLports & Data Exchange

7.1 XMLport Architecture, Formats & Properties 7.2 XMLport Node Hierarchy, Triggers & Stream Manipulation

8 Chapter 8: Codeunits, Events, Triggers & Interfaces

8.1 Codeunit Types, Execution & SingleInstance Codeunits 8.2 Events, Publishers, Subscribers & Event Isolation 8.3 Interfaces, Polymorphism & Dynamic Codeunit Invocation 8.4 Installation & Upgrade Codeunits, Data Transfer & Archive

9 Chapter 9: Entitlements, Permissions & Queries

9.1 Permission Sets & Permission Set Extensions 9.2 Inherent Permissions, Inherent Entitlements & Security Scoping 9.3 Query Objects: Joins, Aggregation & Query as Report Data Source

10 Chapter 10: UI Customization, User Assistance & Onboarding

10.1 Profile Objects & Custom Page Views in AL 10.2 User Assistance: ToolTips, Teaching Tips & In-App Tours 10.3 Assisted Setup, Guided Experience & Onboarding Checklists

11 Chapter 11: Business Central Design Patterns & Data Process Model

11.1 Functional Table Types: Master Data, Supplemental & Setup Patterns 11.2 Document Standards: Header, Line, History & Journal Architectures 11.3 Data Process Model & Posting Routine Architecture

12 Chapter 12: AL Language Fundamentals, Data Types & Variables

12.1 AL Syntax, Variable Scope, Primitive & Complex Data Types 12.2 Expressions, Control Flow Statements & Built-in Functions 12.3 Access Modifiers (Public, Internal, Local) & Code Scoping

13 Chapter 13: Data Manipulation, Streams & Error Handling

13.1 Record Operations: Get, FindSet, ModifyAll & Filter Functions 13.2 Streams, File Management & TempBlob 13.3 Error Handling: ErrorInfo, TryFunctions & Collectible Errors

14 Chapter 14: Testing, Test Toolkit & Automation

14.1 Testing Architecture & the Test Toolkit 14.2 Authoring Test Codeunits & UI Handler Functions

15 Chapter 15: Telemetry, Performance Optimization & Analyzers

15.1 Telemetry Configuration & Azure Application Insights 15.2 Custom Telemetry Signals with Session.LogMessage & KQL 15.3 Performance Profiler, SetLoadFields & Query Optimization 15.4 AL Analyzers: CodeCop, AppSourceCop, UICop & PTECop

16 Chapter 16: REST Web Services & JSON in AL

16.1 HttpClient, HttpRequestMessage & REST API Consumption 16.2 JSON Processing: JsonObject, JsonArray, JsonValue & JsonToken

17 Chapter 17: API Pages, OData, Read Scale-Out & OAuth

17.1 API Pages (PageType=API), OData Web Services & REST Endpoints 17.2 Bound and Unbound OData Actions with [ServiceEnabled] 17.3 Read Scale-Out Architecture & DataAccessIntent Configuration 17.4 Microsoft Entra ID App Registrations & OAuth 2.0 Integration

15.3 Performance Profiler, SetLoadFields & Query Optimization

Key Takeaways Explain This Topic AI

The in-client Performance Profiler in the Web Client enables users and developers to record interactive browser sessions, identify hot paths, and export .alcpuprofile files for deep analysis.

VS Code AL Performance Profiling supports snapshot profiling and flame graph visualization to pinpoint slow methods and costly database operations.

Partial records (SetLoadFields, AddLoadFields, AreFieldsLoaded) optimize data access by loading only specified fields and skipping unnecessary SQL joins with companion tables.

When looping over records with repeat...until, developers must use FindSet() with appropriate buffering parameters (ForUpdate, UpdateKey) and avoid executing CalcFields inside the loop body.

SQL concurrency is optimized by selecting appropriate ReadIsolation levels (such as ReadUncommitted for reporting) and keeping transactional write locks as short as possible.

Last updated: August 2026

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.

Loading diagram... Data Access Layer: Full Table Join vs Partial Records Optimization

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)

Parameter Default Purpose & Recommended Usage

ForUpdate false When true , signals to the NST that the transaction intends to modify the retrieved records ( Rec.Modify() ), applying appropriate SQL row-locking hints and avoiding lock escalations.

UpdateKey false When true , informs the database engine that fields belonging to the current sorting key or primary key will be modified during the loop, ensuring the cursor remains stable.

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

  • Scenario / Requirement Recommended Method Prohibited / Unoptimized Pattern
  • Iterating over multiple records Rec.FindSet() Rec.Find('-') (Legacy C/SIDE pattern; unoptimized cursor)

Reading exactly one record Rec.FindFirst() or Rec.FindLast() Rec.FindSet() (Allocates unnecessary multi-row buffer)

Checking if matching records exist if not Rec.IsEmpty() then if Rec.FindFirst() then or if Rec.Count() > 0 then

  • Counting record occurrences Rec.Count() Reading all records with repeat...until counter
  • 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.