+44 (0)1782 976577 letstalk@eazydynamics.com

Business Central Slow Reports: Causes & Fixes

Business Central, Support

A small snail makes its way upwards. Text on image reads 'Slow Reports & Queries? - Causes and Fixes'
Table of Contents

    Business Central Slow Reports – Causes & Fixes (High Level First, Deep-Dive Second)

    If Business Central reports and queries are crawling, it’s rarely “just the cloud.” We show the quick, non-technical fixes users can apply today, then dive into the technical tuning – keys, telemetry, data hygiene, and layouts – that make slowness disappear for good.

    Part 1 — High Level guide (for non-technical readers)

    TL;DR

    Most slow reports come from asking for too much data, using the wrong filters, or a heavy report layout. Start by narrowing the scope and scheduling heavy runs out of hours. If that doesn’t solve it, we’ll optimise the app behind the scenes.

    Why reports and queries feel slow

    • Huge date ranges: running “All dates” forces Business Central to trawl through years of entries.

    • No filters: “All customers” or “All locations” multiplies the work.

    • Heavy layouts: pretty PDFs with lots of groupings and totals can be slow to render.

    • Busy environment: imports, posting and other jobs running at the same time create queues.

    Fast fixes you can do today

    Use this 5-minute checklist:

    1. Tighten the date → last month/quarter (or a specific period you actually need).
    2. Add filters → Customer/Item/Location/Dimensions you care about.
    3. Save your filters → Create a saved view so teams don’t drift back to “All”.
    4. Split big runs → One month at a time; or by business unit/location.
    5. Pick the right output → If you only need numbers, export to Excel/CSV instead of a formatted PDF.
    6. Schedule heavy reports → Run them overnight and get the result by email.
    7. Try another layout → If preview is quick but the final PDF is slow, ask us to simplify the report layout or use a lighter one.

    Quick diagnosis: what’s actually slow?

    Use this decision path:

    • Preview is quick; PDF/Print is slow → The layout is the bottleneck.

    • Everything is slow on “All dates”; last month is fine → You’re pulling too much data.

    • Slow only at certain times → The system is busy with other work; schedule reports off-peak.

    • Slow since a new app/update → Likely a change; we’ll test in a sandbox and fix.

    What “good” looks like

    • Typical month/quarter reports run in seconds to a couple of minutes.

    • Big “all-year” reports run overnight, delivered by email.

    • Teams use saved filters/views; nobody runs “All dates” by default.

    • If formatting isn’t needed, people export data instead of waiting for a heavy PDF.

    Related Article: Slow Page Loads

    Signals → Likely cause → First fix (at a glance)

    Signal you see Likely cause First fix Next step
    Preview fast, PDF slow Layout overhead (RDLC/Word) Simplify groups/expressions If still slow, switch output to CSV/Excel or lighter layout
    Only “All dates” is slow Too much data; missing key Filter last 90–365 days Add composite key matching filters; trim dataset
    Slow at 10am daily Concurrency with posting/imports Schedule runs off-peak Separate queues & categories; adjust concurrency
    Slow since last app/update Extension overhead Reproduce in sandbox without app Profile subscribers; add feature flag or keys
    Finance statements crawl Wide column span; stale analysis views Shorter column set; refresh views off-peak Pre-aggregate; push to Power BI for heavy slicing

    If you find this article useful, click and subscribe to our newsletter - Business Central Uplugged - helping you use what you've already paid for!

    Part 2 — Technical deep dive (for admins and developers)

    We’ll keep this practical. Use it as a runbook to diagnose and fix slow reports and queries.

    1) Measurement first (don’t guess)

    • Client Performance Profiler (BC web client): capture page/report timings.

    • AL Performance Profiler (VS Code): profile object execution during debug sessions.

    • Application Insights: requires instrumentation configured; gives object durations, long calls and signals.

    • Baseline runs: fix the date range and parameters; record preview vs PDF time. Repeat after each change.

    2) Key strategy (BC “keys”, not SQL indexes)

    Keys must match real filter/sort patterns. Extra keys help reads but increase posting cost and storage.

    Rules of thumb:

    • Add the minimum number of keys that align with saved filters and common sorts.

    • Prefer composite keys that line up with how users filter.

    • Re-review quarterly; remove unused custom keys.

    Common objects & candidate keys (use only if they match usage):

    Table Candidate key pattern (example) When it helps
    Item Ledger Entry Posting Date, Item No., Location Code Period/Item/Location filtered item movement reports
    Value Entry Item No., Posting Date Cost/valuation by item over a period
    Cust. Ledger Entry Customer No., Posting Date AR ageing and statements by customer/period
    Vend. Ledger Entry Vendor No., Posting Date AP listings by vendor/period
    Sales Invoice Header/Line Sell-to Customer No., Posting Date Sales by customer/date reports
    Warehouse Activity Line Location Code, Posting Date Operational queries filtered by site/time

    Guardrail: Don’t add keys “just in case”. Measure, add the one that matches your heaviest report, measure again.

    3) AL patterns that keep datasets small and fast

    Do these:

    • Use FindSet()/FindSet(true,false) for large, read-only scans.

    • Call SetLoadFields() to fetch only required columns.

    • Prefer SetRange() over broad SetFilter() with wildcards.

    • Batch CalcFields and avoid calling FlowFields inside tight loops.

    • Use Query objects for narrow, key-filtered joins and server-side aggregation.

    Avoid:

    • Find('-') full-table scans.

    • Calling multiple FlowFields per row inside nested loops.

    • Wide Query objects that pull more columns than used.

    4) Dataset vs Layout: decide where to optimise

    Decision rules:

    • Preview ≤10s, PDF >60s → Layout first. Simplify RDLC groups, reduce expressions, trim page breaks. Consider Word layout only if grouping requirements are light.

    • Dataset export already slow → Dataset/keys first. Cut fields, pre-aggregate in AL, add the missing key.

    • Users only need numbers → Skip rendering. Provide CSV/Excel via scheduled job.

    RDLC guidance:

    • Aim for fewer nested groups; move totals into dataset when practical.

    • Avoid heavy expressions in cells; pre-calculate in AL.

    • Remove unused fields from dataset to reduce payload.

    Word layouts caveat: Quicker to render for simple documents, but limited for complex grouping/totals. If users need sophisticated presentation, keep RDLC and optimise or push analysis to Power BI.

    5) Data growth & housekeeping (with audit caveats)

    • G/L Date Compression: reduces historic detail; confirm audit policy before compressing.

    • Change Log: track fewer fields; set retention; don’t log high-churn fields without purpose.

    • Archive & close cycles: move old docs through proper archival; purge job queue logs.

    • Analysis Views: only keep dimensions actually used; schedule updates; avoid “update on posting” at scale.

    6) Concurrency and Job Queues

    • Create separate categories: POSTING, IMPORTS, REPORTS.

    • Assign dedicated queues and stagger schedules.

    • Set sensible Max Concurrent per queue. One noisy job must not block posting.

    • Heavy reports → overnight. Imports → off-peak. Posting → business hours lanes.

    7) Extensions & regression hunting

    • Suspect a partner/ISV app? Spin a sandbox without the app, replay the same report, compare timings.

    • Profile event subscribers attached to report/query events.

    • Use feature flags in your extension to toggle heavy logic.

    8) Case study (example change log)

    Scenario: “Sales by Item (12 months)” takes 7:40 to PDF.
    Changes:

    • Added composite key on Posting Date, "No.", "Location Code" in ILE extension.

    • Trimmed dataset: removed 9 unused columns; moved month bucketing to AL.

    • RDLC: reduced nested groups from 3 → 1; removed page-breaks per item.
      Result: Preview 9s, PDF 1:12.
      Trade-off noted: Slightly higher posting cost on ILE due to added key—acceptable for this tenant’s volume.

    FAQs – BC Slow Reports

    Why did reports slow down this quarter?
    Data volume grew, user filters widened, or an extension added extra work. We baseline timings, check dataset vs layout cost, and test recent changes in a sandbox to isolate the cause.
    What filters should be standard on heavy reports?
    Posting Date (last 90–365 days), relevant Location/Business Unit, and key dimensions. Publish these as saved views so users stop running “All”.
    Will adding keys always make things faster?
    They speed reads when they match real filters, but they increase posting cost and storage. Add the minimum set, validate impact, and prune unused keys later.
    Is switching to a Word layout the answer?
    Sometimes. Word can render faster for simple reports, but it’s limited for complex grouping. For heavy analysis, optimise RDLC or provide CSV/Excel or Power BI.
    How do we measure improvement without SQL access?
    Use the Client Performance Profiler, AL Profiler, and Application Insights (with instrumentation configured). Compare before/after with identical parameters.
    When should we schedule reports?
    Any run spanning long periods or large datasets should be scheduled overnight or off-peak, on a dedicated REPORTS queue, separate from POSTING and IMPORTS.

    Want us to take a look?

    We’ll baseline your top five slow reports, add one targeted key, trim the dataset, simplify the layout, and separate your job queues – sound good? Get in touch.

    Enter your details below or call us on +44 (0) 1782 976577