1

Is there an issue with SPLIT(JOIN()) functions in SSRS 2012?

Here's why I ask...

I've just set up an SSRS 2012 server. I have an existing report I built in SSRS 2008 R2, which pulls from a 2005 database. I created a new project in MVS 2010 and added the existing rdl.

When I preview the report, the performance is at least 5 times worse than it is when I preview it in MVS 2008. I ran a trace and found that it took quite a while for SSRS to even execute the SP. Once it did, it rendered quickly.

I was trying to think of something that might slow down the SP's execution. The only thing I came up with is that I have a lot of multi-valued parameters I pass into the SP using SPLIT(JOIN()) functions. Have those been replaced by something new in 2012? If not, I don't even know where to start looking for the problem. My initial google searches have turned up nothing.

Has anyone experienced this problem or know of a list of things that worked well in 2008 but not so well in the new version?

I hope this question isn't too vague. Thanks for reading!

EDIT: I feel silly. I just traced the 2008 report execution and it turns out it does the same thing, which I never noticed before. The rendering is really quick after the SP shows up in Profiler. So...I have no clue what the problem could be. Any help would be GREATLY appreciated!

Anna
  • 91
  • 10
  • Why are you doing a split join in the first place? In my experience with SSRS, the ultimate destroyer of speed is overuse of SSRS functions when they are not needed or could be done by: 1. SQL instead 2. A bit of VB.NET in code instead. I have seen performance go from a few seconds to a few minutes in lots of cases when I used functions a lot. Can you elaborate a little on why you need to do the functions? Also I have seen many times debugging a piece of code is slower on a dev machine than an actual server for functions as well. – djangojazz Jun 11 '13 at 22:43
  • I have 8 multi-value parameters, all of which need to be passed into main stored procedure. I don't know how to pass in a set of selections without using SPLIT JOIN. I'm sorry to say I'm a luddite when it comes to VB.NET. I remember seeing something about defining custom code to accomplish this, but I didn't realize the performance would be so different, so I didn't bother learning to do it. Is that what you're referring to? – Anna Jun 11 '13 at 23:00
  • I am C# more than VB.NET but generally you can do what you need with mult value with a predicate like 'where column in (@multiValue)'. I am not certain if this will work for procs or functions but it does with queries. – djangojazz Jun 11 '13 at 23:29
  • You need a SPLIT JOIN (or .NET code) to turn the multi-value array into a comma delimited string. An IN won't work without doing that. – Anna Jun 11 '13 at 23:41
  • 1
    It will work a with a select query, I do it all the time with multi value parameters in my SSRS. When I set up a multi value parameter, Users, like so: select 'John' as User union select 'Brett' union select 'Bill' and set that up to a parameter that 'gets values from a query'. I can then in my main dataset do this: select * from users where user in (@Users). – djangojazz Jun 12 '13 at 00:25
  • Your SP might be victim of a terrible execution plan due to parameter sniffing which is a performance killer. See [my explanation here](http://stackoverflow.com/questions/16924144/why-does-the-2nd-t-sql-query-run-much-faster-than-the-first-when-called-by-repor/16929881#16929881) – Chris Latta Jun 12 '13 at 03:02
  • @djangojazz - I thought that would only give you the first item in the array... I will try it and see if it helps. Thanks! – Anna Jun 12 '13 at 23:46
  • @ChrisLätta - That occurred to me yesterday and I made the modifications. It helped a bunch but would it explain the difference between 2008 and 2010? – Anna Jun 12 '13 at 23:49
  • It helped to get the rdl deployed out to the reporting server and view it in the browser rather than the preview pane in VS. Dev server performance aside, previewing the report in VS 2010 is a heck of a lot slower than it is in 2008 apparently... – Anna Jun 12 '13 at 23:50
  • @Anna - I discovered it by accident one day a few years back. SSRS while similar to the TSQL language is an offshoot of it and interprets things differently. While this is illegal in TSQL in SSMS 'where thing in (@things)' as you need to specify the array like ('thing1','thing2'); in SSRS it is perfectly legit. I often take problematic procs and functions and sometimes tweak them in SSRS differently with a select statement instead. – djangojazz Jun 13 '13 at 14:35
  • @djangojazz - Wow... I removed all the SPLIT(JOINS()) and it you're right - it is much faster now. I knew they slowed things down but I didn't think there'd be 'that' much of a change. When I developed this in SSRS 2008 I read (in many places) that the only way to pass multi-value parameters from place to place was using SPLIT JOINs, and that was the only way I could get things to work right. But it works without them now! Maybe a 2012 change? – Anna Jun 17 '13 at 20:49
  • Also, for what it's worth, you can pass the parameter into an SP without using the JOIN in the parameter expression, but you still need to run the delimited list through a UDF within the SP to transpose it into table form. Thanks! – Anna Jun 17 '13 at 20:57
  • np Anna, glad it works. I think they made a change in 2008R2, I honestly don't recall much of 2008 as I spent the majority of my time learning 2008R2 and up. In general just remember functions kill SSRS with speed. It has something to do with how SSRS evaluates them at runtime in the language. It is very inefficient. So as a general guideline I try to do as much heavy lifting as possible in SQL or my dataset obtaining method. And functions I stick with simple stuff like IIF(Fields!thing.value > 10, "Red", "Black") for field colors and other simple graphical stuff. – djangojazz Jun 17 '13 at 21:17
  • I marked an answer just so you can mark this complete for upkeep. SSRS is a great tool but it has some really weird curveballs that come up that MS either does not want to address or is creating something else instead for them. Most notably hover over reporting which SSRS completely lacks but you can use Javascripting for a little bit. – djangojazz Jun 17 '13 at 21:20

3 Answers3

1

I would just stick with a predicate like: 

Where thing in (@Sets)

Where the 'Sets' variable could be from another dataset I created obtained from SQL like:

Select 'Brett' as Name
Union
Select 'Anna'
Union
Select 'John'
Union
Select 'Jenny'

Simple choose to get the data for sets from 'get data from a dataset'. Once the variable is set SQL 2008R2 and higher should do the lifting for you with figuring out the clause of the predicate in expression of Where thing in (@Sets) actually translates to:

Where thing in ('Brett', 'Anna', 'John', 'Jenny')
djangojazz
  • 14,131
  • 10
  • 56
  • 94
1

The primary solution to speeding SSRS reports and decreasing server load is to cache the reports. If one does this (either my preloading the cache at 7:30 am for instance) or caches the reports on-hit, one will find massive gains in load speed.

Please note that I do this daily and professionally and am not simply waxing poetic on SSRS

Caching in SSRS http://msdn.microsoft.com/en-us/library/ms155927.aspx

Pre-loading the Cache http://msdn.microsoft.com/en-us/library/ms155876.aspx

If you do not like initial reports taking long and your data is static i.e. a daily general ledger or the like, meaning the data is relatively static over the day, you may increase the cache life-span.

Finally, you may also opt for business managers to instead receive these reports via email subscriptions, which will send them a point in time Excel report which they may find easier and more systematic.

You can also use parameters in SSRS to allow for easy parsing by the user and faster queries. In the query builder, type IN(@SSN) under the Filter column that you wish to parameterize, you will then find it created in the parameter folder just above data sources in the upper left of your BIDS GUI. [If you do not see the data source section in SSRS, hit CTRL+ALT+D.

See a nearly identical question here: Performance Issuses with SSRS

Community
  • 1
  • 1
Bryan
  • 3,271
  • 2
  • 15
  • 30
0

Create a UDF which will take a comma (or some other delimiter you want to use) separated list, and return a table you can join on.

https://blogs.msdn.microsoft.com/amitjet/2009/12/11/convert-comma-separated-string-to-table-4-different-approaches/

Then you can take set up a parameter in your sproc such as @TheList varchar(max)

You should then be able to use it in a JOIN, Use it to create a temp table and then join on that in your query, or use it as a sub-select.

We use this quite often, and found that if you are primarily using values which are Integers, then the returned table should be a table of INT's to increase performance.

Pseudo example:

declare @TheList varchar(max)

set @TheList = ('1,2,3,4,5,6,7,8')

select *
from dbo.MyRecords r
join dbo.udf_CreateArrayTable(@TheList) at on r.RecID = at.RecID
D.S.
  • 1,413
  • 2
  • 16
  • 26