Report takes long time loading

I have a report which is fairly simple but takes a very long time..

It involves the incidents being counted by categories hence it has several Union All.

Also the report numbers are generetd through 2 tables hence within every Union All tehre is a left or an Inner join.

sample code:

SELECT
1 Sort_Order,
COUNT(*) AS Call_Count,
'Incident Resolved at Level 1' AS Count_Type
FROM HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1 T1
INNER JOIN HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM2 T2
ON T1.NUMBERPRGN = T2.NUMBERPRGN
WHERE PROBLEM_STATUS = 'closed'
AND T2.THIRD_ASSIGNEE IS NULL
AND T2.THIRD_ASSIGNMENT IS NULL
AND T1.SECONDARY_ASSIGNEE IS NULL
AND HAL_FIRST_RES='t'
AND DATEPART(mm, DATEADD(hour, -@.offset, CAST(T1.OPEN_TIME AS DATETIME))) = @.MONTH
AND DATEPART(yy, DATEADD(hour, -@.offset, CAST(T1.OPEN_TIME AS DATETIME))) = @.YEAR
AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)

UNION ALL

-- Calls RESOLVED BY L2
SELECT
2 Sort_Order,
COUNT(*) AS Call_Count,
'Incidents Resolved at Level 2 or 3' AS Count_Type
FROM HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1 T1
LEFT JOIN HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM2 T2
ON T1.NUMBERPRGN = T2.NUMBERPRGN
WHERE (HAL_FIRST_RES<>'t' OR HAL_FIRST_RES IS NULL)
AND PROBLEM_STATUS = 'closed'
AND DATEPART(mm, DATEADD(hour, -@.offset, CAST(T1.OPEN_TIME AS DATETIME))) = @.MONTH
AND DATEPART(yy, DATEADD(hour, -@.offset, CAST(T1.OPEN_TIME AS DATETIME))) = @.YEAR
AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)

UNION ALL

could you suggest what might be the reason why teh report churns for so long.

thanks,

kiran.

Hello Kiran,

You might want to do some tuning on the SQL code you are running from the report to try to speed it up.

Jarret

|||

Kiranvukkadala

Do you have indexes on your tables? That would help you out a lot. Also,

AND DATEPART(mm, DATEADD(hour, -@.offset, CAST(T1.OPEN_TIME AS DATETIME))) = @.MONTH
AND DATEPART(yy, DATEADD(hour, -@.offset, CAST(T1.OPEN_TIME AS DATETIME))) = @.YEAR AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)

Are some problem areas, cast and function calls will be problematic if you a returning a large row volumes. I would use a inner join on t1.OTI_Originator to UserID in the HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents table. I don't think you need a sub query here.