Re: SQL Mass Delete Vs. READ/DELETE

If you have a key to these records you just can delete on this key.
That way the record do not have to surface and are delete deep down in the DB.

Otherwise it will not matter that much.
Both methods will have to do somekind of select and will consume comparable
totals of resources.

Regards,
Eduard.

----- Original Message ----
From: Brian Piotrowski <bpiotrowski@xxxxxxxxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxx
Sent: Thursday, June 21, 2007 12:23:48 PM
Subject: SQL Mass Delete Vs. READ/DELETE


Hi All,



I don't want to start a whole new flame war on the merits of SQL vs.
READ/DELETE, but I wanted to get some opinions as to the best way to
tackle a problem.



I have quite a few tables that have a large number of records in them
("large" to us is 13 million). I have two methods I am currently
testing and I wanted to know which is the best method to get rid of old
records:



Method #1 - Perform a mass delete using set criteria in an SQL
statement:

C/Exec SQL

C+ delete from sst01 where dsetdt <= int(:tmyyyymmdd)

C/End-exec

C*



Method #2 - Start at the beginning of the PF and examine each record.
Delete out those that meet the criteria:

read SST01;

dow not %eof(SST01);

if dsetdt <= %int(tmyyyymmdd);

delete rsst01;

ENDIF;

read SST01;

ENDDO;



Any thoughts?



Thanks!



/b;



-=-=-=-=-=-=-=-=-=-=-=-=-=-
Brian Piotrowski
Assistant Mgr. - I.T.
Simcoe Parts Service, Inc.
Ph: 705-435-7814 x343
Fx: 705-435-6746
-=-=-=-=-=-=-=-=-=-=-=-=-=-