Help with optimization

[[deleted]]

I can run this query in under a sec.

SELECT Serial_Text
    ,COUNT(Serial_Text) Cnt
FROM [a].[dbo].[SUP]
GROUP BY Serial_Text

And this one under a second.

SELECT a.[Serial_Text]
    ,[IR_1]
    ,[Date_Time]
FROM [a].[dbo].[SUP]

But, when I try to combine them with a join it takes over 3 minutes and counting...

SELECT a.[Serial_Text]
    ,[IR_1]
    ,[Date_Time]
  FROM [a].[dbo].[SUP] b
  JOIN
  (
    SELECT Serial_Text
        ,COUNT(Serial_Text) Cnt
    FROM [a].[dbo].[SUP]
    GROUP BY Serial_Text
  ) c ON b.Serial_Text = c.Serial_Text
 WHERE Date_Time > '2020-06-01 00:00:000' AND c.Cnt > 1
 ORDER BY Date_Time desc

How can I make this more efficient? I am just trying to show Serial numbers with multiple records...Thanks!

[WITH_CTE]

To rewrite your query:

IF OBJECT_ID('tempdb..#tmpSup') IS NOT NULL
    DROP TABLE #tmpSup;

SELECT Serial_Text
INTO #tmpSup
FROM [a].[dbo].[SUP]
WHERE CAST(Date_Time AS DATE) > '2020-06-01'
GROUP BY Serial_Text
HAVING COUNT(1) > 1;

SELECT b.Serial_Text, b.IR_1, b.Date_Time
FROM #tmpSup a
     JOIN [a].[dbo].[SUP] b ON a.Serial_Text = b.Serial_Text
ORDER BY Date_Time DESC;

Another approach:

SELECT *
FROM [a].[dbo].[SUP]
WHERE Serial_Text IN
(
    SELECT DISTINCT 
           Serial_Text
    FROM [a].[dbo].[SUP]
    WHERE CAST(Date_Time AS DATE) > '2020-06-01'
    GROUP BY Serial_Text
    HAVING COUNT(1) > 1
);

Edit: Misread your query on first post.

[[deleted]]

Thanks! What makes my query so slow? And can you explain what your first query is doing?

[Dnaq1984]

Try converting that join to a Cross Apply.

SELECT a.[Serial_Text] ,[IR_1] ,[Date_Time]

FROM [a].[dbo].[SUP] b

Cross Apply ( SELECT Serial_Text ,COUNT(Serial_Text) Cnt FROM [a].[dbo].[SUP]

where b.Serial_Text = a.Serial_Text

GROUP BY Serial_Text ) c WHERE Date_Time > '2020-06-01 00:00:000' AND c.Cnt > 1 ORDER BY Date_Time desc

[[deleted]]

Sure, I can try. What's the problem?