SQL Server Stored Procedure Optimize Linked Server Query

When calling to a database on a linked server, create a temporary table variable to store results from the remote table(s), then join the temporary table to your local database table(s). This is much more efficient than joining tables between databases. To use the stored procedure in .NET dataset table adapter, you must use table variable, not temp table.
code example. In this example the linked server has some employee information and a local table has additional employee information.

CREATE PROCEDURE [dbo].[spLinkedServerQuery]
AS
BEGIN
  SET NOCOUNT ON;
        -- declare a temporary table variable
	Declare @temptablevar table
	(
		ID int not null,
		organizationlevel int,
		jobtitle nchar(1),
		salary int
	);

	-- pull data from linked server into temporary table variable, using joins and where statement as necessary
	insert into @temptablevar (ID, organizationlevel, jobtitle, salary)
	select e.ID, e.organizationlevel, e.jobtitle, s.salary
	from linkedserver.HR.dbo.employee e 
	join linkedserver.HR.dbo.salary s on s.ID = e.ID
	where
	s.salary > 100000;

	-- select data from temporary table variable and join to local table. Include where statement as necessary.
	select t.ID, t.organizationlevel, t.jobtitle, t.salary, lt.vacationhours, lt.sickleavehours
	from @temptablevar as t
	join localtable as lt on lt.ID = t.ID
	where lt.vacationhours > 40 and lt.sickleavehours > 100
END

Leave a Comment