提高Sql Delete的性能

问题描述：

我们有一个基于id字段（主键）从表中删除一些行的查询。这是一个非常简单的查询：（如70K）提高Sql Delete的性能

delete all from OUR_TABLE where ID in (123, 345, ...)

问题是节数IDS可以是巨大的，所以查询需要较长的时间。有什么办法可以优化吗？（我们正在使用sybase - 如果这很重要）。

这是ASE或ASA？你知道你的版本号吗？ – AdamH 2009-02-24 14:08:02

它是ASE版本10x。 – amit 2009-02-25 03:08:53

答

请考虑分批运行。一次循环运行1000条记录可能比一个查询执行任何操作都快得多，并且此外不会将表格一直锁定在其他用户处。

如果您有级联删除（以及大量受影响的外键表）或触发器，您可能需要以更小的批次运行。你必须体验一下，看看哪个是你的情况的最佳数字。我已经有了一些表格，我不得不分批删除100个表格，其中有50000个工作表格（有幸在这种情况下，我删除了一百万条记录）。

但在任何连我会把我打算删除到一个临时表，并从那里删除我的键值。

答

我想知道是否解析IN中有70K条目的子句是一个问题。你是否尝试过使用连接的临时表？

不知道Sybase，但在SQLServer中，这也是我第一次尝试优化删除。 – 2009-02-23 10:58:07

答

请问our_table有删除级联的参考吗？

答

有两种方法可以使像这样的语句执行：

创建一个新表，并复制所有，但删除行。之后交换表格（alter table name ...）我建议尝试一下，即使听起来很愚蠢。有些数据库在复制时比在删除时快得多。
分区你的表。创建N个表并使用视图将它们合并成一个表。将行按照删除标准分组到不同的表中。这个想法是删除整个表而不是删除单个行。

+1这是我的建议。 – Elijah 2009-02-23 11:38:14

答

Sybase可以处理IN子句中的70K个参数吗？我使用的所有数据库对于IN子句的参数数量都有一些限制。例如，Oracle有大约1000个的限制。

您可以创建子选择而不是IN子句吗？这将缩短SQL。也许这可能有助于IN子句中的大量值。事情是这样的：

DELETE FROM OUR_TABLE WHERE ID IN 
     (SELECT ID FROM somewhere WHERE some_condition)

删除大量记录，可以加快与数据库中的一些干预措施，如果数据库模型许可证。这里有一些策略：

你可以通过删除索引，删除记录和重新创建索引来加快速度。这将消除重新平衡索引树，同时删除记录。
- 滴上表的所有索引
- 删除记录
- 重新创建索引
- 如果你有很多的关系到此表，尝试禁用限制，如果你是绝对相信，delete命令不会破坏任何完整性约束。因为数据库不会检查完整性，所以删除将变得更快。删除后启用约束。
- 禁用完整性约束，禁用检查约束
- 删除记录
- 启用约束
- 禁止触发器的表，如果您有任何，如果您的业务规则允许的。删除记录，然后启用触发器。
- 最后，按照其他建议 - 制作包含不会被删除的行的表的副本，然后删除原始，重命名副本并重新创建完整性约束（如果有）。

我会尝试的1，2和3的组合。如果还是不行，那么4.如果一切都慢，我会寻找更大的盒子 - 更多的内存，更快的磁盘。

禁用触发器是一个非常糟糕的主意，除非您能够阻止其他用户在禁用数据库时对其执行操作。 – HLGEM 2009-02-23 14:28:46

我知道，这就是为什么我写了“如果你的商业规则允许的话”。例如，我看到很多数据库中使用触发器进行某种审计更改。如果他需要表现，可能会消除这种触发器。 – zendar 2009-02-23 15:05:47

答

找出什么是使用了性能！

在很多情况下，你可以使用所提供的解决方案之一。但也可能有其他的（基于Oracle的知识，所以在其他数据库上会有所不同）编辑：刚刚看到你提到了sybase：

你有那张表的外键吗？确保引用ID已编入索引
您是否在该表上有索引？可能是在删除之前删除并在删除之后重新创建可能会更快。
检查执行计划。它是否使用全表扫描可能更快的索引？或者相反呢？ HINTS可以帮助
，而不是选择到NEW_TABLE创建一个表作为选择上述的建议可能会更快。

但请记住：找出什么是性能优先使用。

当您使用DDL语句确保您理解并接受它可能对交易和备份的后果。

答

尝试按照与表格相同的顺序对要传入的“ID”进行排序，或者存储索引。然后，您可以在磁盘缓存中获得更多匹配。

将要删除的ID放入临时表中，该表的Ids按与主表相同的顺序排序，可让数据库在主表上进行简单扫描。

您可以尝试使用多于一个连接，并通过连接吐出工作以便使用数据库服务器上的所有CPU，但是请考虑先取出哪些锁等。

答

我也认为，临时表可能是最好的解决方案。

但是，如果您要执行“从...删除ID（从...中选择ID）”，那么对于大型查询它仍然会很慢。因此，我建议您使用连接删除 - 许多人不知道该功能。

因此，鉴于此示例表：

-- set up tables for this example 
    if exists (select id from sysobjects where name = 'OurTable' and type = 'U') 
     drop table OurTable 
    go 

    create table OurTable (ID integer primary key not null) 
    go 
    insert into OurTable (ID) values (1) 
    insert into OurTable (ID) values (2) 
    insert into OurTable (ID) values (3) 
    insert into OurTable (ID) values (4) 
    go

然后，我们可以写我们的删除代码如下：

create table #IDsToDelete (ID integer not null) 
    go 
    insert into #IDsToDelete (ID) values (2) 
    insert into #IDsToDelete (ID) values (3) 
    go 
    -- ... etc ... 
    -- Now do the delete - notice that we aren't using 'from' 
    -- in the usual place for this delete 
    delete OurTable from #IDsToDelete 
     where OurTable.ID = #IDsToDelete.ID 
    go 
    drop table #IDsToDelete 
    go 
    -- This returns only items 1 and 4 
    select * from OurTable order by ID 
    go

提高Sql Delete的性能

相关推荐