Slow stored procedures are one of the most common and most misdiagnosed problems we are called in to fix. The instinct is often to throw more hardware at the database or to rewrite the whole procedure. In most cases neither is necessary. On a recent engagement we cut a reporting procedure from over 15 minutes to roughly 30 seconds — a 30x improvement — by adding two carefully chosen indexes and nothing else.
The symptom
The procedure joined two large tables — an order-header table and an order-line table — filtered by a date range and a status, then aggregated the results. As the tables grew past a few million rows, execution time climbed from acceptable to unusable. Users had learned to run the report and walk away for a coffee. That is a productivity tax that compounds every single day.
Finding the real bottleneck
We never optimise on a hunch. The first step was to capture the actual execution plan and the I/O statistics:
SET STATISTICS IO, TIME ON;
-- run the procedure with a representative parameter set
The plan told the whole story. SQL Server was performing a Clustered Index Scan on both tables — reading every row — followed by an expensive hash join. The SET STATISTICS IO output showed millions of logical reads per execution. The optimiser had no useful index to seek into, so it read everything, every time.
The fix: two covering indexes
The tables were being filtered on date and status, and joined on the order id. So we built a composite index on the header table that matched the filter, with the join and output columns as included columns, and a matching index on the line table keyed on the foreign key:
CREATE NONCLUSTERED INDEX IX_OrderHeader_Status_Date
ON dbo.OrderHeader (Status, OrderDate)
INCLUDE (OrderId, CustomerId, TotalAmount);
CREATE NONCLUSTERED INDEX IX_OrderLine_OrderId
ON dbo.OrderLine (OrderId)
INCLUDE (Sku, Quantity, LineTotal);
Two principles drove the design:
- Key order matches the query. The most selective equality predicate (
Status) comes first, followed by the range predicate (OrderDate). This lets the engine seek directly to the qualifying rows instead of scanning. - Cover the query. By
INCLUDE-ing the columns the procedure actually returns, the engine never has to jump back to the base table with a key lookup. The index alone satisfies the query.
The result
After the indexes were created and statistics updated, the execution plan changed from a full scan to an Index Seek on both tables. The numbers:
- Execution time: ~15 minutes → ~30 seconds
- Logical reads: reduced by well over 95%
- Zero changes to the application code or the procedure logic
The report that people used to schedule around now returns before they have finished reading the request.
What to take away
Indexing is not about adding indexes everywhere — that slows down writes and wastes storage. It is about reading the execution plan, understanding which predicates the query filters and joins on, and designing a small number of precise indexes that let the engine seek instead of scan. Two right indexes beat twenty wrong ones.
If you have a stored procedure or report that has quietly gotten slower as your data grew, it is very often fixable without new hardware and without a rewrite. We do this kind of database performance work regularly across logistics, 3PL, and e-commerce systems — get in touch if a slow query is costing your team time every day.