Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS Report Optimisation Query Expand / Collapse
Author
Message
Posted Monday, August 18, 2014 8:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 6:56 AM
Points: 8, Visits: 40
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
Post #1604466
Posted Monday, August 18, 2014 9:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:43 PM
Points: 3,667, Visits: 8,006
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/



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1604532
Posted Monday, August 18, 2014 11:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:10 PM
Points: 11,322, Visits: 13,115
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1604584
Posted Tuesday, August 19, 2014 5:13 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:01 PM
Points: 356, Visits: 871
Are you embedding the SQL in the report or is the report calling a stored procedure? The latter would be preferred if possible.
Post #1605240
Posted Wednesday, August 20, 2014 1:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 6:56 AM
Points: 8, Visits: 40
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?

Post #1605305
Posted Wednesday, August 20, 2014 6:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:10 PM
Points: 11,322, Visits: 13,115
abigail.tomlins (8/20/2014)
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?



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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1605392
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse