SSRS Parameter Sniffing with multiple values

Question

I have a sql query that takes about 15 seconds to run in ssms, but when I run it in ssrs, it takes 5-7 minutes. From everything I've read this appears to be from "parameter sniffing" so I've declared variables inside the query to bypass this, however I've still running into issues with multiple parameters.

I tried this: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ee0e8b0c-44ae-4558-9b7e-d287dacfb8a5/multi-value-parameter-sniffing?forum=sqlreportingservices

With:

My passed in param is: @Places

create table #places (place varchar(50))
insert into #places select Val from dbo.parseValues(@Places,',')

select * from mytable m
inner join #places p on p.place = m.place
drop table #places

and this works if I only select one place when running the report, but otherwise it throws an error that says:

For more information about this error navigate to the report server on the local server machine, or enable remote errors ---------------------------- Query execution failed for dataset 'dataset1'. (rsErrorExecutingCommand) ---------------------------- An error has occurred during report processing. (rsProcessingAborted)

The ParseValues function comes from http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

Does anybody have any other ideas?

Of all the possible ways to split strings in sql the while loop is the worst performer. Check out this article for a number of other options. sqlperformance.com/2012/07/t-sql-queries/split-strings You could also eliminate the temp table since the function returns a table. — Sean Lange
This article offers several suggestions: sommarskog.se/query-plan-mysteries.html — Tab Alleman
The thing is SSRS does not build the multi value string as 'Val1, Val2, Val3'. It actually treats it like an object which sends value as 'Val1', 'Val2', 'Val3'. Thus the parsing fails. — SouravA
Is there a way to bypass the parameter sniffing with multiple values? @Sourav_Agasti — Timothy

SouravA SouravA · Accepted Answer · 2015-01-21T10:38:33

As I added in comment, 'SSRS 2008` does not implicitly convert the multiple values into a string. So before passing it to the parsing function(which accepts a comma separated string), you need to convert it into comma separated string. Good part is, that is easy.

Create a hidden parameter(say @StringPlaces) in your report. In the "Available Values" section, put the below expression:

=Join(Parameters!Places.Value, ",")

The Join function prepares the comma delimited string. Now all you need to do is pass this newly prepared parameter to the parsing function, which splits it into rows.

parseValues(@StringPlaces,',')

Have the parsing function return a table so that you don't need the temp table anymore.

Note SQL Server 2012 onwards, you won't need to convert the set of values into a string. The engine does it for you. So had your code been running on SQL 2012/2014, it would have worked like a charm.

SSRS Parameter Sniffing with multiple values

1 Answers