削除データが多いので、実行時間がかかり場合の解決方法

現象：

 　DELETE FROM XHIKI WHERE (PORDER + SPACE(4-LEN(CONVERT(VARCHAR,EDA))) + CONVERT(VARCHAR,EDA))

　IN ( SELECT B.PORDER + SPACE(4-LEN(CONVERT(VARCHAR,B.PEDA))) + CONVERT(VARCHAR,B.PEDA) FROM 　　　　　SEIBAN_TABLE A, XKLOT B WHERE A.SEIBAN = B.SEIBAN AND A.DELETEDATE IS NULL

 

　　件数を調べると、「XHIKI」テーブルの総件数が1,572,135件ですが、IN句のカッコ内の個数が32,703件です。そのため、このSQL文

　　を実行する時間がかかり、2時間過ぎても終わらない現象が出てしまいました。

 

　　原因は以下の二つです。

　　　　①　削除データが多い

　　　　②　検索条件が複雑

 

対策：

　　新たにストアドプロシージャを作成して、このプロシージャから以上のSQL文の削除処理を行います、以下の通りです。

　　　　①　ワークテーブルの作成

　　　　②　以上SQL文の検索条件より取得したデータをワークテーブルへ登録

　　　　③　検索データの分割

 

実は①、②で実行時間は6分以内、③は時間も短くなっているけど、大きい影響がありません（30秒ぐらいを減らせる）、

特に一時テーブルが十分です。

 

プログラムが以下です。

　　　　

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

CREATE PROCEDURE dbo.XhikiTable_Delete
(
@TABLE_NAME VARCHAR(40)
)
AS
BEGIN

DECLARE @START_TIME VARCHAR(20)
DECLARE @END_TIME VARCHAR(20)
DECLARE @COUNT  INTEGER
DECLARE @SQL VARCHAR(200)

CREATE TABLE #TEMP_TABLE
(
PORDER VARCHAR(20)
)

CREATE  INDEX TEMP_TABLE_INDEX ON #TEMP_TABLE(PORDER) 

--BEGIN TRANSACTION

    SET @COUNT = 0
    SET @START_TIME = CONVERT(VARCHAR,GETDATE(),20)
 
 INSERT INTO #TEMP_TABLE(PORDER)
 (SELECT B.PORDER + SPACE(4-LEN(CONVERT(VARCHAR,B.PEDA))) + CONVERT(VARCHAR,B.PEDA) AS PORDER
 FROM SEIBAN_TABLE A, XKLOT B
 WHERE A.SEIBAN = B.SEIBAN AND A.DELETEDATE IS NULL)
 
 SET @COUNT = (SELECT COUNT(*) FROM XHIKI  WHERE (PORDER + SPACE(4-LEN(CONVERT(VARCHAR,EDA))) + CONVERT(VARCHAR,EDA)) IN ( SELECT PORDER FROM #TEMP_TABLE ) )
 
     BEGIN
  DECLARE @i INTEGER
  SET @i = 0

  SET @SQL='DELETE  FROM '+ @TABLE_NAME +' WHERE (PORDER + SPACE(4-LEN(CONVERT(VARCHAR,EDA))) + CONVERT(VARCHAR,EDA)) IN ( SELECT PORDER FROM  #TEMP_TABLE ) '
  
  WHILE(@i < @COUNT/10000+1)
  BEGIN
   SET ROWCOUNT 10000

   EXEC(@SQL)

   SET @i =@i +1
  END
 END
     SET @END_TIME = CONVERT(VARCHAR,GETDATE(),20) 

DROP TABLE #TEMP_TABLE

IF @@ERROR<>0
    BEGIN
--        ROLLBACK TRANSACTION
        PRINT '------------------------------'
        PRINT '-                            -'
        PRINT '- XHIKI  DELETE ABORT END    -'
        PRINT '-                            -'
        PRINT '------------------------------'
        PRINT 'Error encountered, ' + CONVERT(VARCHAR,@@ERROR)
    END
ELSE
    BEGIN
--        COMMIT TRANSACTION
        PRINT '------------------------------'
        PRINT '-                            -'
        PRINT '- XHIKI INSERT DELETE END   -'
        PRINT '-                            -'
        PRINT '------------------------------'
        PRINT ' START TIME         : ' + @START_TIME
        PRINT ' DATA  COUNT       : ' + CONVERT(VARCHAR,@COUNT)
        PRINT ' LOOP  i                 : ' + CONVERT(VARCHAR,@i)
    PRINT ' END   TIME            : ' + @END_TIME
      END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO