在代码中与在存储过程中循环执行DELETE

本文关键字：循环 执行 DELETE 过程中 存储 代码 存储过程 | 更新日期: 2023-09-27 18:13:53

我一直在努力从数据库中删除大量旧数据。5个不同的表中的每一个都有多达50M行需要删除。没有一条删除语句可以处理这么多的数据，所以我必须一次循环删除一些数据。我的问题是，与在应用程序代码中循环相比，在存储过程中循环是否有明显的性能提高。现在具体来说，我使用DB2 (9.7 CE)，并使用c#编码。对于存储过程，我使用:

--#SET TERMINATOR ;
DROP PROCEDURE myschema.purge_orders_before;
--#SET TERMINATOR @
CREATE PROCEDURE myschema.purge_orders_before (IN before_date TIMESTAMP)
    DYNAMIC RESULT SETS 1
P1: BEGIN
    DECLARE no_data SMALLINT DEFAULT 0;
    DECLARE deadlock_encountered SMALLINT DEFAULT 0;
    DECLARE deadlock_condition CONDITION FOR SQLSTATE '40001';
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET no_data = 1;
    -- The deadlock_encountered attribute is throw-away,
    -- but a continue handler needs to do something,
    -- i.e., it's not enough to just declare a handler,
    -- it has to have an action in its body.
    DECLARE CONTINUE HANDLER FOR deadlock_condition
        SET deadlock_encountered = 1;
    WHILE (no_data = 0 ) DO
        DELETE FROM 
            (SELECT 1 FROM myschema.orders WHERE date < before_date FETCH FIRST 100 ROWS ONLY );
        COMMIT;
    END WHILE;
END P1
@
--#SET TERMINATOR ;

它的方法被毫不客气地从这个线程中移除。我的编程方法如下:

public static void PurgeOrdersBefore( DateTime date ) {
    using ( OleDbConnection connection = DatabaseUtil.GetInstance( ).GetConnection( ) ) {
        connection.Open( );
        OleDbCommand command = new OleDbCommand( deleteOrdersBefore, connection );
        command.Parameters.Add( "@Date", OleDbType.DBTimeStamp ).Value = date;
        int rows = 0;
        int loopRows = 0;
        int loopIterations = 0;
        log.Info( "starting PurgeOrdersBefore loop" );
        while ( true ) {
            command.Transaction = connection.BeginTransaction( );
            loopRows = command.ExecuteNonQuery( );
            command.Transaction.Commit( );
            if ( loopRows <= 0 ) {
                break;
            }
            if ( log.IsDebugEnabled ) log.Debug( "purged " + loopRows + " in loop iteration " + loopIterations );
            loopIterations++;
            rows += loopRows;
        }
        if ( log.IsInfoEnabled ) log.Info( "purged " + rows + " orders in " + loopIterations + " loop iterations" );
    }
}

我执行了一个VERY原始测试，在开始和结束处打印一个时间戳，并在每个10,000之后跳出循环。上述测试的结果是，存储过程删除10,000行需要6分钟多一点的时间，而编程方法只需要不到5分钟。由于它是原始的，我想我能得出的唯一结论是，它们在实践中可能会有非常小的差异，并且在c#代码中保持循环允许更动态的监视。

说了这么多，还有其他人对这个主题有什么看法吗?您能解释一下如果使用存储过程方法，我可能会得到哪些潜在的好处吗?特别是，如果Serge Rielau一直关注这个站点，我很想听到您的意见(似乎他是所有其他人在谈到这种DB2废话时所提到的忍者……)

--------------编辑---------------------

在LOAD REPLACE之后进行某种类型的导出如何?以前有人这样做过吗?有没有我可以效仿的例子?这意味着什么呢?

在代码中与在存储过程中循环执行DELETE

如果要删除的记录数量占总数的很大一部分，那么将好的记录复制到临时表中，清空原始表，然后将临时表复制回来会更便宜。这样做的最佳方法在rdbms之间是不一致的;例如，有的支持TRUNCATE，有的不支持。

尝试使用TOP命令。我假设您对日志文件的大小有问题(这就是为什么您不能直接使用Delete from table命令)。

所以你可以这样写你的查询:

DELETE TOP 10000
FROM myschema.orders
WHERE date < before_date

然后循环此命令，直到删除的行= 0;

相关文章：