用于表到表插入的 SSIS 与(仅限 SQL)INSERT INTO () SELECT FROM 方法 [英] SSIS for table-to-table inserts vs. (SQL only) INSERT INTO () SELECT FROM approach

问题描述

我目前正在将大量记录从一张表转移到另一张表，并在此过程中进行总结.所以，我有一个这种通用格式的 SQL:

I am currently transferring a large amount of records from one table to another, summarizing in the process. So, I have a SQL in this general format:

INSERT INTO TargetTable
   (Col1,
   Col2,
   ...
   ColX)
   Tot
   )
SELECT
   Col1,
   Col2,
   ...
   ColX
   SUM(TOT)
FROM 
   SourceTable
GROUP BY
   Col1,
   Col2,
   ...
   ColX

使用 SQL SELECT 作为源将记录从一个表传输到另一个表时，将此 SQL 移动到 SSIS 任务中是否有任何性能优势?例如，是否关闭了日志记录?

Is there any performance advantage of moving this SQL into an SSIS task when transferring records from one table to another using a SQL SELECT as a source? For example, is logging turned off?

第二个问题:我可以使用任何策略来确保最大传输速率吗?例如，插入前从Target表中删除索引，锁定表等?

Secondary question: Are there any tactics that I could use to ensure a maximum transfer rate? For example, removing indexes from the Target table before inserting, locking the table, etc?

推荐答案

将日志设置为简单.将日志大小设置得足够高以处理插入.系统上还有其他人吗?一个 tabblock 将有助于插入 - TargetTable with (tablock).如果您在 TargetTable 上有聚集索引，则在选择中以这种方式排序数据.如果你可以接受脏读 SourceTable with (nolock).如果您要插入超过 100,000 条记录，您可能需要使用 where 来拆分插入.

Set logging as simple. Set the log size high enough to handle the insert. Are others on the sytems? A tablock will help the insert - TargetTable with (tablock). If you have a clustered index on TargetTable order the data that way in the select. If you can accept dirty read SourceTable with (nolock). If you are inserting more than 100,000 records you might want to break up the insert using a where.

这篇关于用于表到表插入的 SSIS 与(仅限 SQL)INSERT INTO () SELECT FROM 方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！