Home Forums Reporting Services Reporting Services how to optimise the performance of the reports RE: how to optimise the performance of the reports

  • May 22, 2015 at 7:36 pm

    #1800182

    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)