删除行并插入新行,或者
更新现有行
对我来说更容易只是删除所有行并插入新行,但是如果这将使表和索引碎片化,并影响性能,那么我希望尽可能进行更新,并仅在必要时删除/插入。
这将是每晚的服务,我不希望提高流程本身的速度。我更担心针对该表的查询的性能,因为该表已经有8900万条记录,并且每个夜间过程将如何影响它。
我应该删除/插入记录还是应该更新现有记录? (如果可能)进行此每晚处理?
#1 楼
这实际上取决于要更改的数据量。可以说该表有20列。而且,您还有5个索引-每个都在差异上。现在,如果所有20列中的值都在更改,或者即使5列中的数据正在更改并且这5列都已建立索引,那么您最好还是“删除并插入”。但是,如果仅2列发生更改,并且可以说这些列不是任何非聚集索引的一部分,那么最好“更新”记录,因为在这种情况下,仅聚集索引将被更新(索引不必
在进一步的研究中,我确实发现我的上述评论是多余的,因为SQL Server内部具有执行UPDATE的两种独立机制。 -“就地更新”(即,通过将列值更改为原始行中的新值)或作为“非就地更新”(DELETE,然后插入INSERT)。
规则是就地更新,并在可能的情况下执行。在这里,行以相同的范围完全位于同一页上的相同位置。仅更改受影响的字节。日志只有一条记录(前提是没有更新触发器)。如果要更新堆(并且页面上有足够的空间),则会在适当的地方进行更新。如果集群键发生更改,更新也将发生,但是该行根本不需要移动。
例如:如果您的姓氏具有聚集索引,并且您具有以下名称:
Able,Baker和Charlie
现在您想将Baker更新为Becker。无需移动任何行。因此,这可以就位。而如果您必须将Able更新为Kumar,则必须将这些行转移(即使它们在同一页上)。在这种情况下,SQL Server将先执行DELETE,然后再执行INSERT。
考虑到上述情况,我建议您进行常规的UPDATE并让SQL Server找出实现此方法的最佳方法。内部。
有关“ UPDATE”内部结构或与SQL Server相关的任何内部结构的更多详细信息,请参阅Kalen Delaney,Paul Randal等人的书-SQL Server 2008内部结构。
#2 楼
您是否研究了SQL 2008中的MERGE命令?这是一个基本示例: merge YourBigTable ybt
using (select distinct (RecordID) from YourOtherTable) yot
on yot.Recordid = YBT.RecordID
when NOT matched by target
then insert (RecordID)
values (yot.DeviceID) ;
这基本上是一个“ UPSERT”命令。如果存在则更新,如果不存在则插入。非常快,非常酷的命令。
评论
它的运行速度并不比UPDATE快。
– Mark Storey-Smith
2011-11-18 17:44
它比更新然后插入不存在的更新要快。
– datagod
11年11月18日在18:06
如果您知道这种情况,请证明:)
– Mark Storey-Smith
2011年11月18日23:05
#3 楼
但是,我本人在具有3000万(3000万)记录的表上检查了Delete and Insert vs Update。该表具有一个集群唯一组合键和3个非集群键。对于“删除并插入”,它花费了9分钟。对于更新,花费了55分钟。每行只有一列已更新。所以,我要求你们不要猜测。当处理具有许多列和大量数据的大型表时,方程将发生变化。
评论
我也遇到过这种情况,但后来发现有时可以通过在源或目标,提示或子设置目标(不适用于完全合并)中添加指标(临时或烫发)来优化大型合并。
– crokusek
19年2月14日在0:08
#4 楼
更新不是那么快。实现快速插入的技巧是在插入数据时禁用索引。考虑使用此方法:
-- disable indexes
ALTER INDEX [index_name] ON dbo.import_table DISABLE
-- ... disable more indexes
-- don't use delete if you don't care about minimal logging. truncate is faster
TRUNCATE TABLE dbo.import_table
-- just insert the new rows
INSERT dbo.import_table
SELECT
*
FROM
dbo.source_table
-- rebuild indexes
ALTER INDEX [index_name] ON dbo.import_table REBUILD
-- ... rebuild more indexes
甚至更快还可以关闭db选项中的自动统计信息更新。如果表格有重大更改,则应运行:
UPDATE STATISTICS dbo.import_table
EXEC sp_updatestats
定期(每天,每周一次,具体取决于数据库大小)以保持统计信息最新。需要注意的是在表为空时更新统计信息。如果您在重新填充表格后不运行统计信息,则会破坏统计信息。
评论
我不同意这种情况总是如此。而且,@ adopilot问题中的表无法通过TRUNCATE清除,因为它包含89m条记录,而他只想更新36k。
– Mark Storey-Smith
2011年11月18日15:32
需要学习阅读的帖子更加谨慎!我会更新帖子...实际上,我需要进行很多更改。
–阿斯肯
2011年11月18日15:37
评论
我相信您应该在表上提供更多详细信息,因为我猜这将取决于字段索引的潜在存在。