sql - 逐行更新 SQL Table 中的数据

Question

我使用的是 SQL Server 2008 R2。我有一个来自链接服务器的 View [EmployeeMaster] 和数据库中的一个表 [EmployeeDetails]。我有一项服务，每天凌晨 12 点运行，调用一个存储过程，将表与 View 同步。即，存储过程使用 View 中的匹配行检查并更新 [EmployeeDetails] 中的每一行。我的存储过程是这样的。

CREATE PROCEDURE usp_OracleSyncUpdate
AS

CREATE TABLE #ActiveEmployees (
 RowID int IDENTITY(1, 1), 
 [EmployeeId] nvarchar(50),
)
DECLARE @NumberRecords int, @RowCount int
DECLARE @EmployeeId nvarchar(50)
        ,@EmployeeName nvarchar(50)
        ,@EmployeeLastName nvarchar(50)
        ,@EmployeeCategory nvarchar(50)
        ,@ContactNo nvarchar(50)
        ,@Email nvarchar(50)
        ,@Gender nvarchar(50)
        ,@JoiningDate DATETIME

-- into the temporary table
INSERT INTO #ActiveEmployees ([EmployeeId])
SELECT [EMPLOYEE_NUMBER]
FROM [dbo].[EmployeeMaster]
WHERE [EMPLOYEE_NUMBER] IN (
        SELECT EmployeeId
        FROM [dbo].[EmployeeDetails]
        )

-- Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
BEGIN
 SELECT @EmployeeId = EmployeeId
 FROM #ActiveEmployees
 WHERE RowID = @RowCount

 SELECT @EmployeeName = EmployeeName FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@EmployeeName)) <> (SELECT LTRIM(RTRIM([FIRST_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET EmployeeName = (SELECT LTRIM(RTRIM([FIRST_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @EmployeeLastName = EmployeeLastName FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@EmployeeLastName)) <> (SELECT LTRIM(RTRIM([LAST_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET EmployeeLastName = (SELECT LTRIM(RTRIM([LAST_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @EmployeeCategory = [Category] FROM [dbo].[EmployeeCategory] WHERE [EmployeeCategoryId] = (SELECT [EmployeeCategoryId] FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId)
 IF(LTRIM(RTRIM(@EmployeeCategory)) <> (SELECT LTRIM(RTRIM([JOB_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET [EmployeeCategoryId] = (SELECT [EmployeeCategoryId] FROM [dbo].[EmployeeCategory] WHERE [Category] = (SELECT LTRIM(RTRIM([JOB_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)) 
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @Email = Email FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@Email)) <> (SELECT LTRIM(RTRIM([EMAIL_ADDRESS])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET Email = (SELECT LTRIM(RTRIM([EMAIL_ADDRESS])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @Gender = Gender FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@Gender)) <> (SELECT LTRIM(RTRIM([GENDER])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET Gender = (SELECT LTRIM(RTRIM([GENDER])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @JoiningDate = JoiningDate FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@JoiningDate)) <> (SELECT LTRIM(RTRIM([DATE_OF_JOINING])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET JoiningDate = (SELECT LTRIM(RTRIM([DATE_OF_JOINING])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END

 SET @RowCount = @RowCount + 1
END

-- drop the temporary table
DROP TABLE #ActiveEmployees

它的工作和更新列。现在该表有 26196 条记录，执行此存储过程大约需要 23:56 分钟。以后记录会越来越多，执行时间会越来越长。

1. 有什么方法可以缩短执行时间(建议对查询进行一些优化)？
2. 或者有什么其他方法可以同步这两个表吗？
3. 或者有什么方法可以计算 future 的执行时间，我可以根据它设置连接超时吗？

这是我的最终代码

    alter PROCEDURE usp_OracleSyncUpdate
AS
    UPDATE ed
    SET ed.[EmployeeName] = LTRIM(RTRIM(em.FIRST_NAME)),
        ed.EmployeeLastName = LTRIM(RTRIM(em.LAST_NAME)), 
            ed.EmployeeCategoryId = ec.EmployeeCategoryId,
            ed.Email = isnull(ed.Email,em.[EMAIL_ADDRESS]),
            ed.[ContactNo] = isnull(ed.[ContactNo],em.ContactNo),
            ed.Gender = em.Gender,
            ed.JoiningDate = em.[DATE_OF_JOINING]
        FROM dbo.EmployeeDetails ed
        INNER JOIN [EmployeeMaster] em ON ed.EmployeeId = em.[EMPLOYEE_NUMBER]
        INNER JOIN dbo.EmployeeCategory ec ON ec.Category = em.[JOB_NAME]

感谢@marc_s

Answer 1

摆脱 RBAR(逐行)处理 - 关系数据库在集合数据中工作 - 使用单个基于集合 UPDATE 语句，您就完成了。

类似的东西:

CREATE PROCEDURE usp_OracleSyncUpdate
AS
    CREATE TABLE #ActiveEmployees (
     RowID int IDENTITY(1, 1), 
     [EmployeeId] nvarchar(50),
    )

    -- into the temporary table
    INSERT INTO #ActiveEmployees ([EmployeeId])
      SELECT [EMPLOYEE_NUMBER]
      FROM [dbo].[EmployeeMaster]
      WHERE [EMPLOYEE_NUMBER] IN (SELECT EmployeeId
                              FROM [dbo].[EmployeeDetails])

    UPDATE dbo.EmployeeDetails
    SET ed.EmployeeName = LTRIM(RTRIM(ae.FIRST_NAME)),
        ed.EmployeeLastName = (SELECT LTRIM(RTRIM(ae.LAST_NAME)), 
            ed.EmployeeCategoryId = ec.EmployeeCategoryId,
            ed.Email = ae.Email,
            ed.Gender = ae.Gender,
            ed.JoiningDate = ae.JoiningDate
        FROM dbo.EmployeeDetails ed
        INNER JOIN #ActiveEmployees ae ON ed.EmployeeId = ae.EmployeeId
        INNER JOIN dbo.EmployeeCategory ec ON .......
        WHERE EmployeeId = @EmployeeId

我不知道 EmployeeCategory 表是如何链接到其他表的 - 您需要在 INNER JOIN JOIN 条件 那里的行。

另外，很可能，您还可以完全删除那个临时表，直接从 EmployeeMaster 更新 EmployeeDetails 作为保存的第二步内存和处理时间。