Performance impact of Lookup in SSRS Report Builder

Question

I've recently hit a bottleneck situation in which if I keep a current version of a query inside a report (designed in Report Builder SSRS 2008) it will generate loading times of up to 15 minutes for a report with specific parameters. This JOIN represents a sub-query which I JOIN to the main query on a non-indexed column. Let's call this sub-query "Units".

If I delete the "Units" JOIN from the SQL Query and set it up as a separate Data Set inside the report, linking it using the SSRS Lookup function (same as the JOIN in SQL) to the Main Data Set (Query), the report runs smoothly, in under a minute (Approximately 3 to 5 miliseconds).

Keeping in mind that the "Units" sub-query, when ran separately runs in under 5 milliseconds for the same parameters that previously took 15 minutes, but when it is attached to the Main query causes severe performance issues.

Is there a clear benefit on doing this type of separation or should I just investigate further on how to improve the query? What are the performance benefits/downsides of using lookup versus improving the current query performance.

My concern is that this is a situational improvement and this will not represent a long term solution. I've used this alternative in the past to avoid tweaking the query and it did not backfire, but I do not fully understand the performance implications of using this workaround.

Thanks, Radu.

Answer 1

There are a lot of things that could be causing the performance issues but here's a few simple things that might get the dataset back up to speed again with very little effort.

1. Parameter sniffing

You mention with specific parameters, if you mean that the query only performs badly with some parameters and performs well with other parameters, and assuming that the size of the data does not vary significantly based on these parameters then it's likely a parameter sniffing issue. This is caused by a query plan that was generated based on once set of parameters that is not suitable for other parameters. The easiest way to prove this is to simply add option (recompile) to the end of the query. This is not a permanent fix but it will force a new query plan to be generated. If you see an instant improvement then parameter sniffing is the most common cause.

2. Refactor dataset query

The other option is to redesign your query. I don't know what you query looks like but if we take a simple example based on the information you posted...

If you query looks something like..

SELECT * FROM tableA a
    JOIN (SELECT * FROM tableB WHERE someValue=someOtherValue) b
        ON a.FieldA = b.FieldB

then you could refactor it by putting the subquery into a temp table and joining to that, something like

SELECT * 
    INTO #t
    FROM tableB WHERE someValue=someOtherValue

SELECT * FROM tableA a
    JOIN #t b
        ON a.FieldA = b.FieldB

This is an approach I often take and it can get round exactly these types of performance issues.