如何优化跨服务器T-SQL存储过程实现高效增量同步图片数据

问题背景 #

现有T-SQL存储过程用于跨服务器同步图片数据，仅向目标表插入不存在的新记录，要求每分钟执行一次。但源表存有200万+图片数据，当前存储过程即使无新数据需要同步时，仍会触发全表扫描，单次执行耗时超1小时22分，无法满足定时执行的业务需求。源表包含自增列acc_image_id与created_date字段，目标表已有insert_date字段且允许新增列，需基于自增列最大值或日期筛选实现高效增量同步。

源表Schema #

CREATE TABLE [dbo].[acc_image]
(
    [acc_image_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [acc_id] [int] NULL,
    [image_type_id] [int] NOT NULL,
    [data_format] [varchar](10) NOT NULL,
    [label] [varchar](50) NOT NULL,
    [description] [varchar](255) NULL,
    [image_width] [smallint] NULL,
    [image_height] [smallint] NULL,
    [image_color_depth] [tinyint] NULL,
    [image_thumbnail] [image] NOT NULL,
    [data] [image] NOT NULL,
    [notes] [text] NULL,
    [acc_specimen_id] [int] NULL,
    [acc_slide_id] [int] NULL,
    [include_in_report] [char](1) NOT NULL,
    [include_in_internet] [char](1) NOT NULL,
    [created_date] [datetime] NOT NULL,
    [row_version] [timestamp] NOT NULL,
    [report_section_number] [smallint] NULL,
    [external_notes] [text] NULL,
    [source_filename] [varchar](255) NULL,
    [page_count] [int] NOT NULL,
    [include_as_attachment] [char](1) NOT NULL,
    [sort_order] [smallint] NULL,
    [acc_parent_image_id] [int] NULL,
    [created_by_id] [int] NULL,
    [annotation] [text] NULL,
    [specimen_results_enabled] [char](1) NOT NULL,
    [dis_imageserver_id] [int] NULL,
    [external_slide_image_id] [varchar](80) NULL,
    [external_report_image_id] [varchar](80) NULL,
)   

目标表Schema #

CREATE TABLE [dbo].[acc_image]
(
    [acc_image_id] [int] NOT NULL,
    [acc_id] [int] NULL,
    [image_type_id] [int] NOT NULL,
    [data_format] [varchar](10) NOT NULL,
    [label] [varchar](50) NOT NULL,
    [description] [varchar](255) NULL,
    [image_width] [int] NULL,
    [image_height] [int] NULL,
    [image_color_depth] [tinyint] NULL,
    [image_thumbnail] [image] NOT NULL,
    [data] [image] NOT NULL,
    [image_guid] [uniqueidentifier] NULL,
    [created_date] [datetime] NOT NULL,
    [row_version] [varbinary](12) NOT NULL,
    [sort_order] [int] NULL,
    [insert_date] [datetime] NOT NULL,
    [updated_date] [datetime] NULL,
) 

优化方案 #

1. 创建同步状态记录表（推荐） #

持久化每次同步的最大acc_image_id，避免反复查询目标表最大值的开销：

CREATE TABLE dbo.SyncStatus
(
    SyncTableName VARCHAR(100) PRIMARY KEY,
    LastSyncMaxId INT NOT NULL,
    LastSyncTime DATETIME NOT NULL DEFAULT GETDATE()
)

-- 初始化同步状态
INSERT INTO dbo.SyncStatus (SyncTableName, LastSyncMaxId)
VALUES ('acc_image', 0)

2. 修改存储过程实现增量同步 #

核心逻辑：仅扫描源表中acc_image_id大于上次同步最大值的记录，结合业务过滤条件实现精准增量同步，同步完成后更新状态表。

ALTER PROCEDURE [dbo].[get_image]
AS
BEGIN
    DECLARE @RecCt AS INT = 0
    DECLARE @LastMaxId INT
    DECLARE @CurrentMaxId INT

    BEGIN TRY
        -- 获取上次同步的最大acc_image_id
        SELECT @LastMaxId = LastSyncMaxId
        FROM dbo.SyncStatus
        WHERE SyncTableName = 'acc_image'

        -- 获取源表当前最大ID，快速判断是否有新数据
        SELECT @CurrentMaxId = MAX(acc_image_id)
        FROM [ARKPPTEST\POWERPATHTEST].[Powerpath_Test].[dbo].acc_image

        -- 无新数据直接退出
        IF @CurrentMaxId <= @LastMaxId
        BEGIN
            SET @RecCt = 0
            GOTO EndProc
        END

        -- 增量插入符合条件的新记录
        INSERT INTO connect_onprem.dbo.acc_image (
            acc_image_id, acc_id, image_type_id,    
            data_format, label, description, 
            image_width, image_height, 
            image_color_depth, image_thumbnail, 
            data, created_date, row_version, sort_order,
            insert_date
        )
        SELECT 
            src.acc_image_id, a.id, src.image_type_id, 
            src.data_format, src.label, src.description, 
            src.image_width, src.image_height,
            src.image_color_depth, src.image_thumbnail,
            src.data, src.created_date, src.row_version, src.sort_order,
            GETDATE() AS insert_date
        FROM 
            [ARKPPTEST\POWERPATHTEST].[Powerpath_Test].[dbo].acc_image src
        INNER JOIN 
            [ARKPPTEST\POWERPATHTEST].[Powerpath_Test].[dbo].acc_slide ass ON src.acc_slide_id = ass.id
        INNER JOIN 
            [ARKPPTEST\POWERPATHTEST].[Powerpath_Test].[dbo].acc_specimen s ON ass.acc_specimen_id = s.id
        INNER JOIN 
            [ARKPPTEST\POWERPATHTEST].[Powerpath_Test].[dbo].accession_2 a ON a.primary_specimen_id = s.id
        WHERE 
            src.acc_image_id > @LastMaxId
            AND a.acc_type_id <> 134
            AND a.status_final = 'Y'
            -- 双重校验：防止同步中断后重复插入
            AND NOT EXISTS (
                SELECT 1 
                FROM connect_onprem.dbo.acc_image tgt 
                WHERE tgt.acc_image_id = src.acc_image_id
            )
        ORDER BY src.acc_image_id

        SET @RecCt = @@ROWCOUNT

        -- 更新同步状态
        IF @RecCt > 0 OR @CurrentMaxId > @LastMaxId
        BEGIN
            UPDATE dbo.SyncStatus
            SET LastSyncMaxId = @CurrentMaxId,
                LastSyncTime = GETDATE()
            WHERE SyncTableName = 'acc_image'

            INSERT INTO connect_onprem.dbo.ErrorLog (
                UserName, ErrorNumber, ErrorState, 
                ErrorSeverity, ErrorLine, ErrorProcedure, ErrorMsg, ErrorDateTime
            )
            VALUES (
                'RecTrack', @RecCt, 0, 0, 0, 'get_image', 
                'connect_onprem.dbo.acc_image Inserted Records', GETDATE()
            )
        END

EndProc:
    END TRY
    BEGIN CATCH
        INSERT INTO connect_onprem.dbo.ErrorLog (
            UserName, ErrorNumber, ErrorState, ErrorSeverity, 
            ErrorLine, ErrorProcedure, ErrorMsg, ErrorDateTime
        )
        VALUES (
            SUSER_SNAME(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), 
            ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE(), GETDATE()
        )
    END CATCH
END

3. 添加索引提升查询效率 #

• 源表：为关联字段创建覆盖索引，避免键查找：

CREATE NONCLUSTERED INDEX IX_acc_image_acc_slide_id 
ON [ARKPPTEST\POWERPATHTEST].[Powerpath_Test].[dbo].acc_image(acc_slide_id)
INCLUDE (acc_image_id, image_type_id, data_format, label, description, image_width, image_height, image_color_depth, image_thumbnail, data, created_date, row_version, sort_order)

• 目标表：确保acc_image_id为主键或唯一索引：

ALTER TABLE connect_onprem.dbo.acc_image ADD CONSTRAINT PK_acc_image PRIMARY KEY (acc_image_id)

• 关联表：为关联字段添加索引，加速跨表连接：

-- accession_2表：primary_specimen_id覆盖索引
CREATE NONCLUSTERED INDEX IX_accession_2_primary_specimen_id 
ON [ARKPPTEST\POWERPATHTEST].[Powerpath_Test].[dbo].accession_2(primary_specimen_id)
INCLUDE (id, acc_type_id, status_final)

-- acc_slide表：acc_specimen_id覆盖索引
CREATE NONCLUSTERED INDEX IX_acc_slide_acc_specimen_id 
ON [ARKPPTEST\POWERPATHTEST].[Powerpath_Test].[dbo].acc_slide(acc_specimen_id)
INCLUDE (id)

可选优化：日期+ID双重筛选 #

若源表created_date能精准反映记录创建时间，可添加日期筛选作为双重保险，适合ID可能不连续的场景：

1. 扩展同步状态表：

ALTER TABLE dbo.SyncStatus ADD LastSyncDate DATETIME NOT NULL DEFAULT '1900-01-01'

2. 修改存储过程筛选条件：

WHERE 
    src.created_date > @LastSyncDate
    AND src.acc_image_id > @LastMaxId
    AND a.acc_type_id <> 134
    AND a.status_final = 'Y'

3. 同步完成后更新LastSyncDate为当前时间。

内容的提问来源于stack exchange，提问作者dba_gal