Performance issue with Multi value parameter in SSRS

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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?

  • 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

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

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