Performance issue with Multi value parameter in SSRS

Question

Post reply

Performance issue with Multi value parameter in SSRS

Lucky's

SSChasing Mays

Points: 616
More actions
October 4, 2012 at 1:00 am

#282517

Hi,
I have a multi value parameter in SSRS report which has a list of values around 600..When the user selects 'SELECT ALL',its passing the long list of values
to sql procedure and impacting performance of report..its getting timed out even..we
used 'ALL' option ( which passes a string 'ALL' instead of passing all values) also for improving performance..but the user requirement is like..
sometimes they need to select 590 values out of 600 values..which will not work with 'ALL'
Can any one have a better way to resolve this with better performance which works with SSRS 'Select ALL'.
-Lucky

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sanjeewan99 Say Hey Kid Points: 700 More actions · Answer 1

Hi,

Go through this link how to pass Multivalue parameter efficently

http://www.bi-rootdata.com/2012/09/efficient-way-of-using-all-as-parameter.html

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

Hi,

How does the report perform if you remove the part of the WHERE clause that references the MVP?

If it's much quicker, my way would be to get the data into a temp table, and then use the MVP to filter from that table.

Should speed things up.

The ALL option in an SSRS MVP doesn't pass a string of 'ALL', just a list of all the values.

Cheers

Lucky's SSChasing Mays Points: 616 More actions · Answer 3

Hi,

Thank you for the reply..

Actually,in SSRS parameter data set,we have added 'ALL' string using Union

say..

Select distinct colmn name as Param from table

union

select 'ALL'

in SQL Procedure implemented as..

...

Where Column name in @Param or @Param='ALL'

--

so,in dropdown list in SSRS,if the user selects 'ALL',the string only will be passed and if the user selects 'ALL', or with some values say 10 or 15,the performance is even good..but the user wants to use SELECT ALL (for 600 vaues selected or 599 excluding a single value) as explained above..

And i tried even with Temp table..but no performance improvement

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

No worries, was wondering if you'd done something like that, or used the in-built SSRS behaviour, which does as I described.

I've also had success using temp tables the other way around - loading the parameters' values into temp tables and using those tables in the where clause.

Would you be able post the report code at all? Might be able to help speed it up.

Thanks

sanjeewan99 Say Hey Kid Points: 700 More actions · Answer 5

Hi,

Go to this link efficent ways to pass multivalued paramete using All

http://www.bi-rootdata.com/2012/09/efficient-way-of-using-all-as-parameter.html

H4K Hall of Fame Points: 3374 More actions · Answer 6

Lucky's (10/4/2012)
Hi,
I have a multi value parameter in SSRS report which has a list of values around 600..When the user selects 'SELECT ALL',its passing the long list of values
to sql procedure and impacting performance of report..its getting timed out even..we
used 'ALL' option ( which passes a string 'ALL' instead of passing all values)

Firstly use SQL Server Profiler to get the exact query with the parameters values been send to database engine. For this you have to run the profiler on relational database engine and then execute the report.

Check this query. Then run this query from SSMS to check the performance.

but the user requirement is like..
sometimes they need to select 590 values out of 600 values..which will not work with 'ALL'
Can any one have a better way to resolve this with better performance which works with SSRS 'Select ALL'.

How are you using this multi-value parameter in your query ?

If you are using 'IN' operator, replace it with a inner join to a temp table containing all the parameter values.

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

Lucky's SSChasing Mays Points: 616 More actions · Answer 7

Hi,

Thanks a lot for the reply.

Am already checking with profiler..very poor performance as there was along string for that parameter..which works well with passing 'ALL' .But our user dont want to use 'ALL' as i explained above.

Does replacing the IN with inner join with temp table handles multi valued values also?

Can you please explain with an example?

H4K Hall of Fame Points: 3374 More actions · Answer 8

Does replacing the IN with inner join with temp table handles multi valued values also?
Can you please explain with an example?

Check out this example

You can join your selected values together by using the following code in SSRS before passing it to the stored procedure...

JOIN(Parameters!MultiValParam.Value, ",")

Note: You can increase the size of the variable to include large number of values if the value of 8000 shown in the example is not enough and truncation occurs.

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI