对行数大于1000万行的表的VAXER列进行更新
最近，我们在Update语句中面临严重的性能问题，之前的更新通常需要3分钟，现在更新需要近2个小时。对于最新的数据库设计，我们更改了存储过程代码，如下所示。
下面是我详细场景:我有一个非常大的事实表，其中有1000多万行，当满足某个条件时，我需要更新事实表中的一列。因此我们编写了一条update语句来更新该列：
declare @var varchar(max) = (select metrcikey from metricdim where metrciname ='XYZ')
Update
fopty
set
Metrickey = metrickey+','+@var
from
optyfact opty
inner join
optydim dim on opty.optyid = dim.optyid
inner join
geodim geo on geo.atukey = opty.atukey
inner join
agreementdim ag on opty.optyid = ag.optyid
inner join
account acc on acc.optyid = acc.optyid
where
dim.optytype= 'ABC'
and geo.atukey =145
and ag.agreementtype ='Sold'
and acc.accountteamManager ='XXX'有没有办法优化上面的查询，根据我的理解，执行查询2小时的语句是因为SET语句
Metrickey = Metrickey + @var这里的Metrickey列是varchar，@var变量也是varchar(max)，连接这个字符串需要更多的时间。
如果上面的查询可以优化，这将对我有很大的帮助，所以请建议使用varchar值更新varchar列的最佳方法。
回答 2
Stack Overflow用户
发布于 2012-03-03 06:01:57
更新VARCHAR列没有真正的秘密超快解决方案。无论发生什么，它都会相对缓慢，但你可以做一些事情来减轻痛苦。
确保在所有表上都有适当的覆盖索引。
注意:我假设你的代码中有一个拼写错误- "Update fopty“应该是"Update opty"，对吗？
对于optyface表(opty别名)，您应该在包含metrickey的optyid和atukey列上有一个索引。
对于其他表，将所有列放在索引中-不要担心包括(覆盖)任何其他列。
完成所有这些设置后，检查查询分析器以确保在所有表上执行索引查找，否则这将运行很长一段时间。
除此之外，请确保您是在窄列上连接。INTs是好的，VARCHAR是坏的。
最后一件事:确保metrickey列不是聚集索引的一部分，否则表将在更新期间不断自我组织。
我就知道这么多。祝好运!
Stack Overflow用户
发布于 2012-03-05 00:19:45
您应该考虑批量更新(例如，一次100,000行)，而不是尝试一次更新所有行。它不一定会占用更少的时间，但它也不会在两个小时内锁定所有内容。我们可以将实际的optyid值转储到临时表中，这样我们就不必在更新中不断引用基表。
CREATE TABLE #q(rn INT IDENTITY(1,1) PRIMARY KEY, optyid INT);
DECLARE
@rc INT,
@step INT = 1,
@chunk INT = 100000;
INSERT #q(optyid) SELECT DISTINCT opty.optyid
FROM dbo.optyfact AS opty
INNER JOIN dbo.optydim AS dim ON opty.optyid = dim.optyid
INNER JOIN dbo.geodim AS geo ON geo.atukey = opty.atukey
INNER JOIN dbo.agreementdim AS ag ON opty.optyid = ag.optyid
INNER JOIN dbo.account AS acc ON acc.optyid = acc.optyid
WHERE
dim.optytype = 'ABC';
AND geo.atukey = 145
AND ag.agreementtype = 'Sold'
AND acc.accountteamManager = 'XXX';
SET @rc = @@ROWCOUNT;
WHILE @step <= ((@rc / @chunk) + 1)
BEGIN
BEGIN TRANSACTION;
UPDATE o SET MetricKey += ',' + @var
FROM dbo.optyfact AS o
INNER JOIN #q AS q ON o.optyid = q.optyid
WHERE q.rn BETWEEN (((@step-1)*@chunk)+1) AND (@step*@chunk);
COMMIT TRANSACTION;
CHECKPOINT;
SET @step += 1;
END
DROP TABLE #q;https://stackoverflow.com/questions/9538737
复制相似问题
腾讯云开发者
