Fixing slow report performance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mtnbiker419 - 24 Jun 2008 21:46 GMT

We are experiencing extremely slow performance on our reporting
server
with only one report. There are currently about 30 reports hosted on
the server, the vast majority of which will load within a few seconds
of being selected; however, one report on average takes about 5
minutes to load. Previously, when first added to the server several
months ago, the report performed just fine, but in recent weeks the
load time has signifcantly degraded to current levels.

Any help would be greatly appreciated!

Reply to this Message

Bruce L-C [MVP] - 24 Jun 2008 22:31 GMT

Additional information would be of help.

Is the report based on a stored procedure?

Are you viewing the report via Report Manager or are you using your own
front end?

What format (if Report Manager it is HTML if your own front end it could be
any format).

What version of RS?

Signature

Bruce Loehle-Conger
MVP SQL Server Reporting Services

> We are experiencing extremely slow performance on our reporting
> server
[quoted text clipped - 6 lines]
>
> Any help would be greatly appreciated!

Reply to this Message

mtnbiker419 - 30 Jun 2008 16:35 GMT

We are using SQL Server 2005 Reporting Services and reviewing the
report via Report Manager.

I have pasted the query for the report in below. It seems that
whenever we remove the subquery in the "WHERE" statement, the report
runs much faster (although doesn't bring back the appropriate data).
Is this something with our query or with the database itself? The
database we are pulling out of is Microsoft Dynamics CRM 3.0.

SELECT FilteredNew_Projectv2.new_activependinginitiativename,
FilteredNew_Projectv2.new_assignedreources,
FilteredNew_Projectv2.new_clientpocidname,
FilteredNew_Projectv2.new_duedate,
FilteredNew_Projectv2.new_duedateutc,
FilteredNew_Projectv2.new_inscopedeliverablename,
FilteredNew_Projectv2.new_inscopepracticeidname,
FilteredNew_Projectv2.new_name,
FilteredNew_Projectv2.new_parentaccountidname,
FilteredNew_Projectv2.new_pmopoc,
FilteredNew_Projectv2.new_projectbudget,
FilteredNew_Projectv2.new_projectdescription,
FilteredNew_Projectv2.new_projectplanname,
FilteredNew_Projectv2.new_projectriskname,
FilteredNew_Projectv2.new_projecttypename,
FilteredNew_Projectv2.new_projectwebsite,
FilteredNew_Projectv2.new_startdate,
FilteredNew_Projectv2.new_startdateutc,

FilteredNew_Projectv2.new_statusreportfrequencyname,
FilteredNew_Projectv2.new_statusreportsrequiredname,

FilteredNew_Projectv2.new_timesheetsrequiredname,
FilteredNew_Projectv2.owneridname, FilteredNew_Projectv2.owneridtype,
FilteredNew_Projectv2.statecode,
FilteredNew_Projectv2.statecodename, FilteredAnnotation.notetext,
FilteredAnnotation.createdon,
FilteredNew_Projectv2.statuscode,
FilteredNew_Projectv2.statuscodename,
FilteredAccount.new_accountriskstatusname
FROM FilteredNew_Projectv2 INNER JOIN
FilteredAnnotation ON
FilteredNew_Projectv2.new_projectv2id = FilteredAnnotation.objectid
INNER JOIN
FilteredAccount ON
FilteredNew_Projectv2.new_parentaccountid = FilteredAccount.accountid
WHERE (FilteredNew_Projectv2.new_activependinginitiative = 1) AND
(FilteredAnnotation.createdon =
(SELECT
MAX(FilteredAnnotation_1.createdon) AS Expr1
FROM FilteredNew_Projectv2 AS
FilteredNew_Projectv2_1 CROSS JOIN
FilteredAnnotation
AS FilteredAnnotation_1
WHERE (FilteredAnnotation_1.objectid
= FilteredAnnotation.objectid))) AND
(FilteredNew_Projectv2.new_inscopepracticeidname
IN (@InScopePractice)) AND (FilteredNew_Projectv2.statuscode = 1)

Reply to this Message