Azure SQL Server – Cannot get rid of the sort operator

azure-sql-databaseindex-tuningsort-operator

I'm using Azure SQL Server (Microsoft SQL Azure (RTM) – 12.0.2000.8 Apr 3 2019 13:27:00 Copyright (C) 2019 Microsoft Corporation), Premium tier 4 (500 DTUs).

Query: offer progress has a history of statuses for each offer and I want to get the first row for each offer/status. So, for offer #1 I want the first time it was set to status 1, status 2, etc.

        SELECT OfferProgressId, OfferId, DateCreated, OfferStatusDate, OfferStatusId, Name  
        FROM   
        (  
            SELECT   
                op.OfferProgressId, op.OfferId, op.DateCreated, op.OfferStatusDate, op.OfferStatusId,  u.Name,  
                ROW_NUMBER() OVER(PARTITION BY op.OfferId, op.OfferStatusId ORDER BY op.DateCreated) AS FirstStatus  
            FROM OfferProgress op   
            LEFT JOIN [User] u ON u.UserId = op.UserId  
            WHERE   
                op.OfferStatusId IN (3, 5, 6, 7, 8, 9, 16, 17) AND 
                op.DateCreated >= '8/1/2017' 
        ) t   
        WHERE   
            t.FirstStatus=1

Execution plan: https://www.brentozar.com/pastetheplan/?id=rkhP–LcN

My goal is to get rid of the sort operator that has a warning.

My indexes: IX_OfferProgress_DateCreated, IX_OfferProgress_OfferId_OfferStatusId_DateCreated, IX_OfferProgress_OfferStatusId_DateCreated_OfferId_UserId_with_include

Best Answer

I don't know if you are going to be able to eliminate the sort operator, the use of ROW_NUMBER necessitates a sort operation.

But you may be able to reduce the total cost of your query by using something similar. 

;WITH CTE_Initial AS
    (
    SELECT op.OfferProgressId
        , ROW_NUMBER() OVER(PARTITION BY op.OfferId, op.OfferStatusId ORDER BY op.DateCreated) AS FirstStatus
    FROM OfferProgress AS op
    WHERE op.OfferStatusId IN (3, 5, 6, 7, 8, 9, 16, 17) 
        AND op.DateCreated >= '8/1/2017' 
    )
SELECT op.OfferProgressId, op.OfferId, op.DateCreated, op.OfferStatusDate, op.OfferStatusId,  u.Name
FROM CTE_Initial AS I
    INNER JOIN OfferProgres AS op ON op.OfferProgressId = I.OfferProgressId
    LEFT OUTER JOIN [User] AS u ON u.UserID = op.UserID
WHERE I.FirstStatus = 1
Related Question