A report that is fast on a single day and dead on a month range is the most common and most consistently misdiagnosed complaint in ERP reporting. It looks like data volume. It usually is not.
What this covers
- Diagnosis against a captured baseline, because without one any claimed improvement is an opinion.
- Logical reads as the measure, not runtime. Runtime moves with server load and cache state; pages touched does not.
- Predicate rewrites first. Where an expression around an indexed column prevents a seek, moving the arithmetic to the constant restores the index with no schema change at all.
- Index design, and the supportability conversation that comes before applying one to a schema you do not own.
See it in detail
SQL Server performance for ERP reporting is the full method. The month-to-date timeout is one worked instance of it.
Who this is for
Anyone with a report that is quick for a day and dies on a month, or a nightly job whose window has quietly closed. Typically the complaint arrives described as a volume problem, and the hardware has already been discussed.
It is the wrong service if the report returns the wrong rows quickly. That is a definitional problem, and making it quicker makes it wrong sooner.
What we would look at first
- What exactly the date filter filters on, and whether anything indexes it. That single question resolves a large share of these before anything else is examined.
- Logical reads, not runtime. Runtime moves with server load and cache state; pages touched does not, so it is the only number worth comparing before and after.
- Whether an expression wraps an indexed column. If so, moving the arithmetic to the constant restores the seek with no schema change at all — the cheapest available fix and the one most often missed.
- Whose schema it is, because that determines whether an index is a change you are permitted to keep through an upgrade.
What determines the cost
Whether a baseline exists. If nothing was captured before, capturing it is the first work and it is not optional — without it any claim about the result is an opinion, and this site does not publish those. Then: whether the fix is a predicate rewrite or a schema change, whether the schema belongs to a vendor, and how many reports share the same underlying access path, which is frequently more than anyone expects and is the reason one diagnosis sometimes settles several complaints.
Proof
The month-to-date timeout is one instance start to finish, and the technical write-up carries the confirmation steps as well as the diagnosis — including what was checked to rule out volume before anything was changed. The method is written out independently of any one report, so it can be applied without engaging anybody.