时间戳

首页 > 解决方案 > 动态 SQL 从表中执行大量行

sql-server - 动态 SQL 从表中执行大量行

问题描述

我有一个包含大量行的表，我希望通过动态 SQL 执行。它们基本上是存在检查和插入语句，我想将数据从一个生产数据库迁移到另一个 - 我们正在合并事务数据。我正在尝试找到执行行的最佳方法。

我一直在发现将所有行相互附加的合并方法对此效率不高，特别是当一次执行的行数大于〜100时。

假设源表的结构是任意的，如下所示：

CREATE TABLE [dbo].[MyTable]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[DataField1] [int] NOT NULL,
[FK_ID1] [int] NOT NULL,
[LotsMoreFields] [NVARCHAR] (MAX),
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([ID] ASC)
)

CREATE TABLE [dbo].[FK1]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [int] NOT NULL, -- Unique constrained value
CONSTRAINT [PK_FK1] PRIMARY KEY CLUSTERED ([ID] ASC)
)

另一个要求是我正在跟踪源表 PK 与目标 PK 以及是否发生插入或我是否已经将该行迁移到目标。为此，我在另一个表中跟踪迁移的行，如下所示：

CREATE TABLE [dbo].[ChangeTracking]
(
[ReferenceID] BIGINT IDENTITY(1,1),
[Src_ID] BIGINT,
[Dest_ID] BIGINT,
[TableName] NVARCHAR(255),
CONSTRAINT [PK_ChangeTracking] PRIMARY KEY CLUSTERED ([ReferenceID] ASC)
)

我现有的方法是执行一些由存储过程生成的动态 sql。存储过程执行 PK 查找，因为源系统对表 [dbo].[FK1] 具有不同的 PK 值。例如

IF NOT EXISTS (<ignore this existence check for now>)
BEGIN
    INSERT INTO [Dest].[dbo].[MyTable] ([DataField1],[FK_ID1],[LotsMoreFields]) VALUES (333,(SELECT [ID] FROM [Dest].[dbo].[FK1] WHERE [Name]=N'ValueFoundInSource'),N'LotsMoreValues');
    INSERT INTO [Dest].[dbo].[ChangeTracking] ([Src_ID],[Dest_ID],[TableName]) VALUES (666,SCOPE_IDENTITY(),N'MyTable'); --666 is the PK in [Src].[dbo].[MyTable] for this inserted row
END

因此，当您拥有一百万个这些时，它并不快。

有推荐的执行此操作的高性能方式吗？

标签： sql-servertsql

解决方案

听起来您像前端开发人员一样进行这些更新，方法是检查每一行是否匹配，然后进行插入。使用单个查询进行插入会更有效率。下面是一个查找 tblNewClient 表中但不在 tblClient 表中的名称的示例：

INSERT INTO tblClient
        ( [Name] ,
          TypeID ,
          ParentID 
          )
SELECT nc.[Name] ,
    nc.TypeID ,
    nc.ParentID
FROM tblNewClient nc
    LEFT JOIN tblClient cl
        ON nc.[Name] = cl.[Name]
WHERE cl.ID IS NULL;

这将比做RBAR 更有效（通过痛苦的行来行）。

推荐阅读