update一张1000多万条记录的表，几乎把SQLSERVER给弄死了

[silverpearl]

一个1000万条记录的表[log]更新2个字段，
2个字段的值分别要从一张360条记录和一张24条记录的表中查询获得，

昨天做了一个晚上都没做好，早上把它停掉了！

怎么办啊！

update log
set dateid=d.dateid,
hourid=h.hourid
from log a,theDate d,theHour h
where datediff(day,a.createtime,d.theDate)=0
and datepart(hh,a.createtime)=h.hourid-1

[silverpearl]

datediff是不是对索引无效的？

[wudan8057]

1000万条记录想快都很难!最好重新考虑更新方式!例如将这个大的事物分成小事物来执行!

[云中客]

有道理，1000万的数据，这么大的量，怎么可能快呢

[silverpearl]

停掉后 ，sqlserver在对这个库进行自动恢复，还要多个小时才能完成

[wudan8057]

肯定会这样的，因为你的事物为完成就down掉了，sqlserver要对你先前所做的事物进行撤消！

[云路]

真是海量啊

[rfq]

10000条用10秒
10*1000=10000秒
10000/3600=3小时

[viptiger]

在日期上建立索引，会快些

[hillhx]

我不太懂啊，不过也说两句
update log
set dateid=d.dateid,
hourid=h.hourid
from log a,theDate d,theHour h
where datediff(day,a.createtime,d.theDate)=0
and datepart(hh,a.createtime)=h.hourid-1

我觉得

from log a,theDate d,theHour h
where datediff(day,a.createtime,d.theDate)=0
and datepart(hh,a.createtime)=h.hourid-1

有问题，好象这样的连接方式，每更新一条数据都要扫描全部的log a,theDate d,theHour h
积，你看能不能换个方式，比如

update log
set dateid=d.dateid,
hourid=h.hourid
from theDate d,theHour h
where datediff(day,log.createtime,d.theDate)=0
and datepart(hh,log.createtime)=h.hourid-1

我不太懂啊，但总感觉这么写才对

另外你可以每次把事务弄小点，比如一次执行100W的数据量，应该就差不多了

[李雅勇的技术专栏]

建好索引，关掉服务，断开网络，只留一个sql server运行。
做好一切优化，比如你的程序里用了过多的函数，最好可以改一下，比如加一列，用函数把需要的数据在表内更新到此列，之后再多表更新，更新时用此列中的数据，此时此列可以建索引了。
还有就是把这些语句存入存储过程，经过编译也会执行得更快。

[luoqun_ncs]

datediff(day,a.createtime,d.theDate)=0
and datepart(hh,a.createtime)=h.hourid-1

在a.createTime上面用函数会使索引失效
--------------
不知道d.theDate里面是否包含时间,还是只包含日期.
假设只包含日期,可以把第一个条件换成: a.createTime >= d.theDate and a.createTime < d.theDate + 1

第二个条件,sql server不支持函数索引,大家想想其他办法

1000w记录在一个事务里面提交对服务器压力太大了,召唤事务达人.
也请楼主提供log表结构方便大家分析.

[luoqun_ncs]

关于第二个条件的优化,可以建立一个临时表.把a.createTime里面的小时字段先分离出来.这样可以利用索引.

[silverpearl]

[log]的表结构就是：
id bigint
createtime datetime
dateid int
hourid smallint
adid int

是用来记录观看广告的时间，其他一些字段不牵涉到这个操作，所以不写

现在是theDate和 theHour分别是日期表和小时表
现在想把log添加两个字段dateid和hourid

[bugchen888]

DECLARE @cnt INT
SET ROWCOUNT 10000--每次做一万笔
WHILE @cnt>0
BEGIN
BEGIN TRANSACTION

UPDATE LOG
SET dateid=d.dateid,
hourid=h.hourid
FROM LOG a,theDate d,theHour h
WHERE datediff(DAY,a.createtime,d.theDate)=0
AND datepart(hh,a.createtime)=h.hourid-1

SET @cnt=@@ROWCOUNT

COMMIT TRANSACTION
END

[lovcal]

呵呵……楼上的处理方式比较好
但是建议优化一下，尽量不要对表中的字段使用函数
WHERE datediff(DAY,a.createtime,d.theDate)=0
AND datepart(hh,a.createtime)=h.hourid-1
改成A>B and A<C这种形式

[silverpearl]

现在正用bugchen888(臭虫) 的办法测试400万条记录
看看需要多少时间,
已经不用datediff但datepart还是用的

[ycy589]

学习

[prcgolf]

up

[silverpearl]

bugchen888(臭虫) 是正解

1000万条的时间<>1万条*1000的时间

[梦想家起飞]

一切更新开始之前锁表
每更新100条记录提交一次
全部更新完之后解锁表

索引不索引的意义不大
这时候最要命的是缓冲区、回滚段、日志

btw，这种操作只能在三更半夜的时候做。