BC Teardown #2: Why your BC totals and reports crawl

The pain

A total that took a second when you went live now takes minutes. Users stare at a spinner and blame Business Central.

Why it happens

Custom code loops through every record and loads every field, just to add up one number. It gets slower with every order you post.

Before: Loop every row to add it up

SalesLine.SetRange("Document Type",
  SalesLine."Document Type"::Order);
if SalesLine.FindSet() then
  repeat
    Total += SalesLine.Amount;
  until SalesLine.Next() = 0;

// Reads every row and every column

After: Let SQL do the math

SalesLine.SetRange("Document Type",
  SalesLine."Document Type"::Order);
SalesLine.CalcSums(Amount);
Total := SalesLine.Amount;

// When you must loop, load only
// the fields you actually use:
Item.SetLoadFields("No.", Description);

The result

One query returns the total instead of thousands of row reads. Partial records keep loops lean.

Tip: Pair CalcSums with a key that has SumIndexFields for the biggest wins.

Download this teardown as a PDF

Got a Business Central problem like this? Free 30 minute review of your setup. Senior Microsoft certified developers, and you talk to the person writing the code. Contact Dataflow Solutions.