Tag: Performance

Optimizing Business Central Reports with the DataAccessIntent Property

When developing reports in Microsoft Dynamics 365 Business Central, performance and scalability are crucial—especially in cloud environments where system efficiency impacts everything from responsiveness to cost. One often-overlooked feature that can significantly enhance report performance is the DataAccessIntent property.

What Is DataAccessIntent?

The DataAccessIntent property is available on report objects in AL. It specifies how a report should access the database—whether it’s read-only or read-write.

The property supports two values:

  • DataAccessIntent = ReadOnly
  • DataAccessIntent = ReadWrite

ReadOnly

When you set DataAccessIntent = ReadOnly, you are explicitly telling the platform that your report only needs to read data from the database and will not perform any write operations.

Why is this important?

  • Better Performance: In Business Central SaaS, reports marked as ReadOnly can run on read-only replicas of the database. This reduces the load on the primary (read-write) database and enhances scalability.
  • Improved Report Execution Time: Since queries are routed to optimized replicas, report rendering can be faster and more efficient.

ReadWrite

If your report needs to modify data during execution—a rare scenario—you should use DataAccessIntent = ReadWrite. This forces the report to run on the primary database.

However, you should avoid using ReadWrite unless absolutely necessary because:

  • It eliminates the benefit of using read replicas.
  • It may degrade performance, especially under high concurrency.

When to Use Each

ScenarioUse ReadOnly?Use ReadWrite?
Standard data listing/reporting✅❌
Reports that update records❌✅
Diagnostic or audit reports✅❌

How to Set DataAccessIntent in AL

report 50100 "Customer Balance Report"
{
    DataAccessIntent = ReadOnly;

    dataset
    {
        dataitem(Customer; Customer)
        {
            column(Name; Name) { }
            column(Balance; "Balance (LCY)") { }
        }
    }

    layout
    {
        // Define RDLC or Word layout
    }
}

By default, if you don’t specify the property, it behaves as ReadWrite. So it’s a good practice to explicitly set it to ReadOnly when applicable.

Important Considerations

  • The DataAccessIntent property is only a hint to the Business Central server. The server may not always be able to use a read-only replica, even if the property is set to ReadOnly.
  • If a report with DataAccessIntent set to ReadOnly attempts to modify data, a runtime error will occur.
  • The DataAccessIntent property can be overridden by the user through the “Database Access Intent List” page in Business Central.

In short: if your report doesn’t write data, use DataAccessIntent = ReadOnly. It’s an easy win for performance and best practice compliance.

Stay tuned for more….

Boosting Performance in Business Central with SETLOADFIELDS

When building customizations or integrations in Microsoft Dynamics 365 Business Central, performance is often a key concern—especially when working with large datasets. One powerful tool for improving performance is the SETLOADFIELDS method on record variables.

🚀 What is SETLOADFIELDS?

In Business Central, when you retrieve a record (using FIND, GET, NEXT, etc.), all fields of the record are loaded from the database by default. This includes fields you might never use, such as FlowFields (which are calculated on the fly). This can lead to unnecessary memory usage and slower data retrieval.

SETLOADFIELDS tells the system which fields to load, so it skips the rest. This can dramatically improve performance, particularly when:

  • You’re looping through large datasets
  • You’re only using a few fields per record
  • Your table includes FlowFields or BLOBs

📘 Syntax

Rec.SETLOADFIELDS(Field1, Field2, ...);

You place this line before reading the records. This tells the AL runtime engine to load only the specified fields when the records are retrieved.

Let’s say you need to loop through all items and check their No. and Inventory:

Item.SETLOADFIELDS("No.", Inventory);
if Item.FINDFIRST then
  repeat
    if Item.Inventory > 0 then
      // do something
  until Item.NEXT = 0;

  1. Use SETLOADFIELDS only when needed – Overusing it without understanding the data flow may result in missing fields or unexpected behavior.
  2. Calling CALCFIELDS still works – You can still explicitly calculate FlowFields after using SETLOADFIELDS.
  3. Resets on modification – If you call MODIFY, INSERT, or VALIDATE, the load fields context is reset. Use SETLOADFIELDS again if necessary.
  4. BLOB and FlowFields – Avoid loading BLOBs and FlowFields unless absolutely necessary. SETLOADFIELDS helps you skip them efficiently.

👨‍💻 Summary

FeatureWithout SETLOADFIELDSWith SETLOADFIELDS
Data LoadedAll fieldsOnly specified fields
Memory UsageHighLower
SpeedSlowerFaster

By leveraging SETLOADFIELDS, developers can significantly optimize the performance of their AL code in Business Central. It’s a small addition that can make a big difference in speed and scalability.

Avoid it when doing full record operations like MODIFY, unless you’re confident about which fields are required.

Stay Tuned for more…

Boosting Performance in Business Central with SetAutoCalcFields

In today’s fast-paced business environments, performance optimization is not a luxury — it’s a necessity. Whether you’re building custom reports, extending pages, or writing integration APIs in Microsoft Dynamics 365 Business Central, slow operations can frustrate users and strain resources.

One simple but powerful technique to improve performance is using the SetAutoCalcFields method.

Why FlowFields Matter for Performance

In Business Central, FlowFields are virtual fields calculated on the fly, not stored physically in the database. Examples include:

  • Inventory on Item
  • Balance on Customer

When you retrieve a record, FlowFields are not calculated automatically unless you explicitly call CalcFields().

Manually calculating FlowFields for every record during loops, leading to multiple SQL queries — one for each record — causing heavy load and slower performance.

SetAutoCalcFields solves this by batch-calculating FlowFields along with the record fetch.
Instead of running one query per record, it combines the calculation in a single optimized query.

Imagine fetching 1,000 customers and displaying their balances without SetAutoCalcfields:

CustomerRec.FindSet();
repeat
    CustomerRec.CalcFields(CustomerRec.Balance); // Triggers a DB call every time!
    DisplayBalance(CustomerRec."No.", CustomerRec.Balance);
until CustomerRec.Next() = 0;

Result:
  • 1 SQL query to get Customers
  • + 1,000 SQL queries for Balances

With SetAutoCalcFields:

CustomerRec.SetAutoCalcFields(CustomerRec.Balance);
CustomerRec.FindSet();
repeat
    DisplayBalance(CustomerRec."No.", CustomerRec.Balance);
until CustomerRec.Next() = 0;

Result:
  • 1 SQL query to get Customers and Balances together

Benefits of Using SetAutoCalcFields

  • Improved Page Load Times: By deferring calculations, pages with numerous records and calculated fields will load significantly faster.
  • Faster Report Generation: Reports that rely on calculated fields will be generated more quickly as the calculations are performed only when the field’s value is actually needed for display or processing.
  • Reduced Database Load: Fewer automatic calculations translate to fewer database queries, reducing the overall load on your Business Central database.
  • Enhanced User Experience: Snappier performance leads to a more responsive and enjoyable user experience.

Best Practices for Performance Gains

To maximize the benefits of SetAutoCalcFields:

  • Only specify necessary FlowFields:
    Don’t auto-calculate every FlowField — focus on what your process needs.
  • Use it before data retrieval:
    Call SetAutoCalcFields before FindSet(), FindFirst(), or FindLast()
  • Avoid unnecessary recalculations:
    Once FlowFields are set to auto-calculate, do not manually call CalcFields() again for the same fields.
  • Monitor heavy FlowFields:
    Some FlowFields (e.g., Inventory) involve complex sums across tables — only auto-calculate when really needed.
  • Profile your code:
    Use Performance Profiler to measure improvements.

Using SetAutoCalcFields properly can lead to dramatic performance improvements in Business Central.
By reducing SQL traffic, simplifying code, and batch-fetching FlowFields intelligently, you can create faster, cleaner, and more scalable applications.

A small change in your coding habits can create a big impact for your users.

Hope this will help..

FieldRef.IsOptimizedForTextSearch() Method in Business Central 2025 Wave 1

With the release of Business Central 2025 Wave 1, Microsoft continues to empower developers with more control and insights into the performance and behavior of their extensions. Among the new additions is the method FieldRef.IsOptimizedForTextSearch(), designed to help developers make more performance-conscious decisions when implementing search functionalities.

💡 What is FieldRef.IsOptimizedForTextSearch()?

FieldRef.IsOptimizedForTextSearch() is a method that returns a Boolean value indicating whether a particular field in a table is optimized for text search.

It is a method on the FieldRef data type, which is used in AL code to dynamically refer to fields of records, especially in scenarios involving field iteration, metadata handling, or dynamic filters.

✅ Syntax:

Boolean := FieldRef.IsOptimizedForTextSearch();

⚙️ How to Optimize a Field for Text Search

While IsOptimizedForTextSearch() only checks if a field is optimized, setting it up is done via the table metadata or through the table schema in AL.

To mark a field for text search:

field(10; Description; Text[100])
{
    Caption = 'Description';
    DataClassification = ToBeClassified;
    OptimizeForTextSearch = true;
}

Setting OptimizeForTextSearch = true; enables text search optimization (depending on SQL backend settings as well for on-premise).

Lets see how we can utlize above method to check optimize search

var
    MyRecordRef: RecordRef;
    MyFieldRef: FieldRef;
    IsOptimized: Boolean;
begin
    MyRecordRef.Open(Database::Customer);
    if MyRecordRef.FindSet() then begin
        MyFieldRef := MyRecordRef.Field(Name); // Let's check the "Name" field
        IsOptimized := MyFieldRef.IsOptimizedForTextSearch();

        if IsOptimized then
            Message('The "%1" field in the Customer table is optimized for text search.', MyFieldRef.Name())
        else
            Message('The "%1" field in the Customer table is NOT optimized for text search.', MyFieldRef.Name());
    end;
    MyRecordRef.Close();
end;

To be optimized for full-text search, a field typically needs:

  • A Text or Code data type.
  • An active index that supports full-text search (defined in the table metadata or via table extensions).
  • Proper settings in SQL Server or Azure SQL (if full-text search is enabled).

The FieldRef.IsOptimizedForTextSearch() method is a small but powerful tool in the AL developer’s toolkit. Whether you’re designing smarter search UIs or optimizing performance in large datasets, this method gives you the metadata visibility to make informed choices.

By leveraging this feature, you can:

  • Improve app performance
  • Avoid slow queries
  • Create better user experiences

Stay tuned for more..

Business Central 2025 Wave 1 (BC26) – SetAutoCalcFields Method on RecordRef

Microsoft Dynamics 365 Business Central 2025 Wave 1 (version 26) introduces several enhancements to developer productivity and platform capabilities. One of the notable changes for AL developers is the new SetAutoCalcFields method added to the RecordRef data type.

This method brings the power of calculated flow fields to the RecordRef context, which previously lacked a built-in way to pre-calculate flow fields without manually invoking CALCFIELDS. Let’s explore what this update means, how it works, and how to use it effectively.

🧠 What is SetAutoCalcFields?

The SetAutoCalcFields method allows developers to specify which flow fields (calculated fields) should be automatically calculated when retrieving a record using the RecordRef data type.

This mimics the behavior of the SetAutoCalcFields method already available on the Record data type, extending it to scenarios where developers work with dynamic tables using RecordRef.

📘 Syntax:

RecordRef.SetAutoCalcFields(Field1, Field2, ...)

🛠️ Example

procedure DemoSetAutoCalcFields()
var
    RecRef: RecordRef;
    FieldRef: FieldRef;
    FieldRefAmount: FieldRef;
begin
    // Open the "Customer" table dynamically
    RecRef.Open(Database::Customer);

    // Get reference to the "Balance" flow field (Field No. = 21 in Customer)
    FieldRefAmount := RecRef.Field(21); 

    // Set the flow field to auto-calculate
    RecRef.SetAutoCalcFields(FieldRefAmount);

    // Find a record
    if RecRef.FindFirst() then begin
        // Now the "Balance" field will be auto-calculated
        Message('Customer Balance: %1', FieldRefAmount.Value);
    end;

    RecRef.Close();
end;

The introduction of the SetAutoCalcFields method on the RecordRef data type in Business Central 2025 Wave 1 is a welcome enhancement for developers. It provides a more efficient and streamlined way to work with AutoCalcFields when using RecordRef, leading to improved application performance and cleaner, more maintainable code.

Stay tuned for more.

Calculate Values Only for Visible FlowFields in Business Central

FlowFields in Business Central are incredibly powerful, allowing you to dynamically calculate values based on related data. However, what happens when you have many FlowFields, but only a few are visible on a page? Calculating all of them can lead to unnecessary performance overhead. Business Central’s clever design allows you to optimize this process by calculating values only for visible FlowFields, significantly improving page load times and overall system responsiveness. Let’s delve into how this works and why it’s beneficial.

FlowFields retrieve data dynamically, often requiring complex calculations or lookups. While this provides real-time insights, it can also impact performance, especially when dealing with large datasets or numerous FlowFields.

Imagine a customer card page with dozens of FlowFields, but you only display a handful of them in the main view. Calculating all those FlowFields, even the hidden ones, wastes valuable resources. This is where Business Central’s “calculate only visible” feature comes into play.

The “Calculate Only Visible” Concept

Business Central intelligently determines which Flow Fields are currently visible on the page and only calculates those values. This optimization minimizes unnecessary computations, resulting in faster page loads and improved user experience.

Benefits of Calculating Only Visible FlowFields:

  • Improved Performance: Faster page load times, especially for pages with numerous FlowFields.
  • Enhanced User Experience: Smoother navigation and faster data retrieval.
  • Scalability: Allows you to design complex pages with many FlowFields without sacrificing performance.

Business Central’s runtime environment dynamically analyzes the page layout and determines which Flow Fields are currently displayed.

Business Central’s “calculate only visible FlowFields” feature is a powerful optimization that can significantly improve performance and user experience

Stay tuned for more.

How to Use SetLoadFields in Business Central for performance

With the release of Business Central Wave 1 2020 the partial records capability is available.

The partial records capability is allows for loading a subset of normal table fields when accessing the data source based on SQL.

To access the data source we use GET ,FIND NEXT. When we use these methods then runtime it load all fields and will hamper the performance while fetching the records.

Now from business central wave 2 2020 we can use ‘Partial records’ capability.

To accommodate partial records following methods available in business central. These methods are avaaible on RecordRef and Record datatype in AL. These methods are loaded in two groups i.e. Subsequent loads and Current load

MethodDescription
SetLoadFieldsSpecifies a set of fields to be initially loaded when the record is retrieved from its data source.
AddLoadFieldsAdds fields to the current set of fields to be initially loaded when the record is retrieved from its data source.
AreFieldsLoadedChecks whether the specified fields are all initially loaded.
LoadFieldsAccesses the table’s corresponding data source to load the specified fields.

Lets See few examples of these methods

Following example is for summation of Quantity fields from Item Ledger Entry .In this we would like to make calculation of only Quantity fields and not require the others fields to be loaded.

If you see SetLoadFields is call before data fetching operation starts. This will determine which fields needed for Find Set Call.

Advantage of this is that it gives you ability to load only those fields which are required to perform the operation which will make execution of code faster and give more performance. This feature is recommended to use while fetching the records and not for Insert,Modify or Delete operation.

Using the above method we added the required fields but what will happen to other fields which hasn’t been loaded .In such cases platform does an implicit GET and loads the missing fields, called as JIT Loading (Just in Time).

When JIT loading occurs, another access to the data source is required. These operations tend to be faster because they’re GET calls. GET calls can often be served by the server’s data cache or become clustered index seeks on the database.

Iterating over records in the database, enumerator is created based on selected fields and row is fetched when NEXT is called. but certain operations will not follow enumerator like if we passing a record by value and not by VAR .This operation actually creates new copy of the record and original records and copy of the record not share the filters ,selected field for loading. That means to access unloaded fields it need to trigger JIT load and but it will not update enumerator which means for future iterations also require JIT load.

To overcome such situations we can do following

  1. Pass the record reference using VAR.
  2. Call AddLoadFields on the original records before passing by value.
  3. Use SetLoadFields before calling FIND,GET,NEXT.

Hope this will help you to understand for partial records and stay tuned for more updates.

For more updates you can follow my blog on following channels.