Performance is slow when you run a query in SQL Server 2005 that updates a table on a linked server and then joins the table to a table on the local server

Consider the following scenario:

• You have a query that updates a table on a linked server and then joins the table to a table on the local server.
• The local server is running Microsoft SQL Server 2005.
• On the local server, you run the query in SQL Server 2005.

In this scenario, performance is slow compared with the performance of the same query in Microsoft SQL Server 2000. For example, when you run the query in SQL Server 2000, the query takes less than 1 second to finish. However, when you run the query in SQL Server 2005, the query takes about 24 seconds to finish.

The query performs a Remote Scan operation on the table on the linked server. When you run the query in SQL Server 2005, the Remote Scan operation retrieves all the rows in the table. If the table contains many rows, the Remote Scan operation takes a long time to finish.

However, when you run the query in SQL Server 2000, the Remote Query operation submits the query to run on the linked server. Therefore, even if the table contains many rows, the Remote Scan operation is complete in a short time.

To work around this problem, add a UNIQUE constraint or a PRIMARY KEY constraint in the table on the local server. Add the constraint to the column that you use for the JOIN operation.

For example, you update the DECLARE statement that is mentioned in step 4 of the "More information" section to the following:

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Steps to reproduce this problem

Note The following steps assume that you have two servers that are running SQL Server 2005. One instance name is ServerA\SQL2005. The other instance name is ServerB\SQL2005.

1. On the ServerA instance of SQL Server 2005, run the following statements to create a database in SQL Server Management Studio:

   USE MASTER
   GO
   IF EXISTS (SELECT * FROM sysdatabases where name = 'LinktestTarget')
   	DROP DATABASE LinktestTarget
   GO
   CREATE DATABASE LinktestTarget
   GO
   
   ALTER DATABASE LinktestTarget MODIFY FILE
   (NAME = 'LinktestTarget', SIZE = 200MB)
   GO
   ALTER DATABASE LinktestTarget MODIFY FILE
   (NAME = 'LinktestTarget_log', SIZE = 100MB)
   GO

2. On the ServerB instance of SQL Server 2005, run the following statements to create a linked server in Management Studio:

   DECLARE @serverName nvarchar(50)
   SET @serverName = @@serverName
   
   IF EXISTS (SELECT * FROM master..sysservers WHERE srvname = 'LS')
   	EXEC master.dbo.sp_dropserver @server=N'LS', @droplogins='droplogins'
   
   EXEC sp_addlinkedserver
       @server = N'LS',
       @srvproduct = N'SQLDB',
       @provider = N'SQLOLEDB',
       @datasrc = N'ServerA\SQL2005',
       @catalog = N'LinktestTarget'
   
   EXEC sp_serveroption N'LS', N'data access', N'true'
   EXEC sp_serveroption N'LS', N'rpc out', N'true'
   EXEC sp_serveroption N'LS', N'rpc', N'true'
   EXEC sp_serveroption N'LS', N'use remote collation', N'true'
   EXEC sp_addlinkedsrvlogin
     @rmtsrvname = N'LS',
     @useself = N'true',
     @locallogin = N'sa'

3. On the ServerA instance of SQL Server 2005, run the following statement to create a table, and then populate the table with 50,000 rows of data:

   USE LinktestTarget
   GO
   CREATE TABLE [T1] (
   	[c1] [int]  NOT NULL,
   	[c2] [int] NOT NULL,
   	[c3] char (4000) NOT NULL,
   	CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED ([c1])
   WITH FILLFACTOR = 80 ON [PRIMARY] 
   ) ON [PRIMARY]
   GO
   SET NOCOUNT ON
   GO
   DECLARE @counter int
   SET @counter = 1
   
   WHILE @counter < 50000
   BEGIN
   	INSERT INTO T1 (c1, c2, c3) VALUES (@counter, @counter,'')
   	SET @counter = @counter + 1
   END
   GO
   UPDATE STATISTICS T1 (PK_t1) WITH FULLSCAN

4. On the ServerB instance of SQL Server 2005, run the following statements. Notice that the performance is slow.

   DECLARE @t2 table (c1 int, c2 int)
   INSERT INTO @t2 (c1, c2) VALUES (399990, 399990)
    
   UPDATE A
   SET A.c2 = B.c2
   FROM LS.LinktestTarget.dbo.T1 AS A 
   	JOIN @t2 as B on B.c1 = A.c1