how to optimise the performance of the reports

Question

how to optimise the performance of the reports

premkumar8112

SSC Journeyman

Points: 94
More actions
May 19, 2015 at 3:01 am

#304114

steps ?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

Avoid ad-hoc sql and use stored procs to drive your ssrs data. Faster queries mean faster reports; tune your queries - make em fast.

For your slowest reports consider caching.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

premkumar8112 SSC Journeyman Points: 94 More actions · Answer 2

in reportin services ,from site setting options...one of frnd suggested me to reduce the seconds time...so after selected the time options..now my reports are bit fast, i mean not bad..:) ..but am nt sure whther this is solution for my prblm...and as u suggested i will follow queries using SP ...Thanks for ur quick reply Alan Burstein...

Lowell SSC Guru Points: 323504 More actions · Answer 3

look at the report design as well; as mentioned, make sure the underlying query/proceudre is effience and tuned of course, but check this out:

i inherited a suite of reports that did Select [lots of columns] from sometable, and then did the grouping in the report.

load a MillionBillion row table into SSRS, and then let it handle the grouping, ouch! it made the report run in hours.

changing the report to do the grouping on the SQL side, and report the results, made it run in seconds, even with the overhead and crappy design of SSRS.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 4

Lowell (5/19/2015)
look at the report design as well; as mentioned, make sure the underlying query/proceudre is effience and tuned of course, but check this out:
i inherited a suite of reports that did Select [lots of columns] from sometable, and then did the grouping in the report.
load a MillionBillion row table into SSRS, and then let it handle the grouping, ouch! it made the report run in hours.
changing the report to do the grouping on the SQL side, and report the results, made it run in seconds, even with the overhead and crappy design of SSRS.

+10

To add to Lowell's response, do all of your filtering in SQL as well. Try to avoid bringing over more data than you intend to display.

The only reason to use a tablix filter is if you you have multiple tablix objects that are displaying subsets of a single, larger data set... In which case, it's better to pull all the required data in a single shot and divvy it up report side.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Jason A. Long (5/22/2015)
Lowell (5/19/2015)
look at the report design as well; as mentioned, make sure the underlying query/proceudre is effience and tuned of course, but check this out:
i inherited a suite of reports that did Select [lots of columns] from sometable, and then did the grouping in the report.
load a MillionBillion row table into SSRS, and then let it handle the grouping, ouch! it made the report run in hours.
changing the report to do the grouping on the SQL side, and report the results, made it run in seconds, even with the overhead and crappy design of SSRS.
+10
To add to Lowell's response, do all of your filtering in SQL as well. Try to avoid bringing over more data than you intend to display.
The only reason to use a tablix filter is if you you have multiple tablix objects that are displaying subsets of a single, larger data set... In which case, it's better to pull all the required data in a single shot and divvy it up report side.

It depends. If your ultimate goal is to produce a matrix with subtotals, etc, you might be far better off doing the aggregations and sub-totals, etc, on the T-SQL side of the house so that you don't take a performance hit in pulling all the required data into SSRS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 6

Jeff Moden (5/22/2015)
Jason A. Long (5/22/2015)
Lowell (5/19/2015)
look at the report design as well; as mentioned, make sure the underlying query/proceudre is effience and tuned of course, but check this out:
i inherited a suite of reports that did Select [lots of columns] from sometable, and then did the grouping in the report.
load a MillionBillion row table into SSRS, and then let it handle the grouping, ouch! it made the report run in hours.
changing the report to do the grouping on the SQL side, and report the results, made it run in seconds, even with the overhead and crappy design of SSRS.
+10
To add to Lowell's response, do all of your filtering in SQL as well. Try to avoid bringing over more data than you intend to display.
The only reason to use a tablix filter is if you you have multiple tablix objects that are displaying subsets of a single, larger data set... In which case, it's better to pull all the required data in a single shot and divvy it up report side.
It depends. If your ultimate goal is to produce a matrix with subtotals, etc, you might be far better off doing the aggregations and sub-totals, etc, on the T-SQL side of the house so that you don't take a performance hit in pulling all the required data into SSRS.

Huh? That was Lowell's point... And I agree 100% with what he said. My point, as an add on, was to say, "also, make sure aren't bringing back more data than what you intend to display". Handle the filtering in SQL not in the tablix.

The exception being, if your data is being split between multiple tablix, bring back everything all at once...

As an example... The company I work for handles workers comp claims. It's not uncommon for the ops folks to want the data split between surgical and nonsurgical. From both the query and report layout perspectives it's all the same. That means I can pull both surgical and nonsurgical at the same time in the same data set and dump the the surgical into one tablix and nonsurgical into another simply by using the tablix filers to split the data between the two.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Jason A. Long (5/22/2015)
Jeff Moden (5/22/2015)
Jason A. Long (5/22/2015)
Lowell (5/19/2015)
look at the report design as well; as mentioned, make sure the underlying query/proceudre is effience and tuned of course, but check this out:
i inherited a suite of reports that did Select [lots of columns] from sometable, and then did the grouping in the report.
load a MillionBillion row table into SSRS, and then let it handle the grouping, ouch! it made the report run in hours.
changing the report to do the grouping on the SQL side, and report the results, made it run in seconds, even with the overhead and crappy design of SSRS.
+10
To add to Lowell's response, do all of your filtering in SQL as well. Try to avoid bringing over more data than you intend to display.
The only reason to use a tablix filter is if you you have multiple tablix objects that are displaying subsets of a single, larger data set... In which case, it's better to pull all the required data in a single shot and divvy it up report side.
It depends. If your ultimate goal is to produce a matrix with subtotals, etc, you might be far better off doing the aggregations and sub-totals, etc, on the T-SQL side of the house so that you don't take a performance hit in pulling all the required data into SSRS.
Huh? That was Lowell's point... And I agree 100% with what he said. My point, as an add on, was to say, "also, make sure aren't bringing back more data than what you intend to display". Handle the filtering in SQL not in the tablix.
The exception being, if your data is being split between multiple tablix, bring back everything all at once...
As an example... The company I work for handles workers comp claims. It's not uncommon for the ops folks to want the data split between surgical and nonsurgical. From both the query and report layout perspectives it's all the same. That means I can pull both surgical and nonsurgical at the same time in the same data set and dump the the surgical into one tablix and nonsurgical into another simply by using the tablix filers to split the data between the two.

I was responding to the following...

The only reason to use a tablix filter is if you you have multiple tablix objects that are displaying subsets of a single, larger data set... In which case, it's better to pull all the required data in a single shot and divvy it up report side.

Shifting gears, I'd lose the word "Huh?". I know better because I've seen your posts but it makes people sound ignorant.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 8

Jeff Moden (5/22/2015)
I was responding to the following...
The only reason to use a tablix filter is if you you have multiple tablix objects that are displaying subsets of a single, larger data set... In which case, it's better to pull all the required data in a single shot and divvy it up report side.
Shifting gears, I'd lose the word "Huh?". I know better because I've seen your posts but it makes people sound ignorant.

In retrospect I can see that the idea wasn't expressed as clearly as it should have been.

As for the "Huh?"... Please accept my apology. I've sound ignorant in the past and I'm pretty sure I'll sound ignorant again in the future. I can live with that...

The apology is for the fact that, upon re-readying, I can see where it may read as belligerent and/or confrontational. That wasn't my intent...

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Understood and thanks for taking the time to explain.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)