Geek Answers Handbook

Performance Using SSRS

Hi guys, I recently joined the company. They instructed me to improve the performance of existing SSRS reports.

I looked at a stored procedure that works fine. But when it comes to running a report, it takes time.

Please let me know how to start this thing? Thanks at Advance

+5
reporting-services
source share
9 answers

  • Is the report performing its own calculations on the returned data, which could potentially take time and resources to complete?

  • Are graphic drawings of graphs that contain a lot and a lot of data that can take longer than a graph with a large increment?

  • Is the message an image download? How are images processed? Are they built-in or connected to an external source?

  • Are you creating many groups or abbreviations that may take a while to load a report to create?

The answer to these questions may begin to show you in the right direction how to make the report more effective.

+6
source share

You can see the ExecutionLog table in the ReportServer database. This will give you a breakdown of time, such as data retrieval, processing, rendering, etc. It can make you move in the right direction.

For example, in my previous project rendering reports in MS Excel there was a lot of time - we switched some reports that will be exported by default to CSV format, and this helped. Similarly, we also identified a bottleneck in report processing and added more resources to the machine. It also helped.

+6
source share

What does "stored procedures work fine"? You might want to explore this further.

  • Run the stored procedures from SSMS and view the query plans that it creates. FROM
  • Make sure tables are indexed appropriately to speed up queries
  • Identify and eliminate abrupt as unnecessary connections, unnecessary selected columns, etc.
+2
source share

You can also try setting up custom paging in reports that show a huge amount of data. By doing this, you reduced the cashing time and only showed a limited number of records instead of loading the whole data for the first time.

+2
source share

You can also use the SSRS toolbar to view the performance and configuration of SSRS. http://sqlconcept.com/tools/ssrs-dashboard/

+1
source share

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx

This URL is an article written by members of the SSRS product group explaining some performance optimizations you can do. This is a good article and a great resource for this kind of problem.

Also see here:

http://msdn.microsoft.com/en-us/library/bb522786.aspx

+1
source share

The main solution for speeding up SSRS reports, regardless of the database and business intelligence in a large hierarchy, is report caching. If you do it wisely (for example, preloading the cache at 7:30 in the morning) or cache reports with a hit for 45 minutes, you can find massive gains in download speed.

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

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

From your question, you have caching on hit, if you don’t like the initial reports that have been stored for so long, and the data is relatively static throughout the day, you can increase the life of the cache .

Finally, you can also choose for business managers to instead receive these reports by email subscription , which will send them a report of the time, which they can find easier and more systematic.

If all of them do not work, consider the sniffing parameter, i.e.

If this is your original request

CREATE PROCEDURE [SP_Test_ParameterSniffing] @CustomerID INT AS BEGIN SELECT * FROM Customer c WHERE c.CustomerID = @CustomerID END

All you have to do is add a new parameter to your SP code and assign the value passed to SP to the new parameter and use it in your WHERE clause:

 CREATE PROCEDURE [SP_Test_ParameterSniffing] @CustomerID INT AS BEGIN DECLARE @CustomerID2 INT; SET @CustomerID2 = @CustomerID; SELECT * FROM Customer c WHERE c.CustomerID = @CustomerID2 END
+1
source share

Below are some of the reasons for the poor performance (longer execution time) of SSRS reports

1) when you use more cascading parameters for your reports

2) when you directly receive data from oracle or an external data source.

3) using the "for xml ()" function in the sql query of the ssrs report.

Solution: try using a snapshot report if possible

+1
source share

Improve performance in what sense? Does it take up more memory or does it make the processor processor or use too much disk I / O? What do you mean by saying that SPs work fine? They work fine, but for a long time? Or do they work normally and quickly execute?

First, I would like to understand what performance indicator to improve, and then determine where the bottleneck is, whether it is on the report server or on the database server. You can also use perfmon to identify bottlenecks.

0
source share

More articles:


All Articles