Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to reduce the Time Processing for a SSRS Report


how to reduce the Time Processing for a SSRS Report

Author
Message
rajeshkumar.b
rajeshkumar.b
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 34
I have a SSRS Report which retrives the data from SSAS cube.

When i look at the TimeProcessing for the specific report it is too high , how to fix this?

The rowcount for this is 2500 records.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Without more details, I can only tell you to do less work on the SSRS.


How long does it take to run the MDX?
steveb.
steveb.
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2848 Visits: 7195
are there any parameters being used?

Also need more detail..
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Here's the big picture of how I go about this.

1 - What's the total processing time?

2 - What's the query processing time?

From there you know where it hurts (SSAS, or SSRS). If it's pretty much 50% / 50%, then make sure the query is running as fast as possible.

3 - Can you remove groupings?, can you remove calculated columns and do it in the query?, do you have a lot of conditional formating?, do you have .net code that runs on the details?

That list goes on for quite a while...
Dirt McStain
Dirt McStain
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 138
I had the same issue with pulling ~30k records from a stored procedure. Data retrieval and rendering time grew in a linear fashion as row counts went up and processing time grew exponentially. As soon as I removed the column groups (something that I can't do permanently, unfortunately), my TimeProcessing went from 110K to 6K. Big difference!

________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
sbf1100
sbf1100
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
I have also found by taken out any order by statements it also has helped. that is if you have any.
Dirt McStain
Dirt McStain
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 138
Performance tuning depends on which of these three is holding up the report:processing, Data retrieval, or rendering. You'll notice that the report will run those three tasks in that order, as well, and the report Time Out is based solely on the data retrieval portion. So, if it takes 2 minutes to process, 1 minute to retrieve, and your Time Out setting is set to 30 seconds, it'll spent 2 minutes to process first, then 30 seconds to begin and cancel retrieving.

You can check out statistics with the following query:


use ReportServer

select * from ExecutionLog2



The things to look at are TimeDataRetrieval, TimeProcessing, and TimeRendering relative to RowCount. For example, in my report I had 1 page group, 5 column groups, and 3 row groups, and detail level data. By far, the slowest performer was TimeProcessing, and it was an exponential grower (technically an order of 2 polynomial). I was able to go from 10 minutes total run time to 2 minutes by moving one of the column groups to a page group, so that it was: 2 page groups, 4 column groups, and 3 row groups. The report still displayed all of the data, but the performance increased significantly. I was able to pull 60K+ rows faster with this than I could pull 30K+ rows before (not that SSRS should be used to pull that much data).

If you're experiencing lag with TimeDataRetrieval, then tune your query, and eliminate things like order bys. If you're experiencing lag with TimeRendering, then go easy on the visuals.

________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
gayans
gayans
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 32
Hi,

How do you add page groups ? I can see only column groups and row groups.
Thanks
Dirt McStain
Dirt McStain
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 138
On the Design tab, go to your Row Groups section (bottom left pane of the Design tab) and right click on whichever group you want to page break by. Select Properties from the drop-down menu, go to the Page Breaks section, and select the desired page break options.

________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Tee Time
Tee Time
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 Visits: 465
As mentioned, any order by, group, select distinct, where will slow down the report.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search