SQL on Fire - Mass Deleting Data.

[Michel Esber]

Linux Db2 LUW V8 FP 15

I was readiong Serge´s SQL on Fire presentation (part 2), and there is
a slide that shows how to mass delete data using procedures. Something
like:

loop: LOOP
DELETE FROM (SELECT 1 FROM Table WHERE InvDate <= dt FETCH FIRST
1000 ROWS ONLY) AS D;
IF SQLCODE = 100 THEN LEAVE loop;
END IF;
COMMIT;
END LOOP loop;

I have studied the access plan of the statement above, and compared it
to the plan of the statements my application runs (a simple 'delete
from table where ID=? and timestamp between ? and ?').

The plans are exactly the same, with the same Total Cost.

Question: Should I expect delete performance improvement if I use
procedures with Fetch First X rows, like Serge´s example ? How does
this compare to single DELETE statements, in terms of performance ?

Thanks in advance.

-Michel.

[Serge Rielau]

Michel Esber wrote:

Linux Db2 LUW V8 FP 15

I was readiong Serge´s SQL on Fire presentation (part 2), and there is
a slide that shows how to mass delete data using procedures. Something
like:

loop: LOOP
DELETE FROM (SELECT 1 FROM Table WHERE InvDate <= dt FETCH FIRST
1000 ROWS ONLY) AS D;
IF SQLCODE = 100 THEN LEAVE loop;
END IF;
COMMIT;
END LOOP loop;

I have studied the access plan of the statement above, and compared it
to the plan of the statements my application runs (a simple 'delete
from table where ID=? and timestamp between ? and ?').

The plans are exactly the same, with the same Total Cost.

Question: Should I expect delete performance improvement if I use
procedures with Fetch First X rows, like Serge´s example ? How does
this compare to single DELETE statements, in terms of performance ?

Michel,

The purpose of this code is not to speed up delete.
The purpose it to achieve intermittent commits to limit log-space
consumption for a single transaction.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab