On Mon, 22 Jun 2015 16:59:28 +0200, Theo Scheffers
<theo.scheffers@dohsbase.nl> wrote:

> Dear all
> Enclosed in the zip file two variants of one dbase .prg:
> •	DOUSsort.prg
> •	DOUSsort_no_order.prg
>
> They perform replacements in the field sort_us of the dbase table  
> dohsgrsw.dbf, which is loaded with tmpgr35.DBF.
> Sort_us is not the key field.
> As the table is large the dBase help recommends to use Reindex  in the  
> Ordered table.
> However when using DOUSsort_no_order.prg the replace is only performed  
> in a part of the scope made by the ’set filter to’ and the ‘For’  
> commands.
> The records that are not replaced should receive a value sort_us=999  
> based on one of the last command, but even that is not occurring.
>
> DOUSsort.prg , without order and reindex, replaces all well.
> Can you explain this misbehavior?
> Thanks in advance
> Regards

If an active index sets a range then not all the records will be in the  
rowset.  Setting a filter can, therefore, result in records being missed.   
None of your indexes sets a range so I can't explain why  
DOUSsort_no_order.prg is not seeing all the records.

Adding the keyword REINDEX to the REPLACE command prevents dBASE from  
adjusting the indexes after each record is changed.  Reindexing will  
however, occur after each group of replacements has been made.   Your  
program does not change a key value so the indexes should, in fact, not  
need adjusting at all.  Even though it shouldn't be necessary to recreate  
the indexes dBASE appears to be doing this anyway judging by the time  
taken.

With so many replacements it is better not to have any indexes in play  
while the updates take place.  It is also not necessary to close and  
reopen the table after each group of replacements has been mad and it is  
not necessary for the table to be ordered while the replacements are made.

I've added code at the beginning of the DOUSsort_no_order.prg program to  
disconnect the index file.  As no changes have been made to key fields it  
should be possible to simply use the code to change the 28th byte back to  
chr(1) but it is probably safer to recreate the indexes.

As I don't know what version of dBASE you are using I've used the old  
low-level file commands to create the code to disconnect the indexes  
rather than a file object.

I'm using dBASE 8.1.3 and it doesn't seem to play nicely with level 4  
tables.  To create the indexes I had to specify the .MDX file name which  
is not necessary with level 7 tables.  You may need to change this  
depending on the version of dBASE you are using.

The changes to your program are such that it now runs in just under 4  
minutes instead of the approximately 15 minutes the original program  
took.  I can't see why any blank sort_us fields weren't being filled with  
the value 999 as this is now being done.

I take it that populating the table DOHSGRSW from tmpgr35 was purely for  
the test program.  If you don't have to rebuild DOHSGRSW every time the  
program will be even faster.


Mervyn.