SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS reporting services processing large data


SSRS reporting services processing large data

Author
Message
rajesh.gupta723
rajesh.gupta723
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 1
Hi,

We have SSRS reporting server 2014 and while processing data for large date range (hence carrying a heavier payload), result set is taking huge time (7-8 mins). The result set is carrying about 100-500K records. The objective is to get the results under <2 mins.

These reports are seen through SSRS webform reportviewer hosted inside a web application. I tried using SOAP services exposed by SSRS server and consuming them through the web application. Result is still same, either it times out or takes > 10 mins or so. Even if you run the reports directly on SSRS report server portal, the reports are taking 6-7 mins.

Also, large reports even though rendered on UI after long time, when using "Export to PDF", they are timing our or throwing errors (Internal server error).

Any suggestions or pointers, will be appreciated.

Regards
Rajesh
Sue_H
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72186 Visits: 14878


In terms of the report, you may want to look at whether or not caching and refreshing at certain intervals makes sense - that's often a good way to manage reports with large data sets to process.
In terms of the errors, you'd probably want to look into the Report Server log to get more details and post any and all error messages. If you have a lot of this, you probably want to go through the ExecutionLog view and just aggregate and analyze some of the information to see where and what to work on for performance. Sometimes things like creating a stored procedure instead of using embedded SQL can make a difference. But what to look at and what to work on depends on the statistics you can gather from the ExecutionLog.

Sue



Michael L John
Michael L John
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21056 Visits: 10241
I'm curious about the time it take for the query to complete if it is run in SQL Management Studio. I think the place to start is to make sure the underlying query is optimal.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
bmg002
bmg002
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: 20008 Visits: 2713
Another thing to look at would be filtering those results. Is a report with 100K to 500K rows actually useful in any manner? I am not asking if the data is useful, but is a REPORT with that much data useful?
From an end user side of things, looking at 100,000 rows of data would make my end users eyes bleed. Most of the reports on my SSRS server return as few rows as possible. We try to direct the results to the specific users using filters. So end users only get rows that are of interest to them. If we sent them reports with 100,000 rows (or even worse; 500,000 rows), most of them would be ignored.
Also, how wide are the rows? The more data you pull back, the slower the report will be.
If I am not mistaken, SSRS puts all of the data for the report (after applying filters) in memory and then it will break it out into pages. So the larger the data set, the slower this process will be. Especially if you end up having memory pressure on your SSRS server. Do you have enough RAM to handle the 100k-500k rows of data? If not, it will be dumping that to disk which will make this even slower.

If the end goal is to export this to some format, you may get better performance using a different tool for this. One example I can think of would be SSIS or even Excel. With excel, the end users could just pull the data as they need it. It's still be slow trying to pull in that much data, but then they only need to click on the "refresh data" button whenever they need fresh data.

Or, depending on how the end user is consuming and using the data, building an application in C# that would display the in a datagridview or similar format might be a better option.
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