更新慢（主键）

发布于 2024-07-26 18:36:38 字数 318 浏览 3 评论 0原文

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

当我上面的查询需要超过10分钟才能执行。

这个 Auditdata.ID 中的错误

是主键。

如果我运行 Update 命令，是否也会更新索引？？？这是更新速度慢的原因吗

原文

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

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

what wrong in this

Auditdata.ID is primary key..

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

摇划花蜜的午后 2024-08-02 18:36:38

这里有一些事情在起作用。

首先，SQL 语句看起来很糟糕。更新中的“FROM”子句旨在用作 JOIN 更新。由于您正在更新具有硬编码值的行，因此无需这样做。

其次，更深奥的是，如果索引都像您所说的那样正确，那么您可能正在处理初始写入或事务日志区域（Oracle 中的撤消，SQL Server 中的日志）的缓慢磁盘 I/O ， ETC。）。

作为健全性检查，我会做两件事。第一，仅更新尚未设置条件的行。许多 DBMS 产品很乐意对不发生变化的行执行物理磁盘 I/O（尽管许多产品不会）。尝试一下极限。

第二，小批量应用更新。这确实有助于解决日志争用和磁盘速度较慢的问题。

因此，首先尝试如下操作：

UPDATE auditdata 
   SET TATCallType = '12' 
     , TATCallUnit = '1' 
  FROM auditdata 
 WHERE TATCallType <> '12' 
   AND TATCallUnit <> '1'
   AND EXISTS( SELECT *
                 FROM Auditdata_sms_12 a_sns 
                WHERE a_sns.id = auditdata.ID )

如果您想进行批处理，在 SQL Server 中这非常简单：

SET ROWCOUNT 2000

UPDATE ...

(run continually in a loop via T-SQL or by hand until @@ROWCOUNT = 0)

SET ROWCOUNT 0

There are a couple things at play here.

First, the SQL statement looks broken. The "FROM" clause in an update is designed to be used as a JOIN'd update. Since you're updating rows with hard-coded values, there's no need to do that.

Secondly, and more esoterically, if the indexes are all correct as you say they are, then perhaps you're dealing with a slow disk I/O for either the initial writes OR the transaction log area (undo in Oracle, logs in SQL Server, etc.).

As a sanity check I'd do two things. One, only update rows that do not already have the conditions set. Many DBMS products will happily perform physical disk I/O for a row that doesn't change (although many don't). Try it with the limit.

Two, apply the update in smaller batches. This can really help with log contention and with slower disks.

So, something like the following to initially try:

UPDATE auditdata 
   SET TATCallType = '12' 
     , TATCallUnit = '1' 
  FROM auditdata 
 WHERE TATCallType <> '12' 
   AND TATCallUnit <> '1'
   AND EXISTS( SELECT *
                 FROM Auditdata_sms_12 a_sns 
                WHERE a_sns.id = auditdata.ID )

If you want to do batches, in SQL Server it's pretty easy:

SET ROWCOUNT 2000

UPDATE ...

(run continually in a loop via T-SQL or by hand until @@ROWCOUNT = 0)

SET ROWCOUNT 0

回复收藏 0 原文

失去的东西太少 2024-08-02 18:36:38

查看您的评论，主表包含的行数少于临时表。

尝试使用 EXISTS 子句（或者在某种意义上，将比较减少到更少的行数（即 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)

这个想法是限制比较。

编辑：AuditdataSMS12 应该在 ID 上有索引，以便能够快速获取行。即您实际上正在查找给定 ID 的表。

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

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.

EDIT: 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.

回复收藏 0 原文

居里长安 2024-08-02 18:36:38

已更新
再次阅读初始查询后，我意识到您没有更新主 ID 字段，而是更新了其他 2 个数据字段。请重新阅读我的回复的第一个声明并做出相应的评论。对不起。

您要更新的任一字段上是否定义了聚集索引？聚集索引有一些优点，我不知道它们的优点，但它们可能会在更新期间造成很大的性能损失。我的理解是，对聚集索引的更新可能会导致整个索引必须重新编译。如果表中有大量数据，这肯定会导致您的问题。

另外，请确保表上没有触发器。如果有一个触发器不能正常工作，它可能会导致同样的性能下降。

回复收藏 0 原文

花想c 2024-08-02 18:36:38

一个简单的选择（例如）需要多长时间

 select id from auditdata auditdata 
    inner join Auditdata_sms_12 a_sns
    on auditdata.ID = a_sns.id

以及它找到多少条记录？

如果 Sql 服务器必须读取所有 500 万条记录，或者更新 100 万条记录，并且它没有足够的内存或足够快的硬件，那么查询可能不会太多。

您可能需要监视 Sql 服务器硬件，并查看查询计划以了解哪些位占用了时间。

How long does a simple select (eg

 select id from auditdata auditdata 
    inner join Auditdata_sms_12 a_sns
    on auditdata.ID = a_sns.id

) take and how many records does it find?

If Sql server has to read through all 5 million records , or update a million records and it does not have enough memory or fast enough hardware, then there may not much you with the query.

You probably need to monitor the Sql server hardware and also look at the Query Plan to see what bit takes up the time.

回复收藏 0 原文

~没有更多了~