Subject Re: Replace command with or without reindex?
From Theo Scheffers <theo.scheffers@dohsbase.nl>
Date Thu, 23 Jul 2015 01:55:03 -0400
Newsgroups dbase.getting-started

Thank you Mervin
I'm going to test DOUSsort_no_order1.prg and bring your advise under the attention of dBase LLC

Mervyn Bick Wrote:

> On Tue, 07 Jul 2015 08:02:53 +0200, Theo Scheffers
> <theo.scheffers@dohsbase.nl> wrote:
>
> > Hello Mervyn
> > Thank you very much for your answer.
> > I'm using dBase 9.5.1 with version 7 tables and saving time is the only  
> > reason I'm using REINDEX with REPLACE
> > Indeed I have the impression that older dBase versions did not have this  
> > flaw, as the REPLACE ..  REINDEX is the already for more than 10 years  
> > in my program code.
> > I'm using REINDEX  with no problem with other commands like APPEND and  
> > UPDATE as it is necessary . If I don't use REINDEX with APPEND dBase  
> > collapses with tables of this size.
>
> I understand that when dBASE introduced OODML (Object Oriented Data  
> Manipulation Language) the underlying code of many of the old DML commands  
> was rewritten.  This could explain why things that worked 10 years ago  
> fail now.
>
> You are replacing values in over 27000 records.  None of the values you  
> are replacing is used in an index so dBASE shouldn't be fiddling with the  
> index file at all.  The fact that the replacements take so long indicates  
> that dBASE is rebuilding the index file even if it doesn't seem  
> necessary.  Without REINDEX this means that the indexes are adjusted over  
> 27000 times.  You have over 60 REPLACE commands so even with REINDEX your  
> program is rebuilding the indexes over 60 times.  With so many  
> replacements it is better to disconnect the index file completely.  There  
> is code in the dUFLP to do this but as it only takes 4 lines of code I've  
> written this into the program.
>
> After the replacements have been done it should be possible to simply  
> reconnect the old index file.  Given that we don't know what would have  
> been done to the index file if it had stayed connected it is probably  
> better to rebuild the index file from scratch.
>
> In your example program you rebuild DOHSGRSW from TMPGR35, you sort  
> DOHSGRSW to a temporary file which is then used to rebuild DOHSGRSW which  
> is then copied to SORT_US.  All this takes a LOT of time and you don't  
> really need to do this.  Create and index on the fields you use to sort  
> DOSHSGRSW into TMP5  eg
>
> index on right(replicate("  
> ",12)+casnr,12)+str(sort_us)+str(bronsoort)+database  tag SORT_US  of
> dohsgrsw.mdx
>
> Any time you need to use SORT_US use DOHSGRSW with the order set to  
> SORT_US.  If you need the SORTEER field populated and you are using DML  
> then simply replace SORTEER with SORT_US.  This will take about 15  
> seconds.  If you are using OODML then create a calculated field.
>
> If you don't keep rebuilding DOHSGRSW you will eventually end up with all  
> the SORT_?? field populated.
>
> Mervyn.
>