August 18, 2014 at 8:12 am
Good Afternoon
I have been creating a report within SSRS containing a number of tablix matrix and charts. My current execution time is just over 10 seconds however I want to get it down to 5 seconds. I'm new to SSRS so apologies if this is a silly question. I have been researching optimisation techniques online and have found I need to look at the execution plans for the reports. I have narrowed my slow rendering issue down to one of the charts within the report (a line graph) for which I have looked at the SQL query and used the execution plan to determine the problem. It told me to index an area of the query which I have now completed however it doesn't seem to have any impact within the running time of the SSRS report. At the moment it's still running at just over 10 seconds. Does anyone have any ideas as to what else I can try?
Many Thanks
Abbie
August 18, 2014 at 9:57 am
Performance problems can be caused by many things and we don't have enough information. Please read the following article to be able to post what we need to help you.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 18, 2014 at 11:16 am
How are you running the report, in BIDS or deployed to a report server? I'd recommend deploying it to a report server and testing performance there. You can query the ExecutionLog3 view in the ReportServer database to find out a bit more about what part of the process needs the most tuning: data retrieval (the query), processing (calculations, etc... on the report), rendering. You can then use this data to assess if changes you have made are working and how well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 19, 2014 at 5:13 pm
Are you embedding the SQL in the report or is the report calling a stored procedure? The latter would be preferred if possible.
----------------------------------------------------
August 20, 2014 at 1:24 am
Hiya
The report is calling a stored procedure so speed wise it should be ok, I'm wondering whether it's just the amount of data it's trying to retreive as it's quite alot?
August 20, 2014 at 6:40 am
abigail.tomlins (8/20/2014)
HiyaThe report is calling a stored procedure so speed wise it should be ok, I'm wondering whether it's just the amount of data it's trying to retreive as it's quite alot?
While using a stored procedure may make it easier to optimize teh SQL, in reality, a stored procedure vs. parameterized sql doesn't really make a difference in performance. You really need to take a look at the ExecutionLog3 view, as I mentioned in my first post to determine what part of the report run is taking the most time. A query like this:
SELECT
EL.ItemPath,
EL.UserName,
EL.ExecutionId,
EL.RequestType,
EL.Format,
EL.TimeDataRetrieval,
EL.TimeProcessing,
EL.TimeRendering,
EL.Source,
EL.ByteCount,
EL.[RowCount]
FROM
dbo.ExecutionLog3 AS EL
is a decent starting point as you'll have some actual metrics to work from.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply