locked
Parameters & Report Performance .. RRS feed

  • Question

  • Hi all,

    I have a report which uses two different datasets. The first one is used to get the required data and the second one is to get the customer name to be used in a parameter.

     I had a major performance issue with the report as the first dataset is very complex. And after I did a lot of SQL tuning to the first dataset, it is now working fine with an acceptable performance.

    The problem now is when I tried to add a condition in this dataset to be used by the parameter like

    WHERE  CUSTOMER_NAME  IN (@CustName )

    The performance of the report is getting very bad again and the report can take more than 15 min. to get the result.

    The second dataset is a simple query that is used to get the customer name from the customer table so I don’t think it is the problem.

     Any ideas?  Do you think it has anything to do with the SSRS parameters.

    Thanks all.

     

    Monday, January 28, 2008 3:47 PM

Answers

  • the problem seems to be with the IN clause. IN is a costly operation and it genereates a similar effect as multiple OR conditions. is Customer_name your primary key? if not, try applying the filter on the primary key (when the user select a customer name, you should gather the customer ID at the backend).


    Monday, January 28, 2008 5:40 PM