Hi,

We have reports with dynamic sql involving subqueries,cte,a single parameter
containing the whole where clause etc in the dataset. Then in the layout we
have table with drilldowns to 4 levels and the data will appear in 5th level
without any further drill downs. Also we are forced to include various
conditional visibility,sorting and rowcalculations expressions. Performance
is very poor. What is the best way to improve performance? stored procedures
does not help me much as the sql contains a single where clause parameter and
hence a total dynamic sql.

My guess is that with drill downs to 5 levels that you have a lot of rows
being returned. Most likely that is the issue with performance, the number
of rows being rendered. Also, just to be sure, are you rendering it as HTML?
HTML is the fastest way to render.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Hi ringt,

Thank you for your post. And thanks Bruce's suggestion.

Performance issue will be related to a lot of things. The key point is to
reduce the dataset size. I would like to know the amount of the records
dataset returned. And if you does not use the drill down control, does this
performance improve?

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hello,

My dataset is returning 10165 rows currently. When I checked the
ExecutionLog table in ReportServer database, TimeDataRetrieval -
TimeProcessing - TimeRendering are approximately 1500 - 8500 - 200
respectively. Thus total report execution crosses 10 sec which I want to
reduce. In addition to drill downs, I have two columns which act like place
holders and the application which will call this report will also send me
what column the user wants to see in these placeholders and for a particular
set of two columns I have to make some calculation in each and every level of
drill down. Thus I use lot of switch statements which are time consuming.
Another place where I see some issue is SortExpression. We need sort icons to
appear for each and every column in report. For these place holder columns
SortExpressions is really lengthy as sorting also take place conditionally
with switch statments.

namecol placeholder1 placeholder2 other_columns_1_to_7
Total
------------------------------------------------------------------------------------------------

This is how my report header looks like. Also in the columns 1 to 7 I have
to do calculations conditionally. Thus I am using switch again. So here too
my Total sort expressions becomes very large.

As I mentioned above, TimeProcessing is more amount of time and I think it
is not because of DataRetrieval time. I am doing every thing to make my
report work as per requirement. Can you guys tell me how to replace costly
operations if any and achieve the same functionality.

We are initially displaying them as html for which I have mentioned the
timings. We also want to export to pdf, excel ,csv and tiff. We are never
successful in exporting to Excel. Always it gives the popular error, "Damage
to the file is so large that it cannot be repaired."

Thanks.

[quoted text, click to view]