Microsoft SQL Server

Linked Server and SSRS report performance

Version - SQL Server 2014
Client based in – US

The Challenge

Recently we had to use linked servers to fetch data for some SSRS reports. Our initial approach degraded the performance and the reports were getting loaded very slow which was not at all acceptable. We had to come up with a solution that would use linked servers yet decrease the report loading time.

The Solution

We initially opted to fetch the required report data using linked servers straight away in the joins to local DB objects, just as we would do for DB objects of the local server.  The thing that happened in this scenario is that even if the final data of our query was filtering the results with some filter clauses, SQL server would fetch all of the data from the remote server and then apply the filters locally. This required more resources and time. No doubt, we had some serious performance issues. The reports were too slow. Overcoming this and making reports load faster was a challenge for us.

We tried a common alternative to this – Open Query. But when queries get complex, as was the case with us, this solution becomes difficult to maintain. Also, we had to redundantly use Open Query for several stored procedures. As the query to the linked server was executed several times for the each stored procedure separately, we still did not achieve the desired report loading time. Some other feasible solution was in demand.

Next, we opted to use a global temporary table. We fetched the required data from the remote server at one time and loaded it into global temporary table and then used this global temporary table in the stored procedures. However things don’t always run smooth. When multiple users tried to access the same report at the same time, deadlocks were detected intermittently as we had to configure SSRS in a way that it fetches the datasets sequentially citing global temporary tables should be filled with data before they getting accessed in other stored procedures. 

It was high time we come up with an acceptable solution. Finally, we came up with a solution having – table variables combined with a table valued function. We made a table valued function that would return us the data required from the remote server as a table. 

We made a one time fetch to this data and loaded it into a table variable declared locally in our stored procedures as this data was being used in multiple sections of the same stored procedure. Additionally in SSRS, we removed the option for calling all the stored procedures sequentially in a single transaction and let parallelism happen. This significantly improved the performance and reports now loaded much faster. This time we had something to cherish. Reports loaded quite speedily this time. 

We achieved a great deal of client satisfaction by accomplishing this task.