ChrisM@Work (7/22/2014)
-- change the index to support a nested loops join between rs and r-- with seeks to r on id and residual predicate of RNT and Man:
create nonclustered index IX_A on dbo.R
(Id,RNT,Man)
include (M,D)
-- change the non-sargable predicate in your WHERE clause
AND DATEDIFF(DAY, rs.LM, GETDATE()) <= @b-2
-- to something like this:
AND rs.LM > DATEADD(DAY,0-@B,GETDATE())
-- with no sample data to code against, you will have to tweak this yourself.
HI ..thx for your input ..much appreciate it !!
I have 1 heavy CPU time Sproc with high logical read on worktable as below ( any idea how to rewrite the query )
CREATE PROCEDURE [dbo].[GR]
@GN INT
AS
BEGIN
DECLARE @False bit, @True bit
SET @False = 0
SET @True = 1
SELECTR.RId,
iB.*,
CS.ContainerId,
CS.REPosition 'Position',
CS.StopIdStatus,
CS.[Route],
CS.LastConnected,
CS.LastStatusProcessed,
CS.LMR,
R.LT as LastTransactionBatch,
CS.LastStatusCreationTime,
B.RegNumber,
PJN.[Count] [PendingJobNumbers],
IssMarkedDoNotExport.[Count] [IssMarkedDoNotExport],
IssNotApproved.[Count] [IssNotApproved],
IssExported.[Count] [IssExported],
B.IsActive 'IsBActive',
GD.GName,
GD.GId,
G.GCode,
Iss.OutstandingIss,
LatestIsWithJobNumber.JobNumber,
OldestIs.TimeStatusStored 'OldestIs',
LatestInShiftMessage.IsInShift 'IsInShift',
(SELECT COUNT(*) FROM SMDE S INNER JOIN ROS I ON S.REOutstandingIsId = I.Id WHERE I.RId = R.RId AND DoNotExport = 0 AND IsExported = 0 AND Approved = 0) ReadyForApproval,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsOos = 1)
THEN @True ELSE @False END AS PossessOosFault,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsREFault = 1)
THEN @True ELSE @False END AS PossessREFault,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsSoftwareIs = 1)
THEN @True ELSE @False END AS PossessSoftwareIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsRemoteCommsIs = 1)
THEN @True ELSE @False END AS PossessRemoteCommsIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsLocalCommsIs = 1)
THEN @True ELSE @False END AS PossessLocalCommsIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsTimeSyncIs = 1)
THEN @True ELSE @False END AS PossessTimeSyncIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsDataTransferIs = 1)
THEN @True ELSE @False END AS PossessDataTransferIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.IsLocationInfoSuspect = 1)
THEN @True ELSE @False END AS PossessLocationInfoSuspect,
/* From 9459-67043 */
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.BitNo = 0)
THEN @True ELSE @False END AS PossessOysterNotOperationalIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.BitNo = 2)
THEN @True ELSE @False END AS PossessEmvNotOperationalIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.BitNo = 3)
THEN @True ELSE @False END AS PossessAutonomousModeIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.BitNo = 31)
THEN @True ELSE @False END AS PossessInvalidDateTimeIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.BitNo = 58)
THEN @True ELSE @False END AS PossessEmergencyAcceptanceIs,
CASE WHEN EXISTS (SELECT 1 FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN RSSB ON B.BitRef = I.BitRef
WHERE I.RId = R.RId AND B.BitNo = 1)
THEN @True ELSE @False END AS PossessItsoNotOperationalIs,
OldestNotRaisedNotPendingSendableIs.OldestNotRaisedNotPendingSendableIsMins,
DT.DeviceType,
DT.DisplayName [DeviceTypeName]
FROMREs R
LEFT JOINRef_DeviceTypes DT ON DT.DeviceType = R.DeviceType
OUTER APPLY(SELECT TOP 1 *
FROM RECS
WHERE RId = R.RId) CS
OUTER APPLY (SELECT TOP 1 B.*
FROM BCurrentREs BCR
INNER JOIN Bes B ON B.Id = BCR.BId
WHERE BCR.RId = R.RId) B
OUTER APPLY(SELECT TOP 1 *
FROM Gs
WHERE GN = R.GN) G
OUTER APPLY (SELECT TOP 1 *
FROM GDs
WHERE GId = R.GN) GD
OUTER APPLY (SELECT COUNT(*) OutstandingIss
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
WHERE I.RId = R.RId) Iss
OUTER APPLY (SELECT TOP 1 CASE WHEN H.IsSet = 1 THEN CAST(0 AS bit) ELSE CAST(1 AS bit) END AS IsInShift
FROM RSH H
INNER JOIN RSSRSS ON RSS.BitRef = H.BitRef
WHERE H.RId = R.RId
AND RSS.IsInShift = 1
ORDER BY H.UtsDate DESC, H.UtsTime DESC, H.Seconds DESC, H.MessageSequenceNumber DESC) LatestInShiftMessage
OUTER APPLY (SELECT TOP 1 I.*, H.TimeStatusStored
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
WHERE I.RId = R.RId AND I.JobNumber IS NOT NULL
ORDER BY H.TimeStatusStored DESC) LatestIsWithJobNumber
OUTER APPLY (SELECT Count(*) [Count]
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN SMDE X on X.REOutstandingIsId = I.Id
WHERE I.RId = R.RId AND (I.JobNumber IS NULL OR I.JobNumber = '') AND X.Approved = 1) PendingJobNumbers
OUTER APPLY (SELECT TOP 1 DATEDIFF(MI, H.TimeStatusStored, GETDATE()) OldestNotRaisedNotPendingSendableIsMins
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN SMDE X on X.REOutstandingIsId = I.Id
WHERE I.RId = R.RId AND (I.JobNumber IS NULL OR I.JobNumber = '')
AND X.Approved = 0 AND X.DoNotExport = 0 AND LatestIsWithJobNumber.JobNumber IS NULL
AND PendingJobNumbers.[Count] = 0
ORDER BY DATEDIFF(MI, H.TimeStatusStored, GETDATE()) DESC) OldestNotRaisedNotPendingSendableIs
OUTER APPLY (SELECT Count(*) [Count]
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN SMDE X on X.REOutstandingIsId = I.Id
WHERE I.RId = R.RId AND X.DoNotExport = 1) IssMarkedDoNotExport
OUTER APPLY (SELECT Count(*) [Count]
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN SMDE X on X.REOutstandingIsId = I.Id
WHERE I.RId = R.RId AND X.Approved = 0 AND X.DoNotExport = 0) IssNotApproved
OUTER APPLY (SELECT Count(*) [Count]
FROM ROS I
INNER JOIN RSH H ON I.RSHId = H.Id
INNER JOIN SMDE X on X.REOutstandingIsId = I.Id
WHERE I.RId = R.RId AND X.IsExported = 1) IssExported
OUTER APPLY (SELECT TOP 1 iB.[TimeStamp] [iBTimeStamp], iB.TripType, TripType.[Description] [TripTypeDescription]
FROM [iBStatus] iB
INNER JOIN Ref_TripType TripType ON iB.TripType = TripType.Id
WHERE iB.RId = R.RId
ORDER BY iB.TimeStamp DESC) iBDetails
OUTER APPLY (SELECT TOP 1 I.*, H.TimeStatusStored FROM ROS I INNER JOIN RSH H ON I.RSHId = H.Id WHERE I.RId = R.RId ORDER BY H.TimeStatusStored ASC) OldestIs
WHERER.GN = @GN OR (@GN = -1 AND G.GCode IS NULL)
ORDER BY
CASE WHEN OldestIs.TimeStatusStored IS NULL THEN 1 ELSE 0 END,
OldestIs.TimeStatusStored
END