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

Answer 1

Well it seems your queries are slow.

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

Answer 2

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

Answer 3

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

Answer 4

Hello Syed

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

Thanks

Answer 5

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 [e-mail] and I will have a look

Many thanks

Syed Qazafi Anjum

Answer 6

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

Answer 7

Hi There

I have sent back your code please have a look

Many thanks

Syed

Answer 8

Hello Syed

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

Thank you

Answer 9

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.

Answer 10

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

Answer 11

What do you mean by expressions? What did you do with expression to solve the problem?

Answer 12

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"