Select top 1 field with same table

Question

I have written this query but its executing very slow, We have 8000 of rows and this query takes almost 10 mins to execute. If anyone can help with this query optimization will be grateful.

SELECT v.iID_Event_People, CASE

  WHEN v.sOutlookEventID IS NOT NULL AND v.sOutlookEventID != '' THEN v.sOutlookEventID

  ELSE (

      SELECT TOP 1 sOutlookEventID 

      FROM dbo.Events 

       WHERE sGroupingID = v.sGroupingID AND sOutlookEventID IS NOT NULL AND sOutlookEventID != ''

   )

END AS sOutlookEventID  

FROM     dbo.Events AS v  

Answer 1

Try this code

SELECT 
	v.iID_Event_People, 
	IIF(v.sOutlookEventID IS NOT NULL AND v.sOutlookEventID != '', v.sOutlookEventID, N.sOutlookEventID) AS sOutlookEventID
FROM [#events] AS v
LEFT JOIN
(
	SELECT MIN(sOutlookEventID) AS sOutlookEventID, sGroupingID FROM [#events]
	WHERE sOutlookEventID IS NOT NULL AND sOutlookEventID != ''
	GROUP BY sGroupingID
)N
ON v.sGroupingID = N.sGroupingID

And you'b better add a nonclustered index on sGroupingID and sOutlookEventID

Answer 2

Hi @Udal Bharti

How about this query:

SELECT iID_Event_People
	  ,CASE WHEN sOutlookEventID IS NULL OR sOutlookEventID = ''
	        THEN MAX(sOutlookEventID)OVER(PARTITION BY sGroupingID) 
	        ELSE sOutlookEventID END AS sOutlookEventID
FROM dbo.Events

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".