SQL Query Performance Issue

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

August 24th, 2015 4:45pm

N_14 ,

/*
Please consider doing the below items to see some gain, but its really hard to troubleshoot without actually knowing the purpose of the proc. 
All we can do is just re-format and post it back but that's not what you want.

CI on MessageID,  NCI on InputStateNo, OutputStateNo for Table - MessageAudit
CI on MessageID, NCI on auditcodename, created for Table - MessageAuditTimes
CI on MessageStateNo, NCI on MessageStateName for Table - MessageState
*/

Free Windows Admin Tool Kit Click here and download it now
August 24th, 2015 6:20pm

The below are used twice.. any optimization we can do .. any ideas..

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 

August 26th, 2015 8:09pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics