I am new to the SQL world and working with the below mentioned query, the table contains 3000000 records. Can you please suggest how to reduce query run time or any other query for the same result.

I tried two queries:

#1

SELECT *
FROM   (SELECT ID,
               Priority,
               Agent_Name,
               Urgency,
               Status,
               Agent_Group_Name,
               Country,
               Region,
               Due_by,
               Type,
               Created_Date,
               Resolved_Date,
               Closed_Date,
               Resolution_Status,
               Requester_Location,
               WH_Region,
               ExecDate,
               Date,
               Full_Date,
               Datatype,
               Department_Name,
               Requester_Emails,
               ROW_NUMBER()
                 OVER (
                   PARTITION BY ID
                   ORDER BY Execdate DESC ) nn
        FROM   weekly_tickets
        WHERE  Created_date >= '2022-01-01 12:00:00 AM') sub_table
WHERE  sub_table.nn = 1 

#2

WITH cte
     AS (SELECT ID,
                Priority,
                Agent_Name,
                Urgency,
                Status,
                Category,
                Item_Category,
                Agent_Group_Name,
                What_is_the_Impact_,
                Country,
                Impact,
                Region,
                Resolution_Time_in_Bhrs,
                Sub_Category,
                Due_by,
                Type,
                Issue_Owner,
                Created_Date,
                Number_of_Users,
                Approval_Status,
                Resolved_Date,
                Closed_Date,
                How_is_the_issue_affecting_the_service_,
                Number_of_Users_staffed,
                Resolution_Status,
                Sites,
                Requester_Location,
                Number_of_Users_affected,
                WH_Region,
                CampaignOriginId,
                ExecDate,
                Date,
                Full_Date,
                AgeEvol,
                Datatype,
                Department_Name,
                Requester_Emails,
                ROW_NUMBER()
                  OVER (
                    PARTITION BY ID
                    ORDER BY Execdate DESC ) nn
         FROM   weekly_tickets
         WHERE  Created_date >= '2022-01-01 12:00:00 AM')
SELECT *
FROM   cte
WHERE  cte.nn = 1 

CodePudding user response：

Always read execution plan or show someone else if it's too complex for you. This query seems pretty obvious to reconstruct with a high degree of certainty. I assume following optimization steps:

1. Scan table and filter by Created_date, return all columns required by next steps (here all columns used by SELECT clause),
2. Order by ID, ExecDate DESC
3. Segment
4. [..]

Filter comes from WHERE clause. Ordering and segmenting comes from OVER clause. I also assume that date filter significantly reduces number of rows returned. Since there is less rows to process, query should perform better.

All that means you should start with the following index:

CREATE INDEX IX_Predicted ON weekly_tickets(Created_Date) INCLUDE (ID,
                Priority,
                Agent_Name,
                Urgency,
                Status,
                Category,
                Item_Category,
                Agent_Group_Name,
                What_is_the_Impact_,
                Country,
                Impact,
                Region,
                Resolution_Time_in_Bhrs,
                Sub_Category,
                Due_by,
                Type,
                Issue_Owner,
                Number_of_Users,
                Approval_Status,
                Resolved_Date,
                Closed_Date,
                How_is_the_issue_affecting_the_service_,
                Number_of_Users_staffed,
                Resolution_Status,
                Sites,
                Requester_Location,
                Number_of_Users_affected,
                WH_Region,
                CampaignOriginId,
                ExecDate,
                Date,
                Full_Date,
                AgeEvol,
                Datatype,
                Department_Name,
                Requester_Emails);

CodePudding user response：

You could create an index to help with performance:

CREATE NONCLUSTERED INDEX index_POC_ticket
ON dbo.weekly_tickets (ID, Execdate DESC)
WITH (DROP_EXISTING = ON);

Edit: added DESC to the Execdate column in the index, since I believe it should support the ordering in your window function.

If ID is the primary key, I don't believe you need to include it in the above index (maybe someone more knowledgeable can comment here).