2

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

Enter image description here

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

enter image description here

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?

0

1 Answer 1

4

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 */

enter image description here

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.