I have a SQL Server table containing approximately 15 million rows. A query using WHERE, JOIN, and ORDER BY takes more than two minutes to return about 10,000 rows. The query works correctly, but performance is very slow. SELECT c.CustomerName, o.OrderDate, o.TotalAmount FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID WHERE o.OrderDate >= '2026-01-01' ORDER BY o.OrderDate DESC;

SQL Server query takes more than 2 minutes to return 10,000 rows

Partial answer / troubleshooting steps — no confirmed fix yet from the discussion, but here's what to check first:

Likely cause: Missing (or unused) index on Orders.OrderDate, forcing a full table/clustered index scan over 15M rows before the filter and sort can be applied.

What to check/try, in order:

Look at the execution plan (Ctrl+M in SSMS, or SET SHOWPLAN_XML ON) to confirm whether SQL Server is doing a Table/Clustered Index Scan on Orders. If so, that's your bottleneck.
Add an index that supports the WHERE + ORDER BY + JOIN, e.g.:

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
ON Orders (OrderDate DESC)
INCLUDE (CustomerID, TotalAmount);

This lets SQL Server seek directly on OrderDate, get the rows already in the needed sort order, and cover the CustomerID/TotalAmount columns without a lookup.

Make sure Customers.CustomerID is indexed (usually it is, as PK) so the join is a seek, not a scan.
Update statistics on both tables (UPDATE STATISTICS Orders; UPDATE STATISTICS Customers;) in case stale stats are causing a bad plan.
Check for parameter sniffing / cached bad plan — try running with OPTION (RECOMPILE) to see if a fresh plan performs better.
If the table is very wide or has many indexes already, consider whether fragmentation on the existing indexes is a factor (ALTER INDEX ... REORGANIZE/REBUILD).

Not yet established: whether an index already exists on OrderDate, table size/row width, or actual execution plan output — those details would help confirm which of the above is the actual culprit. If you can share the execution plan (or at least confirm if OrderDate has an index), that would narrow this down further.

1 Answer