Can I query half a record in AL?

For decades, we have always retrieved full records from the database. Now we got an option to only query the fields we need. Check out this video:

https://youtu.be/iFGmTEGLaf0

In this video, Erik explores the SetLoadFields method in AL for Business Central — a powerful performance optimization technique that allows you to load only the specific fields you need from a record, rather than retrieving every single column from the database. If you come from the SQL world, this concept will feel familiar: it’s essentially the AL equivalent of selecting specific columns instead of using SELECT *.

The Problem: Loading Entire Records Every Time

Historically, in Business Central (and its predecessors NAV and Navision), whenever you retrieve a record from the database, you get every single field. For a long time, this wasn’t a significant performance concern. But the introduction of table extensions changed the equation dramatically.

When you have table extensions, data is stored across multiple tables. Consider the Sales Header — if you have four or five different extensions all adding fields to it, then behind the scenes you have the original Sales Header table from the base application plus four or five additional tables, all joined together on every query. In most business logic scenarios, you probably don’t need the fields from those extension tables at all.

Introducing SetLoadFields

The SetLoadFields method allows you to tell the platform exactly which fields you need. Let’s walk through Erik’s example using the G/L Entry table:

procedure Demo()
var
    GLEntry: Record "G/L Entry";
    Counter: Integer;
begin
    GLEntry.SetLoadFields("Credit Amount");
    if GLEntry.FindSet() then
        repeat
            if GLEntry."Credit Amount" = 35 then
                Counter += 1;
        until GLEntry.Next() = 0;
end;

In this loop, the only field we actually need (beyond the primary key, which is always included) is Credit Amount. By calling SetLoadFields("Credit Amount") before the FindSet(), we tell the platform to only retrieve that field. All other fields — including any fields added by extensions — will be blank. For tables with many extensions or many fields, this can deliver a significant performance improvement.

Just-In-Time (JIT) Loading

What happens if your code accesses a field that wasn’t included in SetLoadFields? Business Central uses a mechanism Microsoft calls Just-In-Time (JIT) loading. The platform detects that you’re requesting a field that wasn’t part of the original load set, goes back to the database, and retrieves the entire rest of the record.

// This triggers a JIT load because "Debit Amount" was not in SetLoadFields
if GLEntry."Credit Amount" = 35 then
    Message('%1', GLEntry."Debit Amount");

It’s important to understand that JIT loading doesn’t just fetch the one missing field — it fetches all remaining fields. If this happens frequently inside a tight loop, you’re actually making performance worse than if you hadn’t used SetLoadFields at all, because now you’re making two database trips per record instead of one.

Operations That Trigger JIT Loads

Certain record operations will always trigger a JIT load, regardless of your field set. For example, calling Delete() on a partially loaded record will force a full reload. This makes sense — the platform needs to ensure that extension table records are also properly cleaned up, and it can’t do that without the complete record.

// This will trigger a JIT load, even if you only loaded "Credit Amount"
GLEntry.Delete();

So if your loop is deleting most of the records it iterates through, SetLoadFields may not provide the benefit you’re looking for.

Important Rules and Best Practices

  • Call SetLoadFields before Find: You must set the load fields before calling FindSet(), Find(), or any other retrieval method. It should be called outside the loop.
  • Only one SetLoadFields per record variable: Calling SetLoadFields a second time replaces the first — it doesn’t add to it. You cannot accumulate field sets.
  • Include all fields you’ll access: If you know you’ll need a field inside the loop, add it to the SetLoadFields call. Never design your code to rely on JIT loading for fields you know you’ll need.
  • Be cautious with large code blocks: If your repeat loop spans hundreds of lines, there’s a real danger that someone else (or future you) will add code that accesses an unlisted field without realizing the loop is under SetLoadFields control. This silently degrades performance.

When to Use SetLoadFields

Erik shares his practical approach to deciding when this optimization is worthwhile:

  • High-volume tables with extensions: This is the sweet spot. Erik mentions a client with 400,000 open sales orders, running e-commerce with three or four extensions all extending the Sales Header. Looping through those records with SetLoadFields avoids unnecessary joins to extension tables.
  • Standard BC tables likely to be extended: Tables like Sales Header, Sales Line, Customer, Item, and G/L Entry are popular extension targets. Using SetLoadFields when looping through these is a smart defensive practice.
  • Less critical for your own custom tables: If you control the table, know it has few fields, and know it’s unlikely to have extensions, the benefit is minimal.
  • Less critical for obscure standard tables: Tables like VAT Entries that are rarely extended by other apps probably don’t need this optimization.

Using Queries as an Alternative Approach

Another way to control exactly which fields are retrieved from the database is to use Query objects. The source code provided with this video shows an example of using a Query inside a Report to pull specific fields from Customer Ledger Entries:

report 50100 "Query Report"
{
    Caption = 'Query Report';
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = all;
    DefaultLayout = Excel;
    ExcelLayout = 'queryreport.xlsx';
    dataset
    {
        dataitem(CLE; Integer)
        {
            column(A1; Q.Amount)
            { }
            column(A2; Q.Customer_Name)
            { }
            column(A3; Q.Document_Date)
            { }
            trigger OnPreDataItem()
            begin
                Q.Open();
            end;

            trigger OnAfterGetRecord()
            begin
                if not Q.Read() then
                    CurrReport.Break();
            end;
        }
    }
    var
        Q: Query "Cust. Ledger Entries";
}

This pattern uses an Integer dataitem as a driver and manually reads from a Query object. The Query inherently only retrieves the columns defined within it — Amount, Customer_Name, and Document_Date — making it another effective way to avoid loading unnecessary data.

Conclusion

SetLoadFields is a valuable tool for optimizing performance in AL, especially when working with high-volume tables that are likely extended by multiple apps. The key is to use it deliberately: always call it before your find operation, include every field your loop will access, and be aware that certain operations like Delete() will trigger a full JIT reload. For tables you control with few fields and no extensions, the overhead of managing partial loads may not be worth it — but for large, heavily-extended standard tables in production environments, it can make a meaningful difference.

Picture of hougaard

hougaard