首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >对行数大于1000万行的表的VAXER列进行更新

对行数大于1000万行的表的VAXER列进行更新
EN

Stack Overflow用户
提问于 2012-03-03 02:48:52
回答 2查看 415关注 0票数 0

最近,我们在Update语句中面临严重的性能问题,之前的更新通常需要3分钟,现在更新需要近2个小时。对于最新的数据库设计,我们更改了存储过程代码,如下所示。

下面是我详细场景:我有一个非常大的事实表,其中有1000多万行,当满足某个条件时,我需要更新事实表中的一列。因此我们编写了一条update语句来更新该列:

代码语言:javascript
运行
复制
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语句

代码语言:javascript
运行
复制
Metrickey = Metrickey + @var

这里的Metrickey列是varchar@var变量也是varchar(max),连接这个字符串需要更多的时间。

如果上面的查询可以优化,这将对我有很大的帮助,所以请建议使用varchar值更新varchar列的最佳方法。

EN

回答 2

Stack Overflow用户

发布于 2012-03-03 06:01:57

更新VARCHAR列没有真正的秘密超快解决方案。无论发生什么,它都会相对缓慢,但你可以做一些事情来减轻痛苦。

确保在所有表上都有适当的覆盖索引。

注意:我假设你的代码中有一个拼写错误- "Update fopty“应该是"Update opty",对吗?

对于optyface表(opty别名),您应该在包含metrickeyoptyidatukey列上有一个索引。

对于其他表,将所有列放在索引中-不要担心包括(覆盖)任何其他列。

完成所有这些设置后,检查查询分析器以确保在所有表上执行索引查找,否则这将运行很长一段时间。

除此之外,请确保您是在窄列上连接。INTs是好的,VARCHAR是坏的。

最后一件事:确保metrickey列不是聚集索引的一部分,否则表将在更新期间不断自我组织。

我就知道这么多。祝好运!

票数 0
EN

Stack Overflow用户

发布于 2012-03-05 00:19:45

您应该考虑批量更新(例如,一次100,000行),而不是尝试一次更新所有行。它不一定会占用更少的时间,但它也不会在两个小时内锁定所有内容。我们可以将实际的optyid值转储到临时表中,这样我们就不必在更新中不断引用基表。

代码语言:javascript
运行
复制
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;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/9538737

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档