I have a table PaymentItems
with 8 million rows. 100'000 rows have the foreign key PaymentItemGroupId = '662162c6-209c-4594-b081-55b89ce81fda'
.
I have created a nonclustered index on the column PaymentItems.Date
(ASC) in order to be able to sort / find entries faster for dates.
When running the following query, it will take about 3 minutes:
SELECT TOP 10 [p].[Id], [p].[Receivers]
FROM [PaymentItems] AS [p]
WHERE [p].[PaymentItemGroupId] = '662162c6-209c-4594-b081-55b89ce81fda'
ORDER BY [p].[Date]
Interesting is, without the TOP 10 it will take 18 seconds and return all 100'000 rows. When I order descending instead of ascending (ORDER BY [p].[Date] DESC
) it will take about 1 second. When I remove the index, it's also faster when sorting ascending.
I analyzed the query plan for the slow query, and it looks like MS SQL Server does not filter the rows by the foreign key first, but will instead sort all 8 million rows first (Index scan non clustered on Date
index).
In the fast query, it will filter the where conditions first (key lookup clustered).
Is there anything I can do except removing the index for Date
to prevent leading SQL Server into building a bad query plan like this?
Here is the actual query plan: https://www.brentozar.com/pastetheplan/?id=xBBArQl9kh
Here is the create table script:
CREATE TABLE [dbo].[PaymentItems](
[Id] [uniqueidentifier] NOT NULL,
[PaymentItemGroupId] [uniqueidentifier] NOT NULL,
[Date] [datetime2](7) NOT NULL,
CONSTRAINT [PK_PaymentItems] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PaymentItems_Date] ON [dbo].[PaymentItems]
(
[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PaymentItems_PaymentItemGroupId] ON [dbo].[PaymentItems]
(
[PaymentItemGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
For this query to run fast, you would need the following index, which it seems you don't have. This would prevent the key lookup entirely by completely covering the query.
CREATE INDEX IX_PaymentItems_PaymentItemGroupId_Date ON PaymentItems
(PaymentItemGroupId, Date, Id)
INCLUDE (Receivers)
WITH (DROP_EXISTING = ON);
Foreign keys are not indexed by default (even though they should be), and even if they were you'd be missing the Date
key column.