sql - 在 SQL Server 中替换/更新表行 [批量数据] 的字符串时性能很慢
问题描述
我想用虚拟数据更新下面称为下面formatted body
的主表的列-posts
schema
现在,我想replace/update a substring
[ie source
with the final URL
] 来自上述 formattedbody 列。[ 5335
excel 表中的总记录]
对于我在下面的查询中写的相同 -
DECLARE @LoopCounter INT = 1
DECLARE @SURL nvarchar(max)
DECLARE @FURL nvarchar(max)
WHILE ( @LoopCounter <= 5335)
BEGIN
SET @SURL = (select sourceURL from temptable where ID = @LoopCounter)
SET @FURL = (select [TargetURL] from temptable where ID = @LoopCounter)
update posts
Set FormattedBody=REPLACE(CAST(FormattedBody as NVarchar(Max)),@SURL,@FURL)
Where SectionID = 95 and postlevel=1 and CAST(FormattedBody as NVarchar(Max)) like '%' + @SURL + '%'
SET @LoopCounter = @LoopCounter + 1
END
temptable
包含 excel 工作表的数据[i.e. ID,sourceURL, and TargetURL]
。
上面的查询按预期工作,但是performance is too low
, 因为它循环遍历来自 posts 表 [huge data] 的所有行以获取5335
记录。
目前,它仅更新 3 条记录/分钟。
任何建议/帮助表示赞赏!:) 谢谢!
解决方案
我认为您不需要使用while
and update
,我会使用andUPDATE .. JOIN
代替 。while
update
temptable
如果和表之间没有任何关系posts
,您可以使用CROSS JOIN
(笛卡尔积)让每个sourceURL
和[TargetURL]
temptable
列映射到posts
表然后更新。
UPDATE p
SET FormattedBody = REPLACE(CAST(FormattedBody as NVarchar(Max)),sourceURL,[TargetURL])
FROM posts p
CROSS JOIN
(
SELECT sourceURL,[TargetURL]
FROM temptable
where id <= 5335
) targetDt
Where p.SectionID = 95 and p.postlevel=1
推荐阅读
- swiftui - SwiftUI layout issue with Vstack without giving child view a fix frame height
- sql - Separate row created for data that can be included in same row with same id
- matlab - How do I convert a time domain signal into frequency domain?
- api - LuckPerms API 权限更改未应用
- react-native - React native: can't configure the header with navigationOptions
- java - Java snippet based AST-Manipulation before/during compilation
- elasticsearch - How to calcuate starttime & endtime using painless script
- python - 如何使用 Python 拥有分类正则表达式组
- javascript - CloudFlare 缓存页面 - 国家检测
- node.js - Nodejs - 调用 AWS SNS 发布,但未发送消息