1

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

  • At a minimum you'll need to post the table and index DDL. – David Browne - Microsoft Apr 18 at 14:42
  • Is your real question "How to make this query faster?" And you are guessing that the 'sort' is doing more harm than good? – Rick James May 26 at 19:28
1

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
  • Thanks for the answer. Your solution is interesting and it got rid of the sort operator but the query is slower (time) and also more expensive (51% VS 49% of the original query). It makes sense that it's slower because the CTE is returning all the rows, not only where FirstStatus=1. – Francisco Goldenstein Apr 18 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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