从数据库中删除行需要很长时间 [英] Deleting rows from database takes unexpectedly long time

问题描述

你好CP专家

我有一个包含大约80'000行的表TBLA。

我需要使用这个SQL删除一些特定的行

Hi CP experts
I have a table "TBLA" containing about 80'000 rows.
I need to delete some specific rows from it using this SQL

DELETE FROM TBLA TBLA_DEL
WHERE TBLA_DEL.FIELD3 IS NULL
  AND TBLA_DEL.ID IN
  (
     SELECT
        MAX(TBLB_DELCOND.ID)
     FROM TBLB TBLB_DELCOND
     WHERE TBLB_DELCOND.FIELD1 =  TBLB_DELCOND.FIELD2
     GROUP BY TBLB_DELCOND.FGN1, TBLB_DELCOND.FIELD2
     HAVING COUNT(*) > 1
  )

我有问题，对我来说更好的是：

- 上述SQL中单独的SELECT语句只需要300mS

- 上面列出的DELETE语句确实需要8分钟



知道我做错了吗？

提前谢谢你。

Bruno

The "problem" I have, better what is absolutely not clear for me is:
- The SELECT statement alone in the above SQL needs only 300mS
- The DELETE statement listed above does need 8 minutes

Any idea what I'm doing wrong?
Thank you in advance.
Bruno

推荐答案

通过使用JOIN到子查询来消除IN子查询。

Eliminate the IN-subquery by using a JOIN to the subquery.

试试这个< br $>

Try this

DELETE FROM TBLA TBLA_DEL
 WHERE TBLA_DEL.FIELD3 IS NULL
       AND exists
           (
              SELECT
                 TBLB_DELCOND.ID
              FROM TBLB TBLB_DELCOND
              WHERE
                   TBLA_DEL.ID=TBLB_DELCOND.ID
              and
                   TBLB_DELCOND.FIELD1 =  TBLB_DELCOND.FIELD2
              GROUP BY
                   TBLB_DELCOND.FGN1, TBLB_DELCOND.FIELD2
              HAVING
                   COUNT(1) > 1
           )

或

Or

  SELECT   Max(TBLB_DELCOND.ID) AS ID_TO_Delete
        into  #delete
  FROM
        TBLB TBLB_DELCOND
  WHERE
        TBLB_DELCOND.FIELD1 =  TBLB_DELCOND.FIELD2
  GROUP BY
        TBLB_DELCOND.FGN1, TBLB_DELCOND.FIELD2
  HAVING
        COUNT(1) > 1


DELETE FROM TBLA TBLA_DEL
WHERE TBLA_DEL.FIELD3 IS NULL
AND exists
(
    Select ID_TO_Delete from #delete del WHERE TBLA_DEL.ID=del.ID
)

你检查了执行计划吗？

请参阅： SQL调优教程 - 了解数据库执行计划（1 ） [ ^ ]



因为我的第一个想法是：SQL是否正在为我要删除的表中的每一行选择？如果是这样，那么每行将达到300毫秒......而且这种情况会快速增加！

Have you checked the Execution Plan?
See here: SQL Tuning Tutorial - Understanding a Database Execution Plan (1)[^]

Because my first thought would be: "is SQL doing that select for each row in the table I am trying to delete from?" If so, then that's going to be 300ms per row...and that adds up fast!

这篇关于从数据库中删除行需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！