Hi,
I have below SQL in my SP, it having performance issue now. can you help if any modification can be dome to improve performance.
declare @startInSysTime datetime, @endInSysTime datetime
declare @startDateInSysTime date, @endDateInSysTime date
declare @startInSysTimeYMD int, @endInSysTimeYMD int
SELECT @startInSysTime=dbo.fnSiteTimeToSystemTime(@startDate), @endInSysTime=dbo.fnSiteTimeToSystemTime(@endDate)
SET @startDateInSysTime = CAST(@startInSysTime AS DATE)
SET @endDateInSysTime = CAST(@endInSysTime AS DATE)
declare @In int, @EscIn int, @EscOut int, @Processed int, @ManClosed int, @AutoClosed int
SelecT
@In = SUM(case when @skillID = 26 and (InputStateNo = 265 and OutputStateNo = 777) then 1 else 0 end)
FROM Message m
inner join MessageAudit MA ON M.MessageID = MA.MessageID
and InputStateNo = 265 and OutputStateNo = 777
INNER JOIN MessageAuditTimes mat ON m.MessageID = mat.MessageID
AND mat.auditcodename in ('ParcelTrackingHandler.ParcelTrackingHandler')
AND mat.created >= @startDateInSysTime AND mat.created < @endDateInSysTime
INNER JOIN MessageState ms on ms.MessageStateNo=m.MessageStateNo
AND ms.MessageStateName NOT IN ('OutboundDraft','NewOutboundMessage', 'MessageDeleted')
SELECT
@EscIn = SUM(case when mat.auditcodename='Message Escalated To Skill' and IntRef2 = @skillID and ISNULL(IntRef1,0) <> @skillid then 1 else 0 end),
@ManClosed = SUM(case when mat.auditcodename='Message Closed' then 1 else 0 end),
@AutoClosed = SUM(case when mat.auditcodename='Message.AutoClosed' then 1 else 0 end)
FROM Message m
INNER JOIN MessageAuditTimes mat ON m.MessageID = mat.MessageID
AND mat.auditcodename in ('Skill Identified','Message Escalated To Skill','Message Closed','Message.AutoClosed')
AND mat.created >= @startDateInSysTime AND mat.created < @endDateInSysTime
INNER JOIN MessageState ms on ms.MessageStateNo=m.MessageStateNo
AND ms.MessageStateName NOT IN ('OutboundDraft','NewOutboundMessage', 'MessageDeleted')
WHERE m.SkillID = @skillid
--WHERE m.Created > @startDateInSysTime
SELECT @EscOut = (SELECT COUNT(*)
FROM messageaudittimes
WHERE
auditcodename='Message Escalated To Skill'
AND IntRef1 = @skillid
AND IntRef2 <> @skillid
AND created >= @startDateInSysTime AND created < @endDateInSysTime
)
SELECT @Processed = @ManClosed --+ @EscIn
SELECT
@In AS 'In', @EscIn AS 'EscIn',@EscOut AS 'EscOut', @Processed AS 'Processed', @ManClosed AS 'ManClosed',@AutoClosed AS 'AutoClosed'
Nav