| Subject | Re: Replace command with or without reindex? |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Sat, 27 Jun 2015 16:35:53 +0200 |
| Newsgroups | dbase.getting-started |
| Attachment(s) |
Unnamed File 1, Unnamed File 2, Unnamed File 3, Unnamed File 4, DOUSsort_no_order1.prg, Unnamed File 6, Unnamed File 7, Unnamed File 8 |
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. | |