更新慢(主键) [英] Slow update (primary key)

问题描述

update auditdata set TATCallType='12',TATCallUnit='1' from auditdata auditdata 
        inner join Auditdata_sms_12 a_sns
        on auditdata.ID = a_sns.id

当我上面查询时，执行时间超过 10 分钟.

when I above query it takes more than 10 minutes to execute.

这有什么问题

Auditdata.ID 是主键..

如果我运行更新命令也是更新索引???这是更新慢的原因吗

if i run Update command is that also update the indexes??? is this reason of update getting is slow

推荐答案

看你的评论，主表包含的行数少于临时表.

Looking at your comment, main table contains less rows than temp table.

尝试使用 EXISTS 子句(或者在某种意义上，将比较减少到较少的行数(即 1500000)

Try using EXISTS clause (or in some sense, reduce the comparison to less number of rows (i.e. 1500000)

update auditdata set TATCallType='12',TATCallUnit='1' 
from auditdata auditdata 
WHERE EXISTS 
(SELECT id from Auditdata_sms_12 a_sns WHERE a_sns.id = auditdata.ID)

这个想法是为了限制比较.

The idea is to limit the comparisons.

AuditdataSMS12 应该在 ID 上有索引，以便能够快速获取行.这就是您实际要查找给定 ID 的表.

AuditdataSMS12 should have the index on ID to be able to get the row quickly. That is the table you are actually looking up for a given ID.

这篇关于更新慢(主键)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！