Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to reduce the Time Processing for a SSRS Report Expand / Collapse
Author
Message
Posted Friday, January 7, 2011 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 7:40 AM
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.
Post #1044517
Posted Friday, January 7, 2011 9:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,385, Visits: 9,602
Without more details, I can only tell you to do less work on the SSRS.


How long does it take to run the MDX?
Post #1044525
Posted Friday, January 7, 2011 9:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
are there any parameters being used?

Also need more detail..
Post #1044526
Posted Friday, January 7, 2011 9:59 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,385, Visits: 9,602
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...
Post #1044532
Posted Wednesday, February 9, 2011 5:53 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
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
Post #1061649
Posted Friday, February 11, 2011 9:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 25, 2011 9:24 AM
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.
Post #1062688
Posted Friday, February 11, 2011 11:05 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
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
Post #1062740
Posted Sunday, November 18, 2012 8:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:06 PM
Points: 2, Visits: 30
Hi,

How do you add page groups ? I can see only column groups and row groups.
Thanks
Post #1386138
Posted Tuesday, December 18, 2012 1:46 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
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
Post #1398025
Posted Tuesday, December 18, 2012 2:49 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:50 PM
Points: 532, Visits: 448
As mentioned, any order by, group, select distinct, where will slow down the report.

Post #1398054
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse