RE: SQL Mass Delete Vs. READ/DELETE

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: Brian Piotrowski
>
> I don't want to start a whole new flame war on the merits of SQL vs.
> READ/DELETE,

Hee hee!  (oiling up the torches, getting out the pitchforks)


>  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:
>
> Method #2 - Start at the beginning of the PF and examine each record.
> Delete out those that meet the criteria:

You know me, I'm the strident voice against the overuse of SQL.  However, in
the spirit of the right tool for the job, this is really the perfect problem
domain for SQL.  You are talking about set processing, and SQL shines at
this.

You either have an appropriate view over the data (in the form of a logical
view or SQL INDEX) or you don't.  In either case, the system will have to do
the same work: either process by key within limits if an index exists, or
else read every record and test if one doesn't.  But with SQL, you will
avoid bouncing back and forth between the low-level database code and the
higher level of an HLL program.  Unless I'm missing something fundamental, I
to believe that SQL will be faster (possibly MUCH faster) in this case.

Joe