Processing is very slow and rendering report in PDF is very slow. Need suggestions and a s

Hi,
I am trying to re-engineer a report that was originally developed in
Crystal 7 into SQL Server 2000 Reporting Services. The reason for this
is that we are moving to a new infrastructure that is running Windows
2003 Server. Crystal 7 is unsupported and it won't even install.
The report has one parameter: a string variable that can be up to 15
characters long.
The original report in Crystal 7 had two subreports each with a
grouping level linked to a master report with three grouping levels.
The first cut of this report in SQL Server 2000 Reporting Services is
producing the correct amounts. But processing and rendering this
report in PDF (this would be the preferred method) is taking forever.
I have no sub reports in this first design. I embedded sub-tables (two
with one group level each) with another table (with three grouping
levels). There are embedded lists inside these tables. I merged all
the rows together and embedded a list inside to position elements in
the report. Even rendering a row of data is taking too long.
Could I send this report (RDL file) to someone at Microsoft to look at
the design and give me suggestions on what to try next?
I am thinking on removing the groups. I just don't know how I would
link the master dataset with another dataset that is grouping the data
based on the parameter sent.
I have a real short time frame to get this to work and I am not finding
a lot of documentation on performance improvements. Once I correspond
with a Microsoft expert, I could discuss the design, data amounts, and
execution/rendering time frames.Do you have SP1 installed. There were significant improvements in PDF
performance with SP1.
To check version go to http://yourserver/Reportserver
It should be version 8.00.878.00.
If not, that is the first thing I would do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mossman" <tmosson1@.sbcglobal.net> wrote in message
news:1107912663.898526.219740@.g14g2000cwa.googlegroups.com...
> Hi,
> I am trying to re-engineer a report that was originally developed in
> Crystal 7 into SQL Server 2000 Reporting Services. The reason for this
> is that we are moving to a new infrastructure that is running Windows
> 2003 Server. Crystal 7 is unsupported and it won't even install.
> The report has one parameter: a string variable that can be up to 15
> characters long.
> The original report in Crystal 7 had two subreports each with a
> grouping level linked to a master report with three grouping levels.
> The first cut of this report in SQL Server 2000 Reporting Services is
> producing the correct amounts. But processing and rendering this
> report in PDF (this would be the preferred method) is taking forever.
> I have no sub reports in this first design. I embedded sub-tables (two
> with one group level each) with another table (with three grouping
> levels). There are embedded lists inside these tables. I merged all
> the rows together and embedded a list inside to position elements in
> the report. Even rendering a row of data is taking too long.
> Could I send this report (RDL file) to someone at Microsoft to look at
> the design and give me suggestions on what to try next?
> I am thinking on removing the groups. I just don't know how I would
> link the master dataset with another dataset that is grouping the data
> based on the parameter sent.
> I have a real short time frame to get this to work and I am not finding
> a lot of documentation on performance improvements. Once I correspond
> with a Microsoft expert, I could discuss the design, data amounts, and
> execution/rendering time frames.
>|||Yes, we have SP1 installed. Maximum amount of data brought back by a
parameter value is 20698. I run the query in query analyzer based on
the parameter value and it takes up to 24 secs. It seems I am having
problems when the record count is around 1000 or greater. Need to keep
the structure of the report the same as it was in Crystal but I need to
speed up the processing. I could look into re-engineering the query as
well. We have the RSExecutionLog process setup. Looking at the data
it seems a lot time is spent processing and rendering. By the way, the
TimeDataRetrieval, TimeProcessing, and TimeRendering attributes, is the
numeric data in milliseconds?|||Yes, we have SP1 installed. Maximum amount of data brought back by a
parameter value is 20698. I run the query in query analyzer based on
the parameter value and it takes up to 24 secs. It seems I am having
problems when the record count is around 1000 or greater. Need to keep
the structure of the report the same as it was in Crystal but I need to
speed up the processing. I could look into re-engineering the query as
well. We have the RSExecutionLog process setup. Looking at the data
it seems a lot time is spent processing and rendering. By the way, the
TimeDataRetrieval, TimeProcessing, and TimeRendering attributes in the
ExecutionLogs table in the RSExecutionLog DB, is the numeric data in
milliseconds?|||This is a 500 plus page report if I understand correctly. RS does all this
in memory and when you have grouping it definitely takes longer. As an
experiment I would try removing all groups (don't worry about what it looks
like, just see if this speeds it up substantially). If there is any work you
could put into a stored procedure I would do that as well. But, the real
issue could just be the shear amount of processing you are requiring to
render this. My philosophy is that reports are for humans to read. No human
is going to sit down and read 500 pages. RS is great if you want to create
reports that someone is going to use and look at. It is not good for large
number of pages (I've seen someone with a 1,000 page report as a
requirement). Where it shines is having on-line reports. I provide summary
reports and then the user does drill throughs to get to more detail about
the information they need. If you can I would look at the requirements and
see if it could be designed a different way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mossman" <tmosson1@.sbcglobal.net> wrote in message
news:1107977412.708510.39290@.g14g2000cwa.googlegroups.com...
> Yes, we have SP1 installed. Maximum amount of data brought back by a
> parameter value is 20698. I run the query in query analyzer based on
> the parameter value and it takes up to 24 secs. It seems I am having
> problems when the record count is around 1000 or greater. Need to keep
> the structure of the report the same as it was in Crystal but I need to
> speed up the processing. I could look into re-engineering the query as
> well. We have the RSExecutionLog process setup. Looking at the data
> it seems a lot time is spent processing and rendering. By the way, the
> TimeDataRetrieval, TimeProcessing, and TimeRendering attributes in the
> ExecutionLogs table in the RSExecutionLog DB, is the numeric data in
> milliseconds?
>