January 7, 2011 at 9:44 am
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.
January 7, 2011 at 9:50 am
Without more details, I can only tell you to do less work on the SSRS.
How long does it take to run the MDX?
January 7, 2011 at 9:51 am
are there any parameters being used?
Also need more detail..
January 7, 2011 at 9:59 am
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...
February 9, 2011 at 5:53 pm
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
February 11, 2011 at 9:48 am
I have also found by taken out any order by statements it also has helped. that is if you have any.
February 11, 2011 at 11:05 am
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
November 18, 2012 at 8:44 pm
Hi,
How do you add page groups ? I can see only column groups and row groups.
Thanks
December 18, 2012 at 1:46 pm
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
December 18, 2012 at 2:49 pm
As mentioned, any order by, group, select distinct, where will slow down the report.
December 19, 2012 at 9:41 am
View the code and change
<CanGrow>true</CanGrow>
to <CanGrow>false</CanGrow>
This helps a little bit in the rendering time .
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply