Why Business Central Report Performance Degrades Over Time

Business Central report performance degrades as the database grows. Reports that ran in seconds at go-live take minutes a year later. After two or three years of accumulated transactions, the slow ones start timing out. The cause is structural, and no amount of tuning inside the ERP closes the gap.

What slows Business Central report performance

BC renders native reports through RDLC, Microsoft’s server-side report layout language. RDLC was built for fixed-layout, paginated output. In Microsoft’s own troubleshooting guidance for developers, the recommendation for document reports is to use Word layouts because RDLC layouts produce slower performance. Finance teams running rolling twelve-month P&Ls across multiple entities with custom dimensional breakouts are pushing RDLC well past what it was built for.

Four issues compound. G/L Entry, Dimension Set Entry, and Item Ledger Entry grow by millions of rows per year, and most reports scan them rather than read from a pre-aggregated layer. Dimensions beyond BC’s two global and up to eight shortcut dimensions don’t sit on the ledger entry tables as indexed columns; they resolve through the Dimension Set Entry structure, which means an additional join for every filter. AL extensions add sequential logic to base report objects, with each extension contributing overhead that aggregates across the run, a pattern Microsoft documents in its performance guidance for developers. And BC’s native multi-company reporting iterates through each company’s data rather than reading from a shared aggregate, so cross-entity reports multiply the underlying work.

SQL Server underneath BC is tuned for the workloads BC was designed to run: posting entries, validating documents, processing batches. A five-dimension twelve-month report across three entities is a different workload, and the same database isn’t optimized for it.

Most BC environments hit this wall around the 18-to-24-month mark. By that point, two full fiscal years of detail sit in the GL, dimensional tagging has been extended several times, and a handful of customizations have been layered into the close reports. The reports stop running in time for the meetings they were built for.

Compute scaling has diminishing returns

The first response to a slow report is usually a request for more compute. Microsoft tunes the BC Online infrastructure, and tenant configuration has some headroom. Returns flatten quickly. Doubling the database tier doesn’t halve the run time, because the constraint is the query plan and the RDLC pipeline, not raw CPU.

Read Scale-Out, the Azure SQL feature BC Online uses to route read-only workloads to a secondary replica, helps for some queries. The catch is that it only applies to objects explicitly marked with the DataAccessIntent property set to ReadOnly, and even then Microsoft notes that the setting “doesn’t guarantee that your data access will be running on the secondary replica.” Extension-modified reports often lose the ReadOnly classification and fall back to the primary. Performance tuning at the tenant level rarely closes more than a small fraction of the gap, and hosting costs increase in the process.

Analysis Views, the BC feature partners often reach for at this stage, help for specific use cases. They pre-compute aggregates against a defined dimension set so analysis pages and analysis reports can serve results without re-scanning the ledger. The structures are fixed, the layout options are narrower than finance teams need for board packages or formatted statements, and Analysis Views don’t write data back to BC. They handle aggregate views; flexible reporting remains a separate problem.

Where Business Central report performance recovers

Pulling the data into Excel through BC’s REST API and OData web services takes RDLC out of the path. The SQL tables and the data remain in BC. Excel’s calculation engine handles the rendering locally on the user’s machine, against data the API has already extracted.

Refresh runs against local CPU, so one slow refresh doesn’t queue behind other users. Report layout is no longer bound by what RDLC supports, which removes a category of workarounds finance teams have been writing for years to get reports into a usable shape. Dimensions become cell formulas, so a controller can build a five-dimension pivot without filing a ticket with the partner. Refresh can also be scheduled and distributed: the workbook updates overnight, the PDF lands in a SharePoint folder, and the CFO opens a current report instead of waiting for one to run.

This is the same approach FRx and Management Reporter used for two decades in the GP world. Microsoft moved both products through end-of-life, with FRx 6.7 reaching the end of its lifecycle and Management Reporter following. The February post on FRx replacements covered why GP customers feel the gap during migration. The same architectural cause produces the performance gap once a BC environment matures: the native reporting layer was never designed for the data volumes and report complexity finance teams produce today.

Diagnosis

The diagnosis starts with row counts. G/L Entry, Dimension Set Entry, and Item Ledger Entry are the usual suspects, and anything past a few million rows compounds the cost of every report that touches them. AL extensions are the next place to look, because they hook into base report objects in sequence and each one adds latency proportional to its complexity. The third question is how many entities a single report consolidates: query cost grows roughly linearly with the number of companies.

Look out for a future post that takes this into multi-environment consolidation, where the performance issues compound and BC’s native tooling runs out of room.

Velixo connects Excel directly to Business Central through API v2 and web services, handles the dimension joins and multi-entity queries on the user’s machine, and supports scheduled report distribution to email, SharePoint, and Microsoft Teams. Finance teams use it to keep close reports current and to write budgets, journals, and forecasts back to BC from the same workbook through Velixo’s universal writeback. Learn more about Velixo for Business Central.