SQLSERVER連結服務查詢快，更新慢問題(未能建立可接受的遊標後續)

 CREATE TABLE #t1
    (
      id INT IDENTITY(1, 1)
             NOT NULL ,
      BH INT
    )
INSERT  INTO #t1
        SELECT  492417420  
SELECT  *
FROM    #t1
                         
              
DROP TABLE  #t1     
                       
 --更新一條資料 ，實際上更新影響0行                     
 --4秒                     
UPDATE  test.[sgoa_data].[dbo].[nsb]
SET     flag = 1  FROM
test.[sgoa_data].[dbo].[nsb] AS a    JOIN (SELECT B.bh FROM    test.[sgoa_data].[dbo].[nsb] AS B ,
        #t1 AS C  
WHERE   B.bh = C.bh) E
 ON   a.bh = E.bh  

--25秒
UPDATE  test.[sgoa_data].[dbo].[nsb]
SET     flag = 1  FROM
test.[sgoa_data].[dbo].[nsb] AS a   ,
        #t1 AS b
WHERE   a.bh = b.bh

 

SELECT A.* FROM       test.[sgoa_data].[dbo].[nsb] AS a    JOIN (SELECT B.* FROM    test.[sgoa_data].[dbo].[nsb] AS B ,
        #t1 AS C
WHERE   B.bh = C.bh) E
        
ON   a.bh = E.bh  

 

 

 

問題處理

--0秒

UPDATE  a
SET     flag = 1
FROM    LINK_39.SGOA_DATA.dbo.NSB AS a
        JOIN ( SELECT  DISTINCT
                        b.bh
               FROM     LINK_39.SGOA_DATA.dbo.NSB AS B ,
                        #t1 AS C
               WHERE    B.bh = C.bh
             ) E ON a.bh = E.bh