Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Query for only one table - takes too much time

Author  Topic 

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-03-19 : 04:52:35
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?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-19 : 05:47:24
change OUTER APPLY to JOIN... see the results
SELECT * 
FROM (SELECT ....
) D
JOIN (SELECT shiftId, EntryTime, ROW_NUMBER() OVER(PARTITION BY ShiftId ORDER BY EntryTime DESC) Seq FROM DeviceUserLocation) DLoc
ON DLoc.ShiftID = D.ShiftID AND Seq=1


Let us know the results

--
Chandu
Go to Top of Page

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-03-19 : 06:03:15
Not much change after applying join. I applied some indexing on table made it working fast.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-19 : 06:14:20
okay....


--
Chandu
Go to Top of Page
   

- Advertisement -