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.

Report Server Processes

I am fairly new to MS report services. Through Profiler I've found
that the report service is constantly calling procedures in the
ReportServer database; procedures like GetMyRunningJobs and updates to
the Notifications table.
I don't have any scheduled reports. If users need reports, they hit
the report URL and it should be generated on an as-needed basis. As
such I would like to minimize the number of cycles Report Services is
using on my DB server. Is there any way to disable these
polling/notification processes? If so, how? If not, darn.
Thanks in advance.
JimmyYes, you can turn off the subscription capabilities of SRS by setting
the following values to false in RsReportServer.config
<IsSchedulingService>True</IsSchedulingService>
<IsNotificationService>True</IsNotificationService>
<IsEventService>True</IsEventService>
That will still leave you with the check for running jobs, which is needed
to cancel running jobs. In general, all these operations should be no-ops if
you don't have any content, so I don't know whether you will get out by
eliminitating them - my guess you won't notice any difference on your SQL
Server.
Thanks,
Tudor
"Jimmy D" wrote:
> I am fairly new to MS report services. Through Profiler I've found
> that the report service is constantly calling procedures in the
> ReportServer database; procedures like GetMyRunningJobs and updates to
> the Notifications table.
> I don't have any scheduled reports. If users need reports, they hit
> the report URL and it should be generated on an as-needed basis. As
> such I would like to minimize the number of cycles Report Services is
> using on my DB server. Is there any way to disable these
> polling/notification processes? If so, how? If not, darn.
> Thanks in advance.
> Jimmy
>|||Excellent. That is exactly what I was looking for. Thanks very much
Tudor.