全部删除/批量插入

Question

首先，我要说的是，我正在 SQL Server 2005 上运行，因此我无权访问 MERGE。

我有一个包含约 15 万行的表，我每天都会从文本文件中更新该表。当行从文本文件中掉出时，我需要从数据库中删除它们，如果它们发生变化或者是新的，我需要相应地更新/插入。

经过一些测试后，我发现从性能角度来看，执行完全删除然后从文本文件中批量插入比逐行读取文件进行更新/插入要快得多。不过，我最近看到一些帖子讨论使用临时表和 UPDATE 语句的输出来模仿 SQL Server 2008 的 MERGE 功能。

我对此很感兴趣，因为我正在研究如何消除当表没有行时删除/批量插入方法中的时间。我仍然认为这种方法是最快的，所以我正在寻找解决空表问题的最佳方法。

谢谢

Answer 1

我认为你最快的方法是：

1. 删除所有外键和索引
   从你的桌子上。
2. 截断
   桌子。
3. 批量插入您的数据。
4. 重新创建您的外键并
   索引。

Answer 2

问题是 Joe 的解决方案不够快，还是在进程运行时无法对目标表进行任何活动？如果您只需要阻止用户对目标表运行查询，则应该将进程包含在事务块中。这样，当 TRUNCATE TABLE 执行时，它将创建一个表锁，该锁将在事务持续时间内保持，如下所示：

begin tran;

truncate table stage_table

bulk insert stage_table
from N'C:\datafile.txt'

commit tran;

Answer 3

另一种解决方案可以满足您正在更新的表没有“停机时间”的要求。

听起来好像最初您正在读取文件并一次执行 INSERT/UPDATE/DELETE 1 行。比这更高效的方法，不涉及清除表，如下所示：

1）将文件批量加载到新的单独表中（无索引）
2）然后在其上创建PK
3) 运行 3 条语句以从这个新（临时）表更新原始表：
删除主表中新表中不存在的行
更新主表中的行，其中新表中有匹配的行
将不存在的新表中的行插入到主表中

这将比逐行操作执行得更好，并且有望满足您的总体要求

Answer 4

有一种零停机时间更新表的方法：在表中保留两天的数据，并在加载新行后删除旧行！

1. 添加一个 DataDate 列，表示大约 150K 行的有效日期。
2. 创建一个包含“今天”DataDate 的单行单列表。
3. 创建两个表的视图，仅选择与 DataDate 表中的行匹配的行。如果您愿意的话，可以将其编入索引。读者现在将参考此视图，而不是表格。
4. 批量插入行。 （显然，您需要将 DataDate 添加到每一行。）
5. 更新 DataDate 表。 立即查看更新！
6. 闲暇时删除昨天的行。

SELECT 性能不会受到影响；沿着主键将一行连接到 150,000 行对于任何 15 年以下的服务器来说应该不会出现任何问题。

我经常使用这种技术，并且在处理依赖于 sp_rename 的进程时也遇到了困难。修改模式的生产过程是一个令人头疼的问题。不。

Answer 5

对于原始速度，我认为表中有大约 150K 行，我只需删除表，从头开始重新创建它（没有索引），然后重新批量加载。完成批量加载后，创建索引。

当然，这假设在一段时间内表为空/不存在是可以接受的，这听起来确实可能是这种情况。