Combining Results from multiple datasets in SSRS – performance comparison

Recently at a training I was asked how to combine the results from two different datasets, while I have used this function before I couldn’t recollect how much of a performance difference it made to look up within SSRS vs performing the join directly with the dataset query. In this blog I try to find out the answer and document it.

For the purpose of this blog I have created a table called customers where I will look up the address for each customer by joining the address table with the customer table on Addressid = addressed.

The above screenshot shows the core parts of the report using the Lookup function including the datasets and the expression used to fetch the city value from the Dataset2 which contains the address information.

The results for the test are mentioned below. I have the below queries which are used to measure the performance for each report by directly querying the Report Server database.

select c.name , count(*) as counts, avg( [TimeDataRetrieval] +[TimeProcessing] + [TimeRendering]) as avgtotal
from [dbo].[ExecutionLogStorage] e
inner join Catalog c
on e.ReportID = c.ItemID
where c.Name in ( 'joined','lookup')
--and TimeStart >='2015-09-07 15:50:54.713'
group by c.name

select c.name , avg([TimeDataRetrieval]) as datafetch , avg([TimeProcessing]) as process , avg([TimeRendering]) as rendering
from [dbo].[ExecutionLogStorage] e
inner join Catalog c
on e.ReportID = c.ItemID
where c.Name in ( 'joined','lookup')
--and TimeStart >='2015-09-07 15:50:54.713'
group by c.name

As you can see after three executions you can see there is a clear difference between the execution time for Joined Report vs lookup report. And this has been validated by over 20 report executions to be remarkable consistent. Counter intuitively the Data fetch time for the Joined Report is significantly higher than that of the lookup report however you will notice that rendering time for lookup report is higher on average when compared to joined report this part is as expected. The results are also consistent regardless of number of rows lookup performs better even if the table has 1000 rows for 64000 rows.