Processing Time Perfomance

First, a general question. What is a "reasonable" time to expect the
ProcessingTime to take. Now I know that is pretty vague and largely depends
on the grouping/filtering defined in the RDL.
Our problem. We have a several main reports that each embed 7-10 subreports
from a pool of 15 or so defined subreports. This was primarily designed this
way for the flexibility and ease of maintenance of selecting/moving around
the subreports in each main report.
I understand that a main report executing subreports cannot take advantage
of multi-threading the queries and instead run each sequentially, correct?
Even so, we have tuned the database very well and the data retrieval (from
execution log) is taking about 2 seconds for about 15 queries in tables with
millions of rows. Incidentally we are using a custom DPE.
Highlevel details of each subreport. I can provide the RDLs, if necessary:
-Each subreport is one page (8.5x11) long and contains a pie chart and bar
chart data region and a table data region.
-The dataset for the table, pie and bar are the same, but the table and pie
do an aditional grouping on a string value on an average of 10-100 rows.
-Two other data regions of about 10 rows exist, but do not do any grouping.
-One of the subreports does do filtering on a range of 10 to potentially
~5000 rows, but is slow in processing even with 10 rows.
-Last, in the table dataregion (which is never bigger than 15 rows after
grouping), an external assembly is called to do some minor formatting for
each row (font, navigation, etc.)
The problem is that the processing time is taking near a minute -- which is
unacceptable given our application is online and the business expectation is
to take about 5 seconds, no more than 10. Cached reports are super-fast, but
the reports are such that it is unlikely that a cache would be used.
So, what can we do to improve perfomance? It does not appear that moving
the subreports into one report will gain us much, even with mult-threaded
queries, as our database is tuned to the hilt already.
-We tried disabling the caching (thinking perhaps the time to write the
cache files was expensive), but no. By the way, does the time to write the
cache fall into "processing time"?
-We could potentially break the data in two (one for the pie and table and
one for the bar) and omit the grouping.
-We could also return two datasets for the subreport that is doing
filtering.
-Our ReportServer and SQLReportCatalog are on the same server (SQL Standard)
and the server is pretty beefy (2 proc 2GHz). I assume nothing in the
processing part is multi-threaded, so one one proc gets used, right? Are
there any config settings to improve performance?
I want to avoid changing around the RDL to remove all filtering and grouping
if it will only shave a few seconds of the minute anyhow.
Thoughts, suggestions requested. Thanks, DavidMy suggestion is to run each subreport individually and see what time you
get for them. See if a particular subreport is giving you the problem. Since
your data is not large for each subreport (ignoring the one that could have
5,000 since you said having smaller data is still a problem) it seems to me
that you should be doing substantially better here than you are.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David Swanson" <DavidSwansonSDG@.newsgroup.nospam> wrote in message
news:4511C346-8482-42D1-9192-1BE85B9089B5@.microsoft.com...
> First, a general question. What is a "reasonable" time to expect the
> ProcessingTime to take. Now I know that is pretty vague and largely
depends
> on the grouping/filtering defined in the RDL.
> Our problem. We have a several main reports that each embed 7-10
subreports
> from a pool of 15 or so defined subreports. This was primarily designed
this
> way for the flexibility and ease of maintenance of selecting/moving around
> the subreports in each main report.
> I understand that a main report executing subreports cannot take advantage
> of multi-threading the queries and instead run each sequentially, correct?
> Even so, we have tuned the database very well and the data retrieval (from
> execution log) is taking about 2 seconds for about 15 queries in tables
with
> millions of rows. Incidentally we are using a custom DPE.
> Highlevel details of each subreport. I can provide the RDLs, if
necessary:
> -Each subreport is one page (8.5x11) long and contains a pie chart and bar
> chart data region and a table data region.
> -The dataset for the table, pie and bar are the same, but the table and
pie
> do an aditional grouping on a string value on an average of 10-100 rows.
> -Two other data regions of about 10 rows exist, but do not do any
grouping.
> -One of the subreports does do filtering on a range of 10 to potentially
> ~5000 rows, but is slow in processing even with 10 rows.
> -Last, in the table dataregion (which is never bigger than 15 rows after
> grouping), an external assembly is called to do some minor formatting for
> each row (font, navigation, etc.)
> The problem is that the processing time is taking near a minute -- which
is
> unacceptable given our application is online and the business expectation
is
> to take about 5 seconds, no more than 10. Cached reports are super-fast,
but
> the reports are such that it is unlikely that a cache would be used.
> So, what can we do to improve perfomance? It does not appear that moving
> the subreports into one report will gain us much, even with mult-threaded
> queries, as our database is tuned to the hilt already.
> -We tried disabling the caching (thinking perhaps the time to write the
> cache files was expensive), but no. By the way, does the time to write
the
> cache fall into "processing time"?
> -We could potentially break the data in two (one for the pie and table and
> one for the bar) and omit the grouping.
> -We could also return two datasets for the subreport that is doing
> filtering.
> -Our ReportServer and SQLReportCatalog are on the same server (SQL
Standard)
> and the server is pretty beefy (2 proc 2GHz). I assume nothing in the
> processing part is multi-threaded, so one one proc gets used, right? Are
> there any config settings to improve performance?
> I want to avoid changing around the RDL to remove all filtering and
grouping
> if it will only shave a few seconds of the minute anyhow.
> Thoughts, suggestions requested. Thanks, David