Reporting performance
A dashboard may look like one page, but the database sees several separate questions: totals, trends, grouped values, grids, filters, and comparisons. Refreshing the page can run all of them again.
PHPRunner can assemble reports, charts, grids, and other elements into a dashboard. Performance depends on making each underlying query efficient and choosing deliberately which results must be live.
Quick answer: Treat every dashboard element as a query with a measurable cost. Find the slow query, examine how it filters, joins, groups, and sorts, and add the indexes that support those operations. Only after the live query is understood should you introduce summary tables or cached results—and their refresh schedule must match the business meaning of “current.”
A slow dashboard does not prove that every component is slow.
One chart may scan years of transaction data while the remaining elements return quickly. Another page may repeat the same expensive aggregation several times. Measure the server request and run the underlying SQL in the database's query tool so you can identify the actual cost instead of guessing from the final page.
Start with the result the reader needs. A dashboard rarely requires every transaction column or unlimited detail rows. Return the smallest useful dataset and apply filters before expensive grouping whenever the database can do so correctly.
An index is useful only when it supports the query's real access pattern.
Fields used in joins, selective filters, and common sort orders are the first candidates. A report filtered by CustomerID and OrderDate may need a composite index beginning with those fields; two unrelated single-column indexes may not provide the same plan.
Use the database's execution plan to confirm whether it scans the entire table, uses the expected index, creates a large temporary result, or sorts more rows than necessary. Adding many speculative indexes can slow writes and consume storage without fixing the report.
The correct architecture depends on how quickly the number must change.
| Requirement | Suitable approach | Tradeoff |
|---|---|---|
| Must reflect the latest committed transaction | Run an optimized live query. | The database performs the work on every refresh. |
| Can be several minutes old | Cache the calculated result for a short period. | Users may briefly see an older value. |
| Aggregates years of detailed history | Maintain daily or monthly summary rows. | The summary needs a reliable refresh process. |
| Used for scheduled management reporting | Calculate the report in advance. | The report represents a defined reporting cutoff. |
Build the presentation from queries whose cost and freshness are already understood.
Use PHPRunner's SQL Query screen and Query Designer to limit fields, add joins and filters, group data, and preview results. Reports can provide grouped totals and cross-tab views; dashboards can combine reports, charts, grids, single records, searches, maps, and snippets.
Dashboard elements can refresh periodically, but a shorter refresh interval multiplies database work. Choose it from the required freshness, not from the assumption that more frequent updates are always better.
Optimize one measured query at a time.
Identify the slow element, inspect its SQL and execution plan, reduce unnecessary rows and columns, and add a justified index. If the correct live query remains too expensive, decide how stale the result may be and introduce caching or summaries with that limit clearly defined.