How can I use efficient index seek to find the latest row filtered on a small subset of rows?

Question

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?

The table

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?

Answer 1

You can try the following to get a correlated index seek per AccountId.

Rather than a VALUES clause you may prefer to use a temp table or table variable though

SELECT /* TOP (1)*/ T.*
FROM (VALUES (5), (6)) A(AccountId)
CROSS APPLY
(
SELECT TOP 1 *
    FROM [dbo].[Transaction] T
    WHERE T.AccountId = A.AccountId
    ORDER BY
        T.[Timestamp] DESC
) T
/* ORDER BY T.[Timestamp] DESC */

If you wanted the top row from any of the specified accounts rather than each of them as I potentially incorrectly surmised then one way would be to un comment the commented out portions above