none
ssrs 2008 parameters to solve performance problem

    Question

  • In an SSRS 2008  report that I am working on, I am having a performance problem. The report is taking about 30 minutes to run which is too long. My problem is there is a full file scan.
     To make the report run faster, I am thinking I could run queries based upon the selection values of 3 different paramters users can select from. If this is possible can you show me sql on how to accomplish this goal? Would I use the same dataset?

    If not, can you show me how you would use multiple datasets?
     If my idea does not work, what would you recommend? Can you show me the sql?

    Saturday, September 14, 2013 9:08 PM

Answers

  • Hi Wendy,

    In your scenario, the report take long time to run, so you want to improve the report performance using parameter to filter data, right? In Reporting Services, we can filter dataset data using report parameters in two ways. One is add WHERE clause on the query like:
    SELECT * FROM TABLENAME WHERE COLUMN=@ParameterName
    Another is add filter to dataset (right-click dataset>DataSet Properties>Filters)
    Expression:Column
    Operator:   =
    Value:        @ParameterName
    For the detail information about how to use a parameter to filter data, please see:
    Tutorial: Adding Parameters to a Report

    Besides, report server performance is affected by a combination of factors. The total time to generate a reporting server report (RDL) can be divided into three elements: Time to retrieve the data (TimeDataRetrieval), Time to process the report (TimeProcessing), Time to render the report (TimeRendering). We can review the report server execution log to find out how long each processing takes to process. When processing larger report, we should choose report execution, rendering, and delivery options that can accommodate large documents. Please refer to the following article about report performance:

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Tuesday, September 17, 2013 1:39 AM
    Moderator

All replies