Replacing fields - what is the fastest method? 2

[wadesnj]

I have a 40,000 record Foxpro 2.6 table. It has 244 fields and is 67Mb in size. I want to selectively clear the values in about 200 fields in 2000 records conditional on a date range. My code at present looks like this:

goto mStartrec && The first matching line is already located.

DO WHILE Invlines.Date <= Menddate and not eof()
FOR x=32 to 242
IF x <> 33 AND x <> 193 && Do not clear field 33 or 193
If Type(FIELD(x))=&quot;C&quot;
REPLACE (FIELD(x)) WITH &quot;&quot;
Endif
If Type(FIELD(x))=&quot;N&quot;
REPLACE (FIELD(x)) WITH 0
Endif
Endif && of not fields 33 or 193
Endfor
mcount=mcount+1
Mper=(Mcount/Maxrecs)*100
=PROGRESS(Mper,'Clearing values for matching record '+allt(str(mcount)))
SKIP
ENDDO

The table is indexed on the DATE field. The routine is very slow (5 minutes plus), and it is only acting on a small number of records.

Is there a better way to do it?

 

[TheRambler]

I don't know if this is the fastest way, but you could try to change your code like this:

goto mStartrec  && The first matching line

DO WHILE Invlines.Date <= Menddate and not eof()
   FOR x=32 to 242
      IF x <> 33 AND x <> 193 && Do not clear fields
         If Type(FIELD(x))=&quot;C&quot;
            REPLACE (FIELD(x)) WITH &quot;&quot;
         Else
            REPLACE (FIELD(x)) WITH 0      
         Endif
      Endif && of not fields 33 or 193   
   Endfor   
   mcount = mcount + 1
   wait window 'Clearing record ' + ;
   allt(str(mcount)) nowait           
   SKIP
ENDDO

 

[AnanthaP]

For speed, I would go about it as below:

(1) Identify the fields by LIST STRU TO PRIINTER or whatever(You can thus bring your documentation upto date).
(2) SET TALK OFN && Does your progress bar and I believe the talk value can also be set to display every so many records ..
(2) REPLACE f1 with '', f2 with '', f3 with 0.. FOR Invlines.Date <= Menddate

The rider is of course, whether there is a limit to the length of the FoxPro command line.

End

 

[dbMark]

AnanthaP, there's likely not much speed difference between one huge REPLACE statement and many smaller ones. Also, what if one of the fields is chenged to another data type? Either all fields would have to be calculated first in the code or you risk someone changing the data type without remembering to adjust the code.

mlv1055, your code assumes that all those fields are either character or numeric type. It would be best to allow for the possibility of other data types. I also had been thinking along similar lines with an IIF() statement, until I realized how many data types there might be. Here's how an IIF() would look if there were only 2 possible data types:

REPLACE (FIELD(x)) WITH IIF(TYPE(FIELD(x))="C","",0)

If you want to test for a few other data types also, just add IIF() statements. This now handles character, memo, numeric, date and logical (the last default):

REPLACE (FIELD(x)) WITH IIF(TYPE(FIELD(x))$"CM","",;
                        IIF(TYPE(FIELD(x))="N",0,,;
                        IIF(TYPE(FIELD(x))="D",{},.F.)))

dbMark

 

[dbMark]

Oops, I had one too many commas in this example. This works:

REPLACE (FIELD(x)) WITH IIF(TYPE(FIELD(x))$"CM","",;
                        IIF(TYPE(FIELD(x))="N",0,;
                        IIF(TYPE(FIELD(x))="D",{},.F.)))

As for the original question as to how to make the code faster, I've never done any tests using REPLACE on 250 fields and thousands of records. With programs being compiled and running on computers with such fast clock speeds, the biggest factor is the table write speed. Even so, there's likely not much difference between individual REPLACE statements and combined ones.

dbMark

 

[DSummZZZ]

You can speed things up a little by replacing some commands/functions. For instance, use SCAN instead of DO WHILE ... and not EOF(). Save an extra function call to EOF().
And don't call the progress bar after every iteration, since screen updates also slow things down, and you are also calling ALLTRIM() and STR() every iteration. Call it every 10 or 100 times.
And finally, you will get the fastest updates if you can open the table exclusive:

DO WHILE Invlines.Date <= Menddate and not eof()
   *... update stuff
   .
   .
   mcount=mcount+1
   Mper=(Mcount/Maxrecs)*100
   =PROGRESS(Mper,'Clearing values for matching record ' + ;
      allt(str(mcount)))            
   SKIP
ENDDO

-change to-

SCAN REST FOR Invlines.Date <= Menddate 
   *... update stuff
   .
   .
   mcount=mcount+1
   IF MOD(mcount, 10)  &&... or 100
      Mper=(Mcount/Maxrecs)*100
      =PROGRESS(Mper,'Clearing values for matching record ' + ;
          allt(str(mcount)))            
   ENDIF
ENDSCAN

-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[NullOp]

1. Save the two fields you want to memvars

2. Scatter the current record to an array using the BLANK option

3. Set the appropriate array fields to the two saved memvar values

4. Gather the record from the array.

Might just work...

NullOp

 

[DSummZZZ]

NullOp,

FOR x=32 to 242

Note that there are more than 2 fields involved.


-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[wadesnj]

Thanks all - I think the use of SCAN REST and less calls to the progress bar will probably be the best improvement. I'll give it a try tomorrow and let you know.

 

[TheRambler]

For anyone interested, this was the best solution from the VFP forum:

_COMM = &quot;BLANK FIELDS &quot;
FOR x=32 to 242
IF x <> 33 AND x <> 193 && Do not clear field 33 or 193
_COMM= _COMM + FIELD(x)+&quot;,&quot;
Endif && of not fields 33 or 193
Endfor

_COMM= left(_COMM,len(_COMM)-1) + &quot; REST FOR Invlines.Date <= Menddate &quot;

set talk on
goto mStartrec
&_COMM

see thread184-745311

 

[NasibKalsi]

* May improve a tiny bit by

_COMM = &quot;BLANK FIELDS &quot; + field(32)
FOR x=34 to 242
IF x <> 193 && Do not clear field 193
_COMM= _COMM + &quot;,&quot; + FIELD(x)
Endif && of not fields 193
Endfor

_COMM = _COMM + &quot; REST FOR Invlines.Date <= Menddate &quot;

set talk on
goto mStartrec
&_COMM

Nasib Kalsi
Try it.