Slow query getting max and min date from table based on status

user_5300021 0
Viewed 440

I have the following data where I need to get the min start date and the max enddate based on user and their status. The query worked but it takes more that 55 minutes to run. Is there a way I can write it efficiently? there are about 150,000 users in the table

Data

DECLARE @TBL TABLE (Users INT,  Users_Status VARCHAR(5),    [Start_Date] DATE,  End_Date DATE)
INSERT INTO @TBL VALUES
(1,'A','2019-03-07','2019-03-22'),(1,'A','2019-01-04','2019-01-08'),(1,'A','2019-01-12','2019-01-27'),
(1,'B','2019-01-30','2019-02-02'),(1,'B','2019-02-27','2019-03-13'),(1,'B','2019-01-13','2019-01-24'),
(2,'A','2019-03-15','2019-03-28'),(2,'A','2019-05-19','2019-05-27'),(3,'A','2019-05-31','2019-06-04'),
(3,'A','2019-05-18','2019-06-03'),(3,'A','2019-01-12','2019-01-13'),(3,'A','2019-04-12','2019-05-02'),
(3,'B','2019-01-08','2019-01-18'),(3,'B','2019-04-16','2019-04-18'),(4,'B','2019-05-25','2019-06-03'),
(5,'A','2019-03-26','2019-03-30'),(5,'A','2019-06-13','2019-06-26'),(5,'A','2019-02-02','2019-02-18'),
(5,'B','2019-01-17','2019-01-20'),(5,'B','2019-03-30','2019-04-19'),(5,'B','2019-05-04','2019-05-16'),
(5,'B','2019-03-25','2019-04-10'),(5,'B','2019-03-09','2019-03-27')

I tried this query

;WITH StartEnd AS
(SELECT
*
,ROW_NUMBER()OVER(PARTITION BY  Users,Users_Status   ORDER BY [Start_Date] ASC) AS Utart
,ROW_NUMBER()OVER(PARTITION BY  Users,Users_Status  ORDER BY End_Date DESC) AS UEnd
FROM @TBL
) ,Starts AS
(
SELECT
*
FROM StartEnd
WHERE Utart =1
), 
Ends AS
(
SELECT
*
FROM StartEnd
WHERE UEnd =1
)
SELECT distinct
 S.*
 ,(SELECT MIN(ST.[Start_Date]) FROM Starts ST WHERE ST.Users = S.Users AND ST.Users_Status =S.Users_Status ) AS Min_Start_Date
 ,(SELECT MAX(e.End_Date) FROM Ends E WHERE E.Users = S.Users AND E.Users_Status =S.Users_Status ) AS  Max_end_Date
FROM StartEnd S

Current output

enter image description here

Desired output

enter image description here

tsql sql-server
2 Answers

First thing to do for improving query performance would be to ensure that there are required indexes in place. Try looking at the query execution plan by either:

  1. Clicking on 'Display actual execution plan' button in SSMS before running the query (Ctrl + M shortcut)
  2. View estimated query execution plan in SQL Server Management Studio, by clicking on 'Show estimated execution plan'button in SSMS (Ctrl + L shortcut).

Display estimated execution plan Button in SSMS

Then add any suggested indexes in the execution plan. Suggested indexes will appear in green text. You can right click and select 'Missing index details' to get the create index script in a new window. Modify it as needed before running.

Execution plan in SSMS

user_70123710

I believe your query can be simplified to 

SELECT Users, 
    Users_Status,
    Start_date,
    End_Date,
    MIN(Start_Date) OVER (PARTITION BY Users, Users_Status) Min_Start_Date,
    MAX(End_Date) OVER (PARTITION BY Users, Users_Status) Max_End_Date
FROM @tbl

However, the performance is more likely to be down to indexing

user_71308550
Related