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

  • If you run your SELECT statement in SSMS , does it  take long time to execute the query as well?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, September 15, 2013 8:06 AM
  • Hi Wendy,

    If you want to limit the data in the report by using parameters then you can do it in the same dataset. Follow the below steps:

    1. Change the existing dataset and add the WHERE clause in the query with @ParameterName
    e.g SELECT * FROM TABLE ....  WHERE FiscalYear = @FiscalYear

    2. You can populate the the values/Labels for FiscalYear parameter from another dataset.

    In this way, you can select the value for parameter and based on parameter's values , we can limit the data for Report.

    Hope, This will help you.

    Thanks,

    Vishal


    Sunday, September 15, 2013 11:04 AM
  • I have run the sql in sql server management studio. However, I did not run the query in the ssrs 2008 report yet since I have been trying to run the report. Would that make a difference?
    Monday, September 16, 2013 2:59 AM
  • Are you saying that for each parameter that is being used I should have a separate dataset? I am thinking if there are 4 possible values for the 'main' parameter, that I can place the logic in separate parts of the same data set. Do you agree?
    Monday, September 16, 2013 3:02 AM
  • Hi,

    Did you try like shown in first link in my previous post - Dynamic dataset .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, September 16, 2013 3:30 AM
    Moderator
  • what do you mean by a Dynamic dataset? Can you show me some code so that I know what you are referring to?
    Monday, September 16, 2013 3:33 AM
  • 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