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

Article translations Article translations
Article ID: 942982 - View products that this article applies to.
Expand all | Collapse all

On This Page

SYMPTOMS

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.

CAUSE

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.

WORKAROUND

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)

STATUS

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

MORE INFORMATION

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

Properties

Article ID: 942982 - Last Review: October 23, 2007 - Revision: 1.1
APPLIES TO
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
Keywords: 
kbexpertiseadvanced kbtshoot kbprb KB942982

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com