Slow Performance: Query, Database, or Other?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mtnbiker419 - 30 Jun 2008 16:39 GMT

I am using SQL Server 2005 Reporting Services and viwing a couple of
reports via the Report Manager. Each report runs off of the Microsoft
Dynamics CRM 3.0 database, and takes 4 to 5 minutes to load. In
examining the reports, I noticed that if I removed the subquery from
the "WHERE" statement of the queries, the report would run at what
should be normal speeds (albeit not bring back the correct data). I
have pasted the queries in for the reports in below.

Any assistance would be greatly appreciated!

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)

SELECT FilteredNew_Projectv2.new_inscopedeliverablename,
FilteredNew_Projectv2.new_name,
FilteredNew_Projectv2.new_parentaccountidname,
FilteredNew_Projectv2.new_pmopoc,
FilteredNew_Projectv2.new_projectriskname,
FilteredAnnotation.notetext, FilteredAnnotation.createdon,
FilteredNew_Projectv2.statuscode,
FilteredNew_Projectv2.statuscodename,
FilteredAccount.new_accountriskstatusname,
FilteredAccount.owneridname,
FilteredNew_Projectv2.new_parentaccountid, FilteredAccount.accountid,
FilteredAccount.name,
FilteredNew_Projectv2.new_projectv2id,
FilteredNew_Projectv2.new_inscopepracticeidname
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.statuscode
<> 2) AND
(FilteredNew_Projectv2.new_inscopepracticeidname
IN (@OperationsParam))

Reply to this Message

Bruce L-C [MVP] - 30 Jun 2008 18:16 GMT

I suggest taking the query and in SQL Server Management Studio get a query
plan. Look for anyplace that is doing a table scan. Unless the table is very
small (say, under a 1,000 rows) then you should not see a table scan. That
is a very good indication that you need some additional indexes. Also,
another thing you can try is to put the subquery data into a temp table and
then do a join instead of using the IN clause.

Signature

Bruce Loehle-Conger
MVP SQL Server Reporting Services

>I am using SQL Server 2005 Reporting Services and viwing a couple of
> reports via the Report Manager. Each report runs off of the Microsoft
[quoted text clipped - 88 lines]
> (FilteredNew_Projectv2.new_inscopepracticeidname
> IN (@OperationsParam))

Reply to this Message