I have a transaction table with transactions for accounts. I want to fetch the latest transaction for a (small amount of) accounts. If I only filter on one account, the DB will use efficient index seek, but as soon as I ask for the latest transaction of more than one account, it will try to do all at once and be much less efficient.
Can I rewrite the query or do some smarter index?
CREATE TABLE [dbo].[Transaction]( [TransactionId] [UNIQUEIDENTIFIER] NOT NULL PRIMARY KEY, [Created] [DATETIME] NOT NULL DEFAULT GETUTCDATE(), [Timestamp] [DATETIME] NOT NULL, [AccountId] int NOT NULL) GO CREATE NONCLUSTERED INDEX [IX_Transaction_AccountId_Timestamp] ON [dbo].[Transaction]([AccountId], [Timestamp] DESC) WITH (ONLINE = ON) GO
And the query (simplified):
SELECT TOP 1 * FROM [dbo].[Transaction] T WHERE T.AccountId in (5, 6) ORDER BY T.[Timestamp] DESC
Gives this plan https://www.brentozar.com/pastetheplan/?id=BycxPIVvn
But if I just filter on one AccountId, it’s much better
SELECT TOP 1 * FROM [dbo].[Transaction] T WHERE T.AccountId in (5) ORDER BY T.[Timestamp] DESC
Gives this plan: https://www.brentozar.com/pastetheplan/?id=rkOFvINv2
This example is a bit simplified. In my real table, I have 10s of millions of rows and this query is one of the heaviest on the db. Since I know that the number of accounts to filter on will always be small (less than 10, often 1), is there something smarter than a for loop I can do to make this fast?