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:
DECLARE @t2 table (c1 int primary key, c2 int)
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.
On the ServerA instance of SQL Server 2005, run the following statements to create a database in SQL Server Management Studio:
USE MASTERGOIF EXISTS (SELECT * FROM sysdatabases where name = 'LinktestTarget') DROP DATABASE LinktestTargetGOCREATE DATABASE LinktestTargetGOALTER DATABASE LinktestTarget MODIFY FILE(NAME = 'LinktestTarget', SIZE = 200MB)GOALTER DATABASE LinktestTarget MODIFY FILE(NAME = 'LinktestTarget_log', SIZE = 100MB)GO
On the ServerB instance of SQL Server 2005, run the following statements to create a linked server in Management Studio: