我有一个表，用于一年中每个月，该表中(除其他外)有25列用于自定义数据。只有前8个数据列被索引，并且我们已经将数据插入到第21列中，他们现在想对这些列进行通配符搜索。我不能为第21列创建索引，因为应用程序不允许在GUI中的前8个数据列上进行通配符搜索。
我试着运行下面的程序，但是当事务日志满了时它超时了，因为它被设置为134 GB的最大大小，所以已经将其更改为无限制增长。
UPDATE CentralContact.dbo.Spd_month_1
SET p1_value = p21_value
WHERE dbs_id ='190'
是否有一种更快的方法来做到这一点，因为每个表包含超过2000万条记录?
2000万行并不是很多行，即使在笔记本电脑上运行也是如此。我在联想x1 (SSD + 8GB RAM)上有几亿行测试表。我的服务器有几十亿行表(未分区)。
由于事务日志满而导致的更新查询超时并不是真正的性能问题。看起来您要么最近没有备份事务日志，要么行非常大，甚至用1个大事务填充了日志。有几个选项需要考虑:
-
备份事务日志以释放所有以前提交的事务的空间。如果你最近还没有这样做过，那么现在就应该这样做了。关于如何做到这一点的详细信息，请在线查询SQL Server书籍。对于134GB来说，这是一个相当大的日志，如果从初始创建开始就自动增长，您可能会有太多的虚拟日志文件和大量的物理碎片-两者都可能对性能产生重大的负面影响(如果您在ssd上运行，那么物理碎片是可以的)。而且，每次自动增长都会变得更糟，因为Tlog空间在使用前需要初始化，因此初始化的块越来越大。当您有一个维护窗口时，请考虑清除日志并重新创建一个"合理"的大小。
-
将更新分解为几个较小的事务。这可能很容易，也可能不容易，这取决于模式的其余部分。如果有一个列具有某种单调值(例如时间戳、日期、身份、ID等)，那么您可以轻松地一次更新范围。拥有包含少量唯一值的列也很有帮助。只是要注意，在进行更改时不要添加或更新一堆新值。如果您不是24x7的操作，那么将数据库锁定在单用户模式以进行更新和验证是最简单的解决方案，尽管这是繁重的解决方案。
最简单的方法可能是使用视图。首先，重命名表，然后创建一个视图来修改列:
sp_rename 'CentralContact.dbo.Spd_month_1 ', '_Spd_month_1'
create view Spd_month_1 as
select p_col21 as p_col1, . . .
from _Spd_month_1;
视图非常简单，因此可以用于更新。您没有删除原始表，因此保留了约束、外键引用、触发器等。应用程序应该能够像访问表一样轻松地访问视图。
更新的问题是每一行都被更改了，所以每一行都被记录了。即使使用SQL Server最小的日志记录功能也是如此。解决这个问题的一种方法是将表复制到另一个位置，截断它，然后重新插入数据。但是，对于134 gb，我会尽量减少任何数据移动操作。
您可能要使用的是Pentaho的Kettle(或"spoon")。点击这里查看。
它所做的是"工作"one_answers"转换"以及其他自动化过程，您可以使用它们来维护各种服务器和数据库。
它做的一件事是批量更新。您可以选择要更新的整个记录集，然后每隔几秒钟向它提供1000条记录，以便更新和提交。这样就不会无止境地锁住表。
我一直使用这个方法，当对流量大/负载重的表进行大型更新时，我从不建议使用其他方法。