現象:
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