SSRS reporting services processing large data

Question

SSRS reporting services processing large data

rajesh.gupta723

SSC Veteran

Points: 212
More actions
June 21, 2017 at 9:12 pm

#326843

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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 One Orange Chip Points: 27038 More actions · Answer 2

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/

Mr. Brian Gale SSC-Insane Points: 24670 More actions · Answer 3

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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.