优化删除

Question

我有一个导入器系统，它更新表中已有行的列。由于UPDATE需要时间，我将其更改为DELETE和BULK INSERT。

这是我的数据库设置片段

Table: ParameterDefinition
Columns: Id, Name, Other Cols
Table: ParameterValue
Columns: Id, CustId, ParameterDefId, Value

我从XML源中获取与ParamterDefinition.Name关联的值，因此要导入，我首先删除所有现有的ParamterValue和XML中传递的所有ParamterDefinition.Name，最后从XML中批量插入所有值。这是我的查询

DELETE FROM ParameterValue WHERE CustId = ? AND ParameterDefId IN (?,?...?);

对于1000个客户，上面的DELETE语句被调用1000次，这现在非常耗时，大约为64秒。

有没有更好的方法来处理1000名客户的删除？

谢谢，

Sheeju

Answer 1

为大容量插入创建一个临时表（ParameterValue_Import）。对该表执行大容量插入，然后根据导入的数据更新/插入/删除。

新行的INSERT INTO .. SELECT .. WHERE NOT EXISTS ( .. )

更新的UPDATE .. FROM

删除的DELETE FROM WHERE NOT EXISTS ( .. )

批量操作比独立操作具有更好的性能。大多数DBMS被设计为处理基于集合的操作，而不是基于记录的操作。

编辑

要基于仅引用一条记录的WHERE子句删除或更新一条记录，DBMS应执行完整的表扫描（如果WHERE条件没有索引）或执行索引查找。只有在成功识别记录之后，DBMS才会继续原始请求（更新或删除）。根据表中记录的数量和/或索引的大小/深度，这可能非常昂贵。此过程针对批处理中的每个命令执行。总成本加起来可能比基于另一个表更新/删除记录的成本还要高。（尤其是如果操作是更新/删除目标表中几乎所有的记录。）

当您试图同时删除/更新多个记录（例如，基于另一个表）时，DBMS可以只使用一个表扫描/索引查找进行查找，并在处理您的请求时进行逻辑联接。

在每种情况下，纯粹更新记录的成本都是相同的，只是查找的总成本可能会显著不同。

此外，删除然后插入记录以更新它可能需要更多的资源：当您删除记录时，所有相关的索引都将更新，当您插入新记录时，索引将再次更新，而在更新记录时，只应更新那些与更新列相关的索引（并且索引更新应只进行一次）。

Answer 2

我给出了@Pred 给出的上述想法的确切语法

批量插入后，可以说您在"ParamterValue_Import"中有数据

插入"ParamterValue_Import"中不在"ParamterValue"中的记录

INSERT INTO ParameterValue (
  CustId, ParameterDefId, Value
)
SELECT
  CustId, ParameterDefId, Value
FROM
  ParameterValue_Import
WHERE
  NOT EXISTS (
    SELECT null
    FROM ParameterValue
    WHERE ParameterValue.CustId = ParameterValue_Import.CustId
);

更新"ParamterValue"中的记录，这些记录也在"ParamterValue_Import"中

UPDATE
  ParameterValue
SET
  Value = ParameterValue_Import.Value
FROM
  ParameterValue_Import
WHERE
  ParameterValue.ParameterDefId = ParameterValue_Import.ParameterDefId
  AND ParameterValue.CustId = ParameterValue_Import.CustId;