I have below sql query. declare @RoleID int SELECT @RoleID = RoleID FROM RoleMaster WHERE RoleEnglishDesc = 'Surveyor' SELECT * FROM ( SELECT S.ShiftID, 2 AS Flag, ISNULL(U.Latitude, 0) Latitude, ISNULL(U.Longitude, 0) Longitude, U.EmployeeEnglishName AS ToolTipEnglish, U.EmployeeArabicName AS ToolTipArabic, 0 AS CaseStageID, COUNT(C.SurveyorAssigned) AS Assigned -- L.EntryTime AS LastEntryTime -- S1.cnt AS OnBreak FROM Users U INNER JOIN dbo.SurveyorShift S ON S.UserId = U.UserID AND S.ShiftStatus = 1 LEFT JOIN CaseInfoCommandQueue C ON C.SurveyorAssigned = U.UserID WHERE U.IsActive = 1 AND NULLIF(NULLIF(U.Latitude, '0'), '') IS NOT NULL AND NULLIF(NULLIF(U.Longitude, '0'), '') IS NOT NULL AND ((U.RoleID = @RoleID) OR (U.RoleID2 = @RoleID) OR (U.RoleID3 = @RoleID)) GROUP BY U.UserID, RoleID, Latitude, Longitude, EmployeeEnglishName, EmployeeArabicName, ShiftID ) D OUTER APPLY ( SELECT TOP 1 EntryTime FROM DeviceUserLocation DLoc WHERE DLoc.ShiftID = D.ShiftID ORDER BY DLoc.EntryTime DESC ) AS L
My only problem in last outer apply to fetch entry time. Because that outer apply only takes 10 secs. That table DeviceUserLocation also have lacs of entries. Any overcome or step to use top 1 in other way?