最近,我们在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
列的最佳方法。
发布于 2012-03-03 06:01:57
更新VARCHAR列没有真正的秘密超快解决方案。无论发生什么,它都会相对缓慢,但你可以做一些事情来减轻痛苦。
确保在所有表上都有适当的覆盖索引。
注意:我假设你的代码中有一个拼写错误- "Update fopty“应该是"Update opty",对吗?
对于optyface表(opty别名),您应该在包含metrickey
的optyid
和atukey
列上有一个索引。
对于其他表,将所有列放在索引中-不要担心包括(覆盖)任何其他列。
完成所有这些设置后,检查查询分析器以确保在所有表上执行索引查找,否则这将运行很长一段时间。
除此之外,请确保您是在窄列上连接。INTs是好的,VARCHAR是坏的。
最后一件事:确保metrickey
列不是聚集索引的一部分,否则表将在更新期间不断自我组织。
我就知道这么多。祝好运!
发布于 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
复制相似问题