By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,589 Members | 2,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,589 IT Pros & Developers. It's quick & easy.

SQL on Fire - Mass Deleting Data.

P: n/a
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.

Oct 21 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Oct 21 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.