Performance Comparison: Building Filters with TextBuilder in AL

When working with AL (Dynamics 365 Business Central), we often need to dynamically construct complex filter strings. A common example is filtering users based on their User Security ID. For this purpose, AL provides the TextBuilder, a wrapper around the .NET StringBuilder, optimized for efficient string manipulation.

---

The Scenario

We want to build a filter for the Access Control table that excludes certain special users. The IDs are read from the User table and concatenated into a filter string separated by |.

---

Option 1: Simple Approach with TrimEnd()

local procedure FilterOutSpecialUsers(var AccessControl: Record "Access Control")
var
    User: Record User;
    UserSelection: Codeunit "User Selection";
    FilterTextBuilder: TextBuilder;
begin
    UserSelection.FilterSystemUserAndGroupUsers(User);
    repeat
        FilterTextBuilder.Append(User."User Security ID");
        FilterTextBuilder.Append('|');
    until User.Next() = 0;

    AccessControl.FilterGroup(2);
    AccessControl.SetFilter("User Security ID", FilterTextBuilder.ToText().TrimEnd('|'));
    AccessControl.FilterGroup(0);
end;

Characteristics

• Two Append operations per user (ID + |)
• Requires a TrimEnd() at the end, which scans the entire string
• Simple code, but extra cost for large datasets

---

Option 2: Conditional Separator

local procedure FilterOutSpecialUsers(var AccessControl: Record "Access Control")
var
    User: Record User;
    UserSelection: Codeunit "User Selection";
    FilterTextBuilder: TextBuilder;
begin
    UserSelection.FilterSystemUserAndGroupUsers(User);
    repeat
        if FilterTextBuilder.Length > 0 then
            FilterTextBuilder.Append('|');
        FilterTextBuilder.Append(User."User Security ID");
    until User.Next() = 0;

    AccessControl.FilterGroup(2);
    AccessControl.SetFilter("User Security ID", FilterTextBuilder.ToText());
    AccessControl.FilterGroup(0);
end;

Characteristics

• One or two Append operations per user, but no final scan
• Avoids unnecessary removal of the last separator
• Scales better for large datasets

---

Performance Comparison

---

Best Practice

For large datasets or performance-critical scenarios, Option 2 is the better choice. It avoids unnecessary operations and works more efficiently with TextBuilder.

---

Important Note on Length Property

The Length property of .NET’s StringBuilder (and therefore AL’s TextBuilder) does not calculate the length on each access. It simply returns an internal field that is updated during modifications. This means checking Length > 0 is O(1) and extremely fast.

Source: https://github.com/dotnet/runtime/blob/5535e31a712343a63f5d7d796cd874e563e5ac14/src/libraries/System.Private.CoreLib/src/System/Text/StringBuilder.cs#L385

---

💡 Tip: If you expect thousands of IDs, also check Business Central’s maximum filter length to avoid runtime errors.