Business Central Performance Tuning: SQL Patterns That Actually Work

"Business Central is slow" is almost never a useful problem statement — it's a symptom. The fix starts with finding out exactly where the time is going, and it's usually not where the person reporting the issue assumes.

Profile before you touch code

Before changing anything, get real data:

• SQL execution plans for the slow operations — are you seeing table scans where you'd expect index seeks?
• Wait statistics from SQL Server — is this CPU-bound, lock-bound, or I/O-bound?
• AL telemetry (if you're on BC SaaS or have Application Insights wired up) — which specific procedure or page is the slow part actually in?

We've seen "the whole system is slow" turn out to be one unindexed FlowField calculation running inside a loop. Guessing at fixes without this data wastes time and sometimes makes things worse.

Pattern 1: Use SetLoadFields aggressively

This is the single highest-leverage, lowest-risk fix in most BC codebases. By default, FindSet and similar operations pull every field on a record, even if you only touch two of them. SetLoadFields tells BC to only fetch the fields you actually need:

Customer.SetLoadFields("No.", Name, "Credit Limit (LCY)");
if Customer.FindSet() then
    repeat
        // only these three fields are populated
    until Customer.Next() = 0;

On wide tables (Customer, Item, G/L Entry) with many FlowFields, this alone can cut query time significantly, because FlowFields are calculated on read unless explicitly excluded.

Pattern 2: Query objects for aggregation, not loops

If you're looping through records in AL to sum, count, or group data, you're doing in the application layer what SQL is built to do natively. A Query object pushes aggregation down to SQL Server:

query 50100 "GL Balance By Dimension"
{
    QueryType = Normal;
    elements
    {
        dataitem(GLEntry; "G/L Entry")
        {
            column(GlobalDim1; "Global Dimension 1 Code") { }
            column(Amount; Amount) { Method = Sum; }
        }
    }
}

We've taken aggregation queries on tables with 200 million+ rows from multi-minute timeouts to sub-second responses this way. The difference isn't marginal — it's architectural.

Pattern 3: Design SIFT keys around your actual query patterns

SIFT (Sum Index Flow Technology) is what makes FlowFields fast — but only if the key covering your FlowField's filters actually matches how you're querying. A FlowField that sums by customer and date, queried by customer and item, will not use SIFT efficiently and falls back to a full calculation.

Review your most-used FlowFields against your actual filter patterns in code and reports. Misaligned SIFT keys are a very common, very fixable source of slow list pages and reports.

Pattern 4: Set-based posting over row-by-row

AL makes it easy to write posting logic that processes one record at a time with individual inserts, validates, and modifies. At small volumes this is invisible. At scale — batch posting thousands of lines — row-by-row processing multiplies overhead badly.

Where possible, batch your validations, minimise the number of separate Modify() calls per record, and avoid re-reading records you already have in memory. This is less about a specific code pattern and more about a discipline: know how many SQL round-trips your code generates per posted document, and keep that number down.

Pattern 5: Covering indexes for reporting queries

If a report or API consistently filters and sorts by a specific combination of fields that isn't already a key, add a key that covers it — including the fields you're returning, if the table isn't too wide. This turns a table scan plus sort into a direct index seek, and it's often the fix behind "this report used to be fast."

What to avoid

• Don't add indexes speculatively. Every key has a write-time cost. Add them based on profiled evidence, not guesses.
• Don't assume the cloud will absorb bad patterns. BC SaaS has the same fundamental SQL behaviour underneath — moving to the cloud doesn't fix an N+1 query pattern.
• Don't optimise code nobody profiled. It's tempting to "clean up" the first slow-looking loop you see. Fix what the data tells you is actually the bottleneck.

The takeaway

Performance work in Business Central is mostly SQL work wearing an AL costume. The AL layer determines what queries get generated; SQL Server determines how expensive they are. Profile first, fix the specific bottleneck, and measure again before moving to the next one.