SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Query for only one table - takes too much time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 03/19/2013 :  04:52:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/19/2013 :  05:47:24  Show Profile  Reply with Quote
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

Edited by - bandi on 03/19/2013 05:48:09
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

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

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/19/2013 :  06:14:20  Show Profile  Reply with Quote
okay....


--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000