SQLSERVER链接服务查询快，更新慢问题(未能创建可接受的游标后续)

最新推荐文章于 2021-03-13 12:22:30 发布

转载最新推荐文章于 2021-03-13 12:22:30 发布 · 304 阅读

文章标签：

#数据库 #java

本文探讨了在SQL中更新特定表数据时的性能优化策略，包括使用内连接减少查询复杂性、避免全表扫描、利用IDENTITY属性提高效率等技巧。通过分析不同更新语句的执行时间，实现更快速的数据更新。

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