REPLACING fields - what is the fastest method? 1

[wadesnj]

This originally posted in FPD area, but I use the same routine in VFP 7 apps.
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?

 

[fluteplr]

First use a scan loop and get rid of the do while skip bit.

There has been no good reason do to it the with a do while for a long, long time.

Get rid othe the = sign before progress it serves no purpose.

Post the progress routine it may be slowing you down also.

I would only call the progress routine every 10 or possibly 100 records. You can use 'mod' to do that.

In 7 try replacing type with vartype it may be faster.

 

[Mike Lewis]

wadesnj,

In addition to Flutepir's good suggestions, you can also replace the following construct:

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

With BLANK FIELDS (FIELD(x))

I haven't tied that, but I think it should work.

But all these changes won't make a big difference. On the surface, there is no reason for your code to take so long. I can only guess that the fault lies in the progress bar. Why don't you eliminate that and see what happens.

Mike



Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[thuvan1957]

Wadesnj,
You can also use the following construct:
_COMM = &quot;REPLACE FIELDS &quot;
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;
_COMM= _COMM + FIELD(x)+&quot; WITH [],­&quot;
Endif
If Type(FIELD(x))=&quot;N&quot;
_COMM= _COMM + FIELD(x)+&quot; WITH 0,&quot;
Endif
Endif && of not fields 33 or 193
Endfor
_COMM= left(_COMM,len(_COMM)-1) + &quot; REST WHILE Invlines.Date <= Menddate &quot;
goto mStartrec
&_COMM

****************************************
* time < 1 minut for all records
****************************************

ThuVan
Hanoi-VietNam

 

[wadesnj]

Thanks, all, for the suggestions, will try out and benchmark. I think a combination of SCAN REST...ENDSCAN, BLANK FIELDS, and less calls to the progress bar will be the fastest way.

 

[thuvan1957]

I don't thing that, my code is fastest way !
(faster >N/2 times (N= number of numeric and character fields)) !!!
ThuVan

 

[GriffMG]

Thuvan is right for large tables, particularly with large numbers of fields, it is probably better to assemble a single replace command for each record - rather than using multiple replaces for each field in each record...

The engine does less work, one replace per record instead of multiplying that by the number of fields.

It MIGHT be even quicker if an Eval() was used instead of the macro (I THINK they are quicker), the scan / end scan is a bit of a red herring as it does not always generate faster code - I've seen something on here showing that under some circumstances it can be fractionally slower.

Good luck



Regards

Griff
Keep [Smile]ing

 

[Mike Lewis]

wadesnj,

Please let us know the results of your benchmark.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[wadesnj]

I can see the logic in saying that a single replace statement operating on many fields should be quicker - but won't I run into the problem of statement length? Surely I will exceed the maximum number of characters with over two hundred field names in the statement?

 

[wadesnj]

I've now tested this, the biggest improvement is with the use of BLANK FIELDS (FIELD(x)).
This is the routine now:

goto mStartrec
SCAN REST FOR Invlines.Date <= Menddate
FOR x=32 to 242
IF x <> 33 AND x <> 193 && Do not clear field 33 or 193
BLANK FIELDS (FIELD(x))
Endif && of not fields 33 or 193
Endfor
mcount=mcount+1
IF MOD(mcount,10)=0
Mper=(Mcount/Maxrecs)*100
=PROGRESS(Mper,'Clearing values for matching record '+allt(str(mcount)))
ENDIF
ENDSCAN

Acting on 1011 records from 38,294, the original routine took over 8 minutes, the above took 58 seconds. Replacing the do while with scan rest and calling the progress bar less only made 1 seconds difference.

 

[wadesnj]

I've just tested ThuVan's method of creating a single replace (or blank fields) statement, and it is much quicker - < 10 seconds to replace 1011 records from 38,294.
This was the routine:

_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

There is not so much user feedback, but it is so much quicker that it doesn't matter!

Many thanks to all.

 

[thuvan1957]

wadesnj,

if You SET TALK OFF then the program runs more quickly !
SET TALK ON take 8 secconds of 10 secconds in total !
You may write:

WAIT WINDOWS &quot;Please wait for ...&quot; nowait
&_COMM
WAIT CLEAR
...

Good luck

ThuVan