SSRS Performance

Question

I created an SSRS report to retrieve 55,000 records using a stored procedure. when execution from Stored Proc takes only 3 seconds, but when executing from an SSRS report, it takes more than one minute. How can I solve this problem?

Answer 1

Additional time may be due to Reporting Services providing a report in addition to requesting data. For example, if you have 55,000 rows returned for a report, and the report server then needs to group, sort and / or filter these rows to render the report, then this may take extra time.

I would look at how data is grouped and filtered in a report, and then look at your stored procedure to see if it is possible to unload part of this processing into SQL code, possibly using some parameters. Try to reduce the number of lines returned to the report, at least necessary for rendering the report, and preferably try to avoid grouping and filtering in the report itself.

Answer 2

I had such a problem due to parameters sniffing in my SP. In SQL Management Studio, when I launched my SP, I recreated it with a new execution plan (and the call was very fast), but my reports used the old bad plan (for a different sequence of parameters) and the load time was much longer than in SQL MS.

Answer 3

Use SSRS Performance Dashboard reports to debug your problems.

Answer 4

in ReportServerDB you will find a table called ExecutionLog. you need to find the directory id of your report and check the latest execution instance. it can tell you about the time gap - for finding data, for processing, for rendering, etc.

Answer 5

An archaic question, but since such things are repeated, my “quick and dirty” SSRS improvement solution that works fine in large corporate environments (I provide reports that can contain up to 100,000 lines per day), set InteractiveSize correctly on the page (for example , set for A4 size -21 cm). If InteractiveSize is set to 0, all results will be displayed as a single page, and this literally kills the performance of SSRS. In such cases, queries that may take several seconds on your database can be visualized forever (or cause an exception in memory if you do not have excess H / W on your SSRS server). Thus, in cases of / SP queries that execute quite quickly on a direct call and retrieve a large number of lines, install InteractiveSize and you won’t need to worry about other more complex solutions.

Answer 6

Obviously, getting a report that works correctly (i.e., accepting the same order of magnitude of time for selecting data in the form of SSMS) would be preferable, but as a working environment, your report would provide snapshots (i.e. not saved no parameters or default parameter values ​​in the report)?

This will allow you to receive and accumulate the planned snapshot of data in a timely manner, which means that SSRS needs to process and display the report when the user opens it. You need to reduce the wait time to a few seconds (depending on what processing the report requires. YMMV, a test to see if the quality has improved).

Go to the report properties tab in the report manager, select "Run", "Change" to display this report from a snapshot of the report execution, specify the schedule.

Answer 7

The primary solution for speeding up SSRS reports is the report cache . If you do this (for example, pre-loading the cache at 7:30 in the morning) or caches of getting into reports, one will find massive gains in download speed.

Please note that I do this daily and professionally and do not just instruct poetry in SSRS

SSRS caching http://msdn.microsoft.com/en-us/library/ms155927.aspx

Preloading the cache http://msdn.microsoft.com/en-us/library/ms155876.aspx

If you don’t like the initial reports, long and your data is static, that is, a daily shared book or the like, which means that the data is relatively static throughout the day, you can increase the cache’s lifespan .

Finally , you can also choose that business managers instead receive these reports via email subscription , which will send them a time report in Excel, which they can find easier and more systematic.

You can also use parameters in SSRS to provide easy parsing by the user and faster queries. In the IN type query builder (@SSN) in the Filter column that you want to parameterize, you will find it in the parameters folder just above the data sources in the upper left corner of your BIDS GUI. [If you do not see the data source section in SSRS, press CTRL + ALT + D.

See an almost identical question here: Performance Exceptions with SSRS

Answer 8

Few can be done to improve report performance, which are listed below: 1. Turn on caching in Report Manager and set a time period for updating the cache. 2. Use indexing in all the database tables of the database that are used as the source in the report, although your stored procedure already takes very little time to visualize the data, but still using indexing can further improve performance at the backend level. 3. Use shared datasets instead of using the built-in datasets in the report and also apply caching to all of these datasets. 4. If possible, set options for loading default values. 5. Try reducing the data selected by the stored procedure, for example. if the report contains historical data that is useless, a filter can be added to exclude this data.

Answer 9

I had the same problem. The query worked in SQL just fine, but was slow, as in SSRS. Do you use the SSRS parameter in your dataset? I found that if you use the report parameter directly in certain queries, there is tremendous success.

Instead, if you have a report parameter called @reportParam in a dataset, simply do the following:

declare @reportParamLocal int set @reportParamLocal = @reportParam select * from Table A where A.field = @reportParam 

It's a bit strange. I do not quite understand why this works, but this is for me.

Answer 10

One quick thing you might want to take a look at is whether the elements in your report can slow you down.

For example, I found significant run-time differences when converting between dateTimes. Do you use any CDate function in the report? If so, you might consider formatting at the sql level.

Conversions in general can lead to a massive slowdown, so take the time to look at your data set and see what could be the problem.

Answer 11

This is a bit of a mix of the answers above, but do your best to return the data from the stored procedure to the simplest and most complete format. I do all sorting, grouping and filtering on the server. The server is built for this, and I just let the reporting services do a pretty display job.

Answer 12

I had the same problem ... it was really a render time, but more specifically, it was because SORT was in SSRS. Try moving the sort to a stored procedure and remove any SORT from the SSRS report. On 55K lines, this will greatly improve it.

Answer 13

In response to @RomanBadiornyi answer, add

 OPTION (RECOMPILE) 

until the end of the main request in the stored procedure.

This ensures that the query will be recompiled for different parameters each time, in case different parameters need a different execution plan.

Answer 14

I had a similar problem: a query that returns 4,000 rows and runs in 1 second on its own takes so much time in SSRS that it has expired.

It turned out that the problem was caused by the way SSRS handled the multi-valued parameter. Interestingly, if the user selected several values, the report was rendered quickly (~ 1 second), but if only one value was selected, the report should have taken several minutes.

Here is the original query, which took more than 100 times longer than needed:

 SELECT ... FROM ... WHERE filename IN (@file); -- @file is an SSRS multi-value parameter passed directly to the query 

I suspect the problem was that SSRS cited the entire source table (over 1 million rows) and then executed the client filter.

To fix this, I ended up passing the parameter to the query through an expression so that I can control the filter myself. That is, in the "DataSet Properties" window on the "Parameters" screen, I replaced the parameter value with the following expression:

 =JOIN(Parameters!file.Value,",") 

... (I also gave the result a new name: filelist), and then I updated the request to look like this:

 SELECT ... FROM ... WHERE ',' + @filelist + ',' LIKE '%,' + FILENAME + ',%'; -- @filelist is passed to the query as the following expression: -- =JOIN(Parameters!file.Value,",") 

I would suggest that moving a request to a stored procedure would also be an effective way to fix the problem (since SSRS basically does the same JOIN before passing a parameter with multiple values ​​to the stored procedure). But in my case it was a little easier to handle all this in a report.

Finally, I have to point out that the LIKE statement may not be the most efficient way to filter by a list of items, but of course it’s much simpler than the code with a shared line approach, and the report now works after about a second, so line breaks didn’t seem useful to you .