Re: Fastest way to delete records.

["Bill Meecham" <bmeecham@xxxxxxxxxxxxxxxxxxxx>]

1. You could either create a logical using the time stamp with conditions
that satisfy your date needs and use other fields that would define a unique
record in all cases then CPYF the records from this logical to your history
file, then remove the records using the logical,
or
2. You could define an OPNQRYF that limits as you wish, run CPYFRMQRYF for
your history and then OVRDBF to your query file and remove the records.


If indexing and processing time is a problem, perhaps you could process this
in smaller batches of dates, in fact this would provide more flexibility.
and now that I see you will be doing this rather frequently, my vote would
be for the second option making part of your validity checking on the
inputted time stamp limited by the interval of time since the previous run,
or make the code smart enough to break the intervals into smaller groups of
time in the process.  Of course you could submit this job and do any of the
above at night.




----- Original Message -----
From: Quazy <quazy@SoftHome.net>
To: <RPG400-L@midrange.com>
Sent: Thursday, February 08, 2001 9:12 AM
Subject: RE: Fastest way to delete records.


> Tried that, but it took about 30 minutes just to do the select and 20
> minutes to write 1 million of the selected records.
>
> As first I need to write them to a new file before I delete them.  (forgot
> to mention that)
>
> So I would have to use two different SQL statements.
>
>
> I was hopeing to imporove on this time.
>
>
> At 01:32 PM 2/7/01 -0800, you wrote:
> >SQL or qmqry.
> >
> > > -----Original Message-----
> > > From: Quazy [mailto:quazy@SoftHome.net]
> > > Sent: Wednesday, February 07, 2001 1:04 PM
> > > To: rpg400-l@midrange.com
> > > Subject: Fastest way to delete records.
> > >
> > >
> > > I have a file with about 10 million records.  It is keyed on
> > > Company #,
> > > Employee #, and Time stamp.
> > >
> > > I need to delete all records that have a time stamp before a
> > > certain time
> > > period.
> > >
> > > Any suggestions on the fastest approach?
> > > Speed is a very important factor.
> > >
> >+---
> >| This is the RPG/400 Mailing List!
> >| To submit a new message, send your mail to RPG400-L@midrange.com.
> >| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> >| To unsubscribe from this list send email to
RPG400-L-UNSUB@midrange.com.
> >| Questions should be directed to the list owner/operator:
david@midrange.com
> >+---
>
> +---
> | This is the RPG/400 Mailing List!
> | To submit a new message, send your mail to RPG400-L@midrange.com.
> | To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
david@midrange.com
> +---

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---