Re: Fastest way to delete records.

[Rich Duzenbury <rduz@xxxxxxxxxxxxxxxxxxx>]

If you're careful with record blocking, this method can be very fast.  The 
supposed 'best' blocking factor on a RISC machine is:
131072 / (RecordLength + 1), meaning cram as many records as possible into 
a single 132Kb block.

And then an override similar to:
OVRDBF     FILE(INPFILE) TOFILE(&FROMLIB/&FILE) +
            MBR(&MBR) FRCRATIO(&SRCBLK) NBRRCDS(&SRCBLK) +
            SEQONLY(*YES &SRCBLK)

It also depends what percentage of the records are to be deleted.  If 
you're going to delete only a few records, I wouldn't recommend reading all 
10M.  If on the other hand you're going to delete many or most, this could 
be a good solution.

It may also be helpful to remove logical file members (If your application 
can stand it), and then add them back at the end.  Again, it depends on the 
application and the circumstances.

Most of the methods we have been discussing don't shrink the space that the 
file takes.  If that were important, then I'd suggest adding a step to lock 
the file and RGZPFM it.

Also, we concluded that running several concurrent processes over the same 
file would be faster than running only one process.  We never went to the 
trouble of doing this, but if speed is truly a factor, you might want to 
look at it.  If the file has 10M records, submit job 1 to process records 1 
- 1M.  job 2 to process records 2 - 2M. job 3 to process records 3 - 3m, 
and so forth.  Set up your batch subsystem so that an 'optimal' number 
(you'll probably have to experiment a bit) are active at the same time.

When we did our Y2K conversions using a method similar to that describe 
above, on the machines we were using we found that about 8-10 conversions 
running at the same time was about right.  Submit many less, and CPU 
utilization drops off while the jobs wait for data.  Submit many more, and 
thrash the machine.

Regards,
Rich

At 10:26 AM 2/8/01 -0500, Terry.Rhoades@blum.com wrote:


>H
>FORIGFILE  UP   E           K DISK
>FBKUPFILE  O    E           K DISK
>C                   IF        TIMESTAMP <=  YYYYMMDD
>C                   WRITE     BKUPFILE
>C                   DELETE    ORIGFILE
>C                   ENDIF
>
>
>It's not pretty, and it's not the fastest.   But if you compile it and run 
>it in batch, it will be done before everyone is finished
>discussing it.  <G>
>
>
>
>
>
>
>
> 
>
>                     Quazy 
>
>                     <quazy@SoftHome.n        To: 
> RPG400-L@midrange.com
>                     et>                      cc: 
>
>                     Sent by:                 Subject:     Re: Fastest way 
> to delete records.
>                     owner-rpg400-l@mi 
>
>                     drange.com 
>
> 
>
> 
>
>                     02/08/01 09:13 
> AM
>                     Please respond 
> to
>                     RPG400-L 
>
> 
>
> 
>
>
>
>
>sounds good, but I can't create a logical on timestamp alone.  As then I
>would have many duplicate keys. as there are 40,000 employee numbers each
>with many timestamps.
>
>At 01:49 PM 2/7/01 -0800, you wrote:
> >Hmm...
> >create a logical on the time stamp.
> >
> >Open Combined Full procedural this file
> >Do while not LR
> >   if  TimeStamp <= ToTimeStamp
> >   Delete  Record
> >   else
> >   SetOn LR
> >   EndIf
> >End Do
> >
> >Can anyone think of a faster way?
> >
> >Regards,
> >
> >Jim Langston
> >
> >
> >Quazy wrote:
> > >
> > > 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
>+---
>
>
>
>
>+---
>| 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
>+---

Regards,
Rich

+---
| 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
+---