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

the performance of delete with commitcount routine

P: n/a
i see in this forum to make this routine using

"DELETE FROM (SELECT 1 FROM <name> WHERE <condition> FETCH FIRST <n>
ROWS
ONLY)
Prepare this one ONCE then EXECUTE in the loop. "

it works .

but now i find it's a bad performance when the data is scattering and
no suitable index is defined.

i think the reason is it will make a tablescan for each loop .

in these case i use another procedure which use a cursor to fetch rows
, so it only perform one table scan .

but the problem is : i have to define a static cursor for each table I
want to delete . 'cause it note that " the curcor XXX is not define"
in running time , after i create the cursor dynamicly with a string and
compile it well . it's so weird . :(

May 25 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
he******@hotmail.com wrote:
i see in this forum to make this routine using

"DELETE FROM (SELECT 1 FROM <name> WHERE <condition> FETCH FIRST <n>
ROWS
ONLY)
Prepare this one ONCE then EXECUTE in the loop. "

it works .

but now i find it's a bad performance when the data is scattering and
no suitable index is defined.

i think the reason is it will make a tablescan for each loop . Your are correct in your analysis. It's not the silver bullet.
in these case i use another procedure which use a cursor to fetch rows
, so it only perform one table scan .

but the problem is : i have to define a static cursor for each table I
want to delete . 'cause it note that " the curcor XXX is not define"
in running time , after i create the cursor dynamicly with a string and
compile it well . it's so weird . :(


Something like that should do...

CREATE PROCEDURE ....
BEGIN
DECLARE txt VARCHAR(100);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR FOR stmt;
SET txt = 'SELECT 1 FROM .... FOR UPDATE';
PREPARE stm FROM txt;
OPEN cur;
....
END
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 25 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.