ms sql在对大表做很慢的更新语句时,应单条处理,而不要一个语句更新多条,以避免锁表而阻塞其它应用的读操作

接到一客户的要求,需要修改其发布过所有文章里的联系方式,于是写sql语句,拿到mssql客户端里直接执行。

起初写的是这种形式的语句
update article set [content]=replace(content,'aaaa','bbbb') where companyid=123
写起来很简单,但是执行时花了3分钟都没有执行完,在执行过程中,网站上对article表访问的页面全部长时间没有响应。于是赶快停掉这个update语句。前面页面马上恢复正常。

分析原因应该是在update时,表被锁定,而阻塞了所有操作。
于是改成
update article set [content]=replace(content,'aaaa','bbbb') where id in(111,222,333,444....)
这种形式,当然首先要查出该客户的所有文章id号,再用逗号拼接构造成sql语句。
这个语句执行一样很慢,可以想象;试了试前台页面又没不响应了。于是赶快停掉。
要重新修改语句。
这次改成
update article set [content]=replace(content,'aaaa','bbbb') where id =111
update article set [content]=replace(content,'aaaa','bbbb') where id =222
update article set [content]=replace(content,'aaaa','bbbb') where id =222
update article set [content]=replace(content,'aaaa','bbbb') where id =222
....
这种多语句的形式,有4K多条,复制到mssql客户端里执行,还是很慢,但前台页面都可以正常打开了,根本感觉不到正在执行的update语句的影响。
经过5分多钟,终于执行完成。

上文中的语句是作了简化的,实际上构造出来的语句是这个形式
update [article] set [content]=replace(replace(replace(replace(convert(nvarchar(max),[content]),'f***','y***'),'1376133***','1358571***'),'353***','605***'),'354***','606***') where id=23002
其中,content字段是ntext型,还得convert();原来数据库是mssql 2000,后来升级到2005,本来应该把字段改成nvarchar(max)型,但因为表太大一直没有改。

查出id号作逗号拼接,是使用uestudio的替换功能,相当方便