Lab / Open methodology
Month-end reports across five million records
5,000,000 orders summarized: 1.22 min → 175.5 ms. Synthetic lab; median of 10 runs. Open code and raw data.
Unindexed month-end reporttoSame report + covering index
SQLite 3.46 LAB MEASUREMENT
500 customer balances from 5 million orders.
Run the same open-order balance report. Business rules and engine stay the same; the access path that scans the whole table per customer changes.
Repeated month-end checks take less time and CPU work. The team can move from waiting for a report to reviewing its balances.
Unindexed month-end report
Scan the entire order table again for each customer balance.
- CPU time
- 1 m 13.1 s
Same report + covering index
The same SQL finds amounts using customer and status keys.
- CPU time
- 175.473 ms
Index + grouped-query control
An alternative that groups orders once; every result is retained, faster or slower.
- CPU time
- 254.617 ms
Bars start at zero and share the same time scale across implementations.
Add a composite index covering customer, order status and amount. SQL text is identical before and after. Measure a grouped-query alternative as a separate control.
All load points and measurements
| Implementation | Median duration | CPU time |
|---|---|---|
| Unindexed month-end report | 1.435 s | 1.435 s |
| Same report + covering index | 3.833 ms | 3.835 ms |
| Index + grouped-query control | 5.507 ms | 5.509 ms |
| Implementation | Median duration | CPU time |
|---|---|---|
| Unindexed month-end report | 14.751 s | 14.749 s |
| Same report + covering index | 36.498 ms | 36.504 ms |
| Index + grouped-query control | 52.952 ms | 52.941 ms |
| Implementation | Median duration | CPU time |
|---|---|---|
| Unindexed month-end report | 1 m 13.11 s | 1 m 13.1 s |
| Same report + covering index | 175.502 ms | 175.473 ms |
| Index + grouped-query control | 254.653 ms | 254.617 ms |
CPU cost at your workload
Compare the recorded CPU time at your own monthly volume.
One batch run = 5,000,000 Order records.
CPU time reduced / month202.5564 hours
Processing cost difference / month—Calculated after you enter a unit price.
Lab CPU time × your volume and unit price. This is not a hosting bill forecast; idle capacity, networking, storage, licences and migration are excluded.
Try it yourself
Replay the recorded lab results side by side.
As the workload grows
Duration by workload
- Before
- After
Duration by workload (ms)
Show as a table
| Order records | Before | After |
|---|---|---|
| 100,000 | 1,434.97 ms | 3.83 ms |
| 1,000,000 | 14,751.16 ms | 36.5 ms |
| 5,000,000 | 73,106.21 ms | 175.5 ms |
How we measured
Environment, protocol and technical analysis
Environment and data
- Environment
- Apple M3; Docker Linux arm64, 2-CPU quota, 1 GiB memory; PHP 8.4.26 / SQLite 3.46.1.
- Dataset
- 500 customers, 100,000 / 1,000,000 / 5,000,000 orders. Identical rows streamed into two databases; complete 500-row results match independent PHP and Python totals.
- Tools
- PHP 8.4.26 · SQLite 3.46.1 · hrtime / getrusage · native arm64 Docker · independent Python oracle
- Repetitions
- 3 sizes × 3 queries × 10 repetitions, plus 18 warmups. All 108 observations and every customer balance are published.
500 customer balances from 5 million orders.
Run the same open-order balance report. Business rules and engine stay the same; the access path that scans the whole table per customer changes.
Open-order totals for 500 customers using deterministic synthetic integer cents. Three sizes and three arms fixed before measurement. Identical SQL before/after indexing plus a grouped-query control. Two warmups then ten repetitions with rotating arm order. All 500 output rows match an independent oracle every time. Reads exclude loading, index construction and validation; two new in-memory databases per size, warm repetitions. Write workload and monetary billing effects were not measured.
Inspect the measurement script ↗What changes for the business?
Repeated month-end checks take less time and CPU work. The team can move from waiting for a report to reviewing its balances.
Change and scope
Add a composite index covering customer, order status and amount. SQL text is identical before and after. Measure a grouped-query alternative as a separate control.
500 customers; 100 thousand, 1 million and 5 million synthetic orders. SQLite 3.46.1, warm in-memory data and identical hardware. Read time excludes loading and index construction; setup time and storage overhead are published separately.
Every query at every measured size
| Orders | Unindexed | Same query, indexed | Grouped-query control |
|---|---|---|---|
| 100,000 | 1.435 s | 0.004 s | 0.006 s |
| 1,000,000 | 14.751 s | 0.036 s | 0.053 s |
| 5,000,000 | 73.106 s | 0.176 s | 0.255 s |
Index setup and storage cost
At 5 million records the index allocated 100,130,816 additional bytes. The single construction observation was 4.086 seconds. These are separate from the median read time. An index also changes the write workload, which this experiment does not measure. Query plans and every setup observation.
Where to start in your system
Identify the slow operation, data volume and peak workload. Establish its baseline, change the code and access pattern, then compare the same result.
What we did
Business rule and data
AIPrepared the synthetic workflow, tests and measurement automation.
VerificationPublished code and raw outputs are available for review; this is not a client project.
Implementation change
AIAdd a composite index covering customer, order status and amount. SQL text is identical before and after. Measure a grouped-query alternative as a separate control.
VerificationOutput equivalence was independently checked; every repetition is retained.