0

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?

Timothy
  • 1,198
  • 3
  • 10
  • 30
  • 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. http://sqlperformance.com/2012/07/t-sql-queries/split-strings You could also eliminate the temp table since the function returns a table. – Sean Lange Jan 20 '15 at 15:34
  • This article offers several suggestions: http://www.sommarskog.se/query-plan-mysteries.html – Tab Alleman Jan 20 '15 at 16:15
  • 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 Jan 20 '15 at 17:32
  • Is there a way to bypass the parameter sniffing with multiple values? @Sourav_Agasti – Timothy Jan 20 '15 at 18:10
  • Timothy please check my answer – SouravA Jan 21 '15 at 18:02

1 Answers1

1

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.

SouravA
  • 5,147
  • 2
  • 24
  • 49