none
MultiValue Parameters in SSRS causes report to be slow

    Question

  • Hello

    I am using SQL 2008 R2. My report runs very slow when I have multivalue parameters. There are three multivalue parameters and those are cascading..

    Please help me with this issue.

    Thank you

    Monday, July 16, 2012 7:26 PM

Answers

  • Hello Alex

    Database design and query both works good...It just that because of multiple values of Parameters create so many rows and because of that report was slow...But now i am using expressions in SSRS, so report runs good now..

    Thank you

    Friday, July 20, 2012 6:15 PM

All replies

  • Well it seems your queries are slow.

    Which means, please give some more details about your setup!

    • Edited by Alex Jean Monday, July 16, 2012 8:01 PM
    Monday, July 16, 2012 8:00 PM
  • Hello Alex

    SQL Query runs fine for that report...If i select one value in that parameter, it works fine...but when i select all the values in parameter, it runs slow in my stored procedure.

    Is there any way to handle this issue?

    Thanks

    Monday, July 16, 2012 8:10 PM
  • Hi There

    Thanks for your posting. If you are using stored procedure, then you can convert your multivalued parameter to table value variable and it will give you reasonable gain in performance

    If you have any questions please do ask.

    1 Create  table value function in your database(I am sure you are already doing this
    CREATE    function [dbo].[SplitString](
     @String varchar (8000),
     @Delimiter varchar (10)
     )
    returns @ValueTable table ([Value] varchar(8000))
    begin
     declare @NextString varchar(8000)
     declare @Pos int
     declare @NextPos int
     declare @CommaCheck varchar(1)
     if (@String IS NULL) OR (@Delimiter IS NULL) OR (rtrim(@String) = '')
    BEGIN
     insert into @ValueTable ( [Value]) Values (@String)
    return 
    END
     
     --Initialize
     set @NextString = ''
     set @CommaCheck = right(@String,1) 
     
     --Check for trailing Comma, if not exists, INSERT
     --if (@CommaCheck <> @Delimiter )
     set @String = @String + @Delimiter
     
     --Get position of first Comma
     set @Pos = charindex(@Delimiter,@String)
     set @NextPos = 1
     
     --Loop while there is still a comma in the String of levels
     while (@pos <>  0)  
     begin
      set @NextString = substring(@String,1,@Pos - 1)
     
      insert into @ValueTable ( [Value]) Values (@NextString)
     
      set @String = substring(@String,@pos +1,len(@String))
      
      set @NextPos = @Pos
      set @pos  = charindex(@Delimiter,@String)
     end
     
     return
    end
    -----------------------------------------------------------------------------------------------------------------------
    now in you stored procedure  please use something like this I assume that @_Entity_ID is your multivalue parameter
    first convert your multivalue parameter to table variable using the function above you have created
    Declare @_EntityTab Table (Param_Entity Varchar(6))
    Insert into @_EntityTab Select * From dbo.SplitString(@_Entity_ID,',')
    select * 
    From
     DWH_DimCostCentre as DIMCC
      Inner Join
     @_EntityTab
      On DIMCC.Unit = @_Unit_ID
      And DIMCC.[Entity] = Param_Entity
    you have noticed that I have used the Table avriable inside my inner join above this is the fastest way to use Multi value
    parameter i have found.

    You can also use XML PATH method.

    http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/

    Many thanks

    Syed Qazafi Anjum

    Monday, July 16, 2012 8:23 PM
  • Hello Syed

    I applied your logic. But i am not getting any data. How i can populate @_EntityTab?

    Thanks

    Monday, July 16, 2012 8:45 PM
  • Hi There

    Here we are populating tha data for table variable. I am assuming that you already have a function dbo.SplitString which split your multivalued parameter(please replace with your function)and @_Entity_ID is your multivalued parameter please replace with your one

    Declare @_EntityTab Table (Param_Entity Varchar(6))

    Insert into @_EntityTab Select * From dbo.SplitString(@_Entity_ID,',')

    select *

    From

    DWH_DimCostCentre as DIMCC

      Inner Join

    @_EntityTab

      On DIMCC.Unit = @_Unit_ID

      And DIMCC.[Entity] = Param_Entity

    I think this will help. Otherwise please send your SP code to my email address sqazafi@hotmail.com and I will have a look

    Many thanks

    Syed Qazafi Anjum

    Monday, July 16, 2012 9:01 PM
  • I have sent you my stored procedure on your e-mail address. There are three parameters . i.e. Segment, Parent, Entity....because of those, Report runs very slow...

    Thank you

    Monday, July 16, 2012 9:17 PM
  • Hi There

    I have sent back your code please have a look

    Many thanks

    Syed

    Monday, July 16, 2012 9:47 PM
  • Hello Syed

    I tested your code...But still its slow....Can you suggest me if i can do anything else in there?

    Thank you

    Tuesday, July 17, 2012 1:38 PM
  • It does seem to be either a bad DB design or a bad query.

    Or if the 'all value' is 200 000 rows, it is normal that the query runs slow.

    Still, there is not enough information to help you out.

    Friday, July 20, 2012 1:48 PM
  • Hello Alex

    Database design and query both works good...It just that because of multiple values of Parameters create so many rows and because of that report was slow...But now i am using expressions in SSRS, so report runs good now..

    Thank you

    Friday, July 20, 2012 6:15 PM
  • What do you mean by expressions? What did you do with expression to solve the problem?
    Thursday, November 01, 2012 7:56 PM
  • Actually, I was using stored procedure with query parameter. So now i am using report parameter and my query is in the expressions.

    Expressions example: Instead of writing - Select * from geography. I will go to fx as shown in the image. And write something like this

    ="Select * from Geography"

    Friday, November 02, 2012 3:37 PM