Why Your ERP Report Is Fast on Today and Times Out on Month‑to‑Date

It is almost never the amount of data. It is which column the date filter lands on, and whether anything indexes it.

This is the most common performance complaint in ERP reporting and the most consistently misdiagnosed. The report returns instantly for today. Widen it to week‑to‑date or month‑to‑date and it hangs, then times out. Because the pain scales with the date range, it reads like a data‑volume problem, and the usual response is to rewrite the query or ask for a bigger server. Both are usually wrong.

The short version. A short window and a long window frequently read different tables. Today often reads open, in‑flight records. Week and month have to reach into posted history. If the history table has no index on the date column you are filtering, the database has no way to skip rows — it reads the entire table to find the ones in your range. The fix is often a single index. The care is all in applying it.

Why the two date ranges are not the same query

Transactional ERPs almost always split their data in two: a live table for records still moving, and a history table for records that have posted. Receipts, invoices, picks and shipments all tend to follow this pattern under different names.

A well‑built report follows that split. Today reads the open table only, because everything from today is still open. Week and month union the open table with history, because most of that period has already posted. Those are two genuinely different execution plans against two very differently sized tables, so treating the slow one as “the same report, just more data” misses what is actually happening.

The live table is small — it holds only what has not yet posted, so it is naturally self‑trimming. The history table holds years. That is the one where indexing decides whether your report returns in a second or not at all.

The specific failure: an unindexed date column

Here is the shape it usually takes. A report filters invoice history on a pick‑date column. That table had four indexes on it — the order number, a history id plus line, the lot, and a rowguid. Every one of those is a perfectly sensible index for a transactional system to ship with. Not one of them helps a query whose WHERE clause filters on pick date.

With no index on the filtered column, SQL Server cannot seek to the rows in your date range. It scans, reading every row in the history table and discarding the ones outside the window. Today was fast because it never touched that table. Month was slow because touching that table meant reading all of it.

Notice what is not wrong here. The query is not badly written. The server is not undersized. The data volume is normal for the business. The only defect is that the access path the report needs does not exist.

How to confirm it in about ten minutes

Establish the split. Run the report at each period setting and record the runtime. If today is sub‑second and week is minutes, you are looking at a threshold, not a gradient. A genuine volume problem degrades smoothly; an access‑path problem falls off a cliff.
Read the actual execution plan for the slow version, not the estimated one. You are looking for a scan on a large table underneath your date filter. That single operator is usually the whole story.
Measure logical reads with SET STATISTICS IO ON. This is the honest number: it does not move with server load, caching or who else is on the box, so it is the one to record before and after. Elapsed time is what the user feels, but logical reads are what you actually changed.
List what is currently indexed on the table, and check whether the filtered column appears as the leading key of anything. Being present somewhere in an index is not the same as being seekable — a column buried as the third key of a composite index will not serve a range scan on its own.

If those four point the same direction, the diagnosis is settled and you have a defensible before‑state to compare against.

The part that needs more care than the diagnosis

Finding it is the easy half. Applying it in a live ERP is where the risk sits, and this is the part most write‑ups skip.

Consideration	Why it matters before you run `CREATE INDEX`
Whose schema is it	If the tables belong to your ERP vendor rather than to you, adding an index is a supportability decision before it is a technical one. Some vendors are relaxed about additive indexes; some treat any schema change as voiding support; some overwrite them on upgrade. Ask before you apply, and record the answer — the next upgrade may silently remove your index and the timeout will come back looking like a new bug.
Which SQL Server edition	Online index operations are an Enterprise feature. On Standard Edition, index creation takes a schema‑modification lock and blocks users on that table for the whole build. On a big history table that is not a few seconds. This belongs in a maintenance window.
Open transactions	An idle query window somewhere holding an open transaction will block the build, and the build will in turn block everyone else. The result looks, from the floor, like the database froze. Close the tabs, and check for open transactions before you start.
Write cost	Every index is maintained on insert, update and delete. On a high‑churn transactional table, an index you added for one monthly report is paid for on every transaction all month. Usually still worth it. Occasionally not.
The reversal plan	Capture the table size, row count and existing index list before you change anything, so the change can be evaluated honestly and dropped cleanly if it does not help.

Designing the index

Lead with the column you filter on. For a date‑range report, that means the date column is the first key — that is what makes the range seekable. Columns you group or join by follow. Columns you only return can go in an INCLUDE list rather than the key, which keeps the index narrower while still avoiding a lookup back to the base table for every row.

Resist the urge to add one index per report. Three overlapping indexes on the same table, each serving one query, cost more in write overhead and buffer pool than one well‑ordered index serving all three. If you already have an index whose leading key is right and it is missing a column or two, extending it is usually better than adding a sibling.

When it genuinely is volume

Sometimes the scan is unavoidable and the answer is architectural rather than an index:

The report aggregates most rows in the table anyway, so there is nothing to skip. A pre‑aggregated snapshot, refreshed nightly, beats recomputing it per page load.
The history table is genuinely enormous and every query hits it. Partitioning by date can help, though it is a much larger commitment than an index.
Several dashboards recompute the same expensive figures independently. Materialise them once on a schedule and have every dashboard read the result.

The tell is the shape of the degradation. A missing index produces a cliff — fine, fine, fine, dead. A true volume problem produces a slope. Diagnose which one you have before choosing a fix, because the architectural remedies cost weeks and the index costs an afternoon and a maintenance window.

The general lesson

“The dashboard is slow” is a symptom report, and it is an accurate one — the user is not wrong. But it describes the experience, not the cause, and the instinct it triggers (optimise the query, buy hardware) points away from the actual defect most of the time.

Ask a narrower question instead: what exactly does the date filter filter on, and does anything index it? In ERP reporting that question resolves a surprising share of performance complaints, usually to one line of DDL and a conversation with whoever owns the schema.