Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance issue with Multi value parameter in SSRS Expand / Collapse
Author
Message
Posted Thursday, October 4, 2012 1:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:09 PM
Points: 62, Visits: 97
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

Post #1368186
Posted Thursday, October 4, 2012 3:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 19, 2013 10:40 PM
Points: 116, Visits: 395
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
Post #1368253
Posted Thursday, October 4, 2012 3:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 1,939, Visits: 3,183
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
Post #1368255
Posted Thursday, October 4, 2012 5:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:09 PM
Points: 62, Visits: 97
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

Post #1368306
Posted Thursday, October 4, 2012 6:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 1,939, Visits: 3,183
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
Post #1368333
Posted Monday, October 8, 2012 6:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 19, 2013 10:40 PM
Points: 116, Visits: 395
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
Post #1369781
Posted Tuesday, October 9, 2012 4:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:21 PM
Points: 132, Visits: 557
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
Post #1370306
Posted Tuesday, October 9, 2012 8:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:09 PM
Points: 62, Visits: 97
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?
Post #1370406
Posted Thursday, October 11, 2012 6:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:21 PM
Points: 132, Visits: 557

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
Post #1371402
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse