3

I have a table that stores transactions for accounts, and I want to select out the latest transaction for a specific account. But the DB (Azure SQL) seems to fetch all transactions for the account, and then do top 1 when I order the result on two columns, and I dont understand why.

My query looks something like this:

SELECT TOP 1 *
    FROM [dbo].[Transaction] T
    WHERE T.AccountId = 4
    ORDER BY
        T.[Timestamp] DESC,
        T.[Created] DESC

and plan: https://www.brentozar.com/pastetheplan/?id=SyF7Y2ew2

If I instead just order on Timestamp it does it quicker:

SELECT TOP 1 *
    FROM [dbo].[Transaction] T
    WHERE T.AccountId = 4
    ORDER BY
        T.[Timestamp] DESC

https://www.brentozar.com/pastetheplan/?id=HJDqFhgD3

The table and index:

CREATE  TABLE [dbo].[Transaction](
    [TransactionId] [UNIQUEIDENTIFIER] NOT NULL PRIMARY KEY,
    [Created] [DATETIME] NOT NULL DEFAULT GETUTCDATE(),
    [Timestamp] [DATETIME] NOT NULL,
    [AccountId] int NULL)
GO
CREATE NONCLUSTERED INDEX [IX_Transaction_AccountId_Timestamp] ON [dbo].[Transaction]([AccountId], [Timestamp] DESC) WITH (ONLINE = ON)
GO

I have simplified the query and table a bit for testing purposes. In my real table I have 10s of millions of rows, with quite a lot of columns not included here, but the behavior regarding the query plan usage is the same.

3
  • Because Created is not part of the index thereby introducing more work (sorting specifically) for the database to do. You can see that directly in the explain plan as the first one includes a Top N Sort node that the latter does not Create an index with that column appended at the end and it should speed things up Jun 9, 2023 at 14:51
  • But its ordering by Timestamp first, so why does it need to also check Created? In my actual query its ordering on 3 columns, where the first (Timestamp) will be enough for more or less 99.99% of cases and only when theres a duplicate its adding the second level ordering.
    – viblo
    Jun 9, 2023 at 14:55
  • Because you've asked the database to do so. Broadly the spirit of your question is why the database isn't doing a semi-streaming sort, eg only doing the in-memory sort for subsets of the results for each Timestamp value. I don't know SQL Server well enough to say, but broadly I know that not all databases support such an operation. Looking forward to seeing what SQL Server experts might say and/or if there is a rewrite that could achieve similar results. That's assuming you won't create the other index though since that would solve the problem too Jun 9, 2023 at 15:07

3 Answers 3

3

The index ordering only enforces that for a particular AccountId the index rows are ordered by Timestamp.

Not by [Timestamp] , [Created]

The TOP N Sort needs to see all the rows for AccountId=4 and keep track of the top 1. (conceptually this is getting the TOP 1 row after all the rows have been sorted by [Timestamp] , [Created] and so the TOP operation does not benefit in any way from any ordering that the rows may arrive in)

You might consider

WITH TopTimestamp AS
(
SELECT TOP 1 WITH TIES *
    FROM [dbo].[Transaction] T
    WHERE T.AccountId = 4
    ORDER BY
        T.[Timestamp] DESC
)
SELECT TOP 1 *
FROM TopTimestamp
ORDER BY [Created] DESC

in order to get the execution plan you want (where the index ordering is used to get the rows with the top timestamp and then the top created is found from that subset) .

enter image description here

I dont understand why

I guess this is just because the product doesn't contain every possible optimisation rule so sometimes writing the query in a different way is needed to get the desired results.

0

(Answering my own question, but I still dont understand why)

The query can be rewritten to a CTE to only do the "expensive" fetch of all transactions with a specific account in case there are several rows with the same value for the first order by column (Timestamp)


WITH cte AS (
    SELECT TOP 1 T.* 
    FROM [dbo].[Transaction] T
    WHERE T.AccountId = 12
    ORDER BY
        T.[Timestamp] DESC

    UNION ALL

    SELECT T.*
        FROM [dbo].[Transaction] T
        INNER JOIN cte 
            ON cte.AccountId = T.AccountId 
                AND cte.TransactionId <> T.TransactionId 
                AND cte.Timestamp = t.Timestamp 
                AND cte.Created < t.Created
)       
SELECT * FROM cte
ORDER BY [timestamp] DESC, [created] DESC
0

your query needs to be ordered by columns, so it needs to have an index on "created" or do a lookup as it does now, so if you want to make this query faster, you need to included this column in your index or you should change it like you did to have it not to order and sort the "created" column. when you ask it to order by both, you may think of it as a set that is needed to be sorted in some places by the second column as well, in your example the ones with close timestamps, do test the inclusion, I think it helps.

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.