5.4Performance optimization & best practices
The essentials in 30 seconds
- Performance = push to the database: filter server-side (lever #1, check the SQL), bring back only what is needed, run joins and grouping on the database — never bring back everything to process on the workstation.
- Accuracy first: check joins (no duplicates inflating totals — trap #1), reconcile totals, handle nulls — a wrong report is worse than none.
- Professional = tested: verify accuracy, layout AND performance on real, large data before distributing — fast on 100 rows says nothing about a million rows.
Running mini-case — Farès saves the year-end report
Final episode: Farès's year-end closing report, management controller in Algiers, collapses — ten minutes of waiting, and a grand total that does not reconcile with accounting. Diagnosis: everything is brought back then filtered on the workstation, a linked subreport sits in the Details section, and a dubious join inflates totals with duplicates. Farès pushes the filter server-side, moves the subreport up to group level, fixes the join, reconciles totals — and tests on real volume before distributing to management. Final episode of cases 5.1-5.3 — Farès closes distribution, prompts and integration with rigor.
Final episode of cases 5.1-5.3 — Farès applies performance and accuracy before distribution to management.
1. Performance levers
The guide closes on what sets a professional report apart. Performance: (1) filter server-side (bring back ONLY what is needed — THE major lever, check the generated SQL); (2) bring back only the useful tables and fields; (3) push work to the database (server-side joins, grouping, aggregation, SQL commands); (4) watch linked subreports (at group level, not Details — otherwise one execution per row); (5) use indexes; (6) avoid complex formulas forcing client-side processing. Farès applies these levers in order, measuring.
Performance mostly depends on the amount of data brought back and what is done on the database vs on the workstation: minimize transferred data, maximize database-side work.
2. Accuracy, readability, reusability
Accuracy first: check joins (no duplicates inflating totals — trap #1), reconcile totals against known values, handle nulls. A wrong report is worse than none: it misleads and drives wrong decisions. Farès systematically reconciles his grand total against accounting.
Readability: consistent, sober formatting, aligned columns, formatted numbers, clear structure, controlled pagination. Reusability: parameters for a generic report (one template for all cases instead of one report per case), reusable formulas, standardized company templates.
3. Maintainability and testing
Maintainability: name formulas and parameters clearly, document logic, break down complex formulas, avoid over-complexity. Adapt to the audience (summary for management, detail for operations) and to use (right export format — 5.1).
Test on real, large data (a report fast on 100 rows can collapse on 1 million). The goal: produce accurate, readable, fast and useful statements — a good report is first accurate and clear, then fast. Farès only distributes his closing after this triple check.
🔎 Concrete example — Make reliable before distributing
The reflexes that make the difference.
Filter server-side (check the WHERE).
Reconcile the grand total against a known figure (duplicates?).
Test on real volume before distribution.Reading: three moves — server, reconciliation, real volume.
Lesson: never distribute an untested report.
Section vocabulary
- Server-side filtering
- Push selection to the database to bring back only needed data — lever #1.
Simple example: Farès checks that his filter appears in the WHERE of the generated SQL.
What it is not: bringing everything back then filtering on the workstation.
Reflex: is my filter in the WHERE?
- Push to the database
- Run joins, grouping and aggregation server-side rather than on the workstation.
Simple example: Farès aggregates database-side instead of bringing back thousands of rows.
What it is not: complex formulas forcing client-side processing.
Reflex: who does the work: the database or my workstation?
- Accuracy
- The first quality: verified joins, reconciled totals, handled nulls.
Simple example: Farès reconciles his grand total against accounting.
What it is not: a pretty but wrong report.
Reflex: did I reconcile my totals against a known value?
- Duplicate trap
- Dubious joins that multiply rows and inflate totals.
Simple example: Farès fixes the join that duplicated budget rows.
What it is not: a total accepted without verification.
Reflex: are my joins clean and verified?
- Real-volume test
- Verifying accuracy, layout and performance on real, large data.
Simple example: Farès tests his closing on the full year before distribution.
What it is not: a test on a 100-row sample.
Reflex: did I test on production volume?
In practice — Optimize and secure a report
- PERFORMANCE: filter SERVER-SIDE (check the SQL), select only useful tables/fields, push joins/grouping to the database, place linked subreports at group level.
- ACCURACY: check joins (no duplicates), reconcile totals against known values, handle null values.
- READABILITY & REUSABILITY: consistent, sober formatting, aligned columns, formatted numbers; parameters for a generic report, well-named formulas.
- TEST on REAL, LARGE data (accuracy, layout, performance) BEFORE distributing.
Ready-to-say script — present a secured report to management
“Before distributing this closing, I applied our triple check: filter pushed server-side with verified SQL, joins fixed with total reconciled against accounting, and test on the real volume of the year. The report is accurate, readable and fast — and I deliver it in PDF for reading, with data-only Excel for your analyses.”
Say to management to get reliability approved before official distribution.
Copy-paste template — pre-distribution check sheet
PRE-DISTRIBUTION CHECK SHEET — report: ___
PERFORMANCE: server-side filter? YES / NO · SQL verified? YES / NO · subreports at group level? YES / NO
ACCURACY: joins verified? YES / NO · totals reconciled against ___ · nulls handled? YES / NO
TEST: real volume ___ rows · layout OK? YES / NO · FORMAT: PDF / Excel / CSV · distributed on: ___Solo / tight budget
Farès alone: checking the WHERE and reconciling one total takes ten minutes and avoids the worst.
Very small business / small team
Triple check on the wall: server, accuracy, real volume — everyone verifies before sending.
SME in Algiers
Farès in Algiers schedules a closing acceptance: test on the full year, accounting reconciliation, then distribution.
Starting team
Pair: one optimizes (server, volume), the other verifies (joins, totals) before distribution.
✓ Best practices
- Check “Show SQL Query”: filters and grouping must be server-side.
- Always reconcile your totals with an expected figure.
- Test on realistic volume, not only on a sample.
✗ Common pitfalls
- Distributing a WRONG report (unverified joins, inflated totals).
- Optimizing appearance while neglecting accuracy.
- Testing only on little data (collapse in production).
Key takeaways
- PERFORMANCE (lever #1): filter SERVER-SIDE (bring back ONLY what is needed — check generated SQL), select only useful tables/fields, PUSH joins/grouping/aggregation to the DATABASE, use INDEXES, linked subreports at GROUP level.
- ACCURACY first: check JOINS (no duplicates — trap #1), RECONCILE totals, handle NULLS. A WRONG report is worse than none (it misleads).
- BEST PRACTICES: READABILITY (consistent/sober, aligned, formatted, structured), REUSABILITY (parameters, templates), MAINTAINABILITY (name/document, break down), ADAPT to audience.
- TEST on REAL, LARGE data before distributing. Goal: ACCURATE, READABLE, FAST and USEFUL statements. A good report is first ACCURATE and CLEAR, then fast.
48h challenge — apply tomorrow
Fill in the pre-distribution check sheet (template above) for 1 real report by Farès: are the server-side SQL, the total reconciliation and the real-volume test in it?
- I can list performance levers in order.
- My report is reconciled and tested on real volume.
- I know why accurate and clear comes before fast.
Frequently asked questions
How do I make a report fast on large volumes?
The key: minimize brought-back data and PUSH work to the database. Levers by impact: (1) FILTER SERVER-SIDE (lever #1) — selection translated into WHERE, simple conditions on database fields, check “Show SQL Query” (the filter must be in the WHERE); client-side filtering (bring everything back then filter) is catastrophic. (2) Bring back only useful TABLES and FIELDS. (3) Push GROUPING and AGGREGATION to the database (server-side grouping, SQL command). (4) Use INDEXES (filter/join on indexed fields). (5) Mind JOINS (avoid cartesian products). (6) Linked subreports at GROUP level, not Details. (7) Limit complex client-side formulas. TEST on REALISTIC volume, measure, find the bottleneck. Golden rule: minimize brought-back data + maximize database-side work.
What are the best practices for a professional, reliable, maintainable report?
Four pillars plus performance. (1) ACCURACY (most important — a wrong report misleads): check JOINS (duplicates = trap #1), RECONCILE totals against known values, handle NULLS, test with real data. (2) READABILITY: consistent, sober formatting, aligned columns, formatted numbers and dates, clear structure (sections, groups, titles, highlighted totals), controlled pagination, detail level adapted. (3) REUSABILITY: parameters (generic report), reusable broken-down formulas, company templates, dynamic lists. (4) MAINTAINABILITY: clearly name formulas and parameters, document, break down complex formulas, avoid over-complexity. (5) PERFORMANCE (server-side, minimal volume). Plus: ADAPT to audience and use and TEST before distribution. The goal is not to “master the tool” but to produce ACCURATE, CLEAR, FAST and USEFUL statements.
Which quality comes before all others in a report?