SSRS Report Speed Problem. How to increase the speed of your SSRS Reports.

Backstory

I tried to show my colleague one of my new reports, but when he tried to open the report it wouldn't load. He could enter the parameters just fine, but the report itself just hung at the "Loading" screen.
Tried the same on my computer and the same thing happened. When i ran the report on the server itself either from the browser or from Report Builder everything worked fine.
My first thought was that this was a speed issue so i started investigating. The problem in this case turned out to be a IE11 problem (setting the report site to comparability mode fixed that), but while I was investigating the issue I found something else that was very interesting.

The Problem

The issue is something called SQL Parameter sniffing and you can read more about it here, Essentially the problem is with the way SQL tries create a execution plan based on the size of the result, but when the query uses parameters the result may vary in size and the execution plan may be far from optimal.
There are several ways to get around this, the easiest way seems to be to add "optimize for unknown" to the query but that requires SQL 2008 R2 or above and may cause a problem for some. But i found a simple workaround while browsing stack exchange that seems to work just fine.

The Solution

The trick is to double up the parameters. Instead of using the @SSRS parameters directly you map the SSRS parameters to SQL Parameters and use the SQL parameters in the query.
I couldn't find a way to do this with multi-value parameters, but it looks like you only need to do this to some of the parameters to bypass the problem.

In the SQL snippit under i take the two SSRS parameters (@Timeperiod and @DefaultTimezone) and create two SQL parameters for them (@TimeperiodVar and @DefaultTimezoneVar) and tie them together. Then I replace the ssrs parameters with the new SQL parameters everywhere they're used in the query. 

DECLARE @TimeperiodVar DATETIME DECLARE @DefaultTimezoneVar INT
--set @AdvertisementIDVar = @AdvertisementID  SET @TimeperiodVar = @Timeperiod SET @DefaultTimezoneVar = @DefaultTimezone; 

The Result

Have a look at the Execution speed on the screenshot under(The Total time is in seconds and the rest is in milliseconds.).

The "OSD Dashboard" is the standard one and "OSD Dashboard Demo" has the new parameter system:

As you can see, I managed to cut the execution time for this report a lot!!
I managed to cut the SQL query time from between 22 seconds down to 0-5 seconds(yesterday it was 47+- seconds for the first one).
SSRS does some caching and so the results may vary, but around one second is what it takes when i run the query in SQL Management Studio.

So this little issue with IE11 Compability mode lead me down a path that enabled me to increase execution speed roughly 27x!

Have tested a few others reports and it seems very promising , I will defently use this on all my reports from now on.