Hi Lazarus Folk,

I hope that this is not too off topic but in view of the discussions about embedded databases I thought I would share a tip from many years ago about using the XBASE "append from array aRec reindex" command with the REINDEX parameter:

* Rebuilds open indexes after all records have been changed. Without REINDEX, dBASE Plus updates all open indexes
* after appending each record from <array>. When the current table has multiple open indexes or contains many
* records, APPEND FROM ARRAY executes faster with the REINDEX option.

This is a tip which I passed onto an IT pro would was appending millions or billions of records across a WAN and found that appending WITH the index was far too slow.

http://www.dbase.com/help/2_61_5/Xbase/IDH_XBASE_COPY_TO_ARRAY_EX.htm

COPY TO ARRAY example

"The following example uses COPY TO ARRAY and APPEND FROM ARRAY to copy records between tables where the fields are the same data type, but may not have the same field names. (If the field names were the same, the APPEND FROM command would be easier.) To minimize disk access, records are read in blocks of 100.

Apologies for this being dBase code instead of PASCAL, but you'll get the idea (PEW)

PROCEDURE AppendByPosition( cSource )
   #define BLOCK_SIZE 100
   local cTarget, aRec, nRecs, nCopied
   *-- Get alias for current table
   cTarget = alias( )
   use ( cSource ) in select( ) alias SOURCE
   if reccount( "SOURCE" ) == 0
      *-- If source table is empty, do nothing
      return 0
   endif
   *-- Create array with default block size
   aRec = new Array( BLOCK_SIZE, FLDCOUNT( "SOURCE" ) )
   nCopied = 0
   do while .not. eof( "SOURCE" )
      *-- Calculate number of records to copy, the smaller of
      *-- the block size and the number of records left
      nRecs = min( BLOCK_SIZE, reccount( "SOURCE" ) - nCopied )
      if nRecs < BLOCK_SIZE
         *-- Resize array for last block to copy
         aRec.resize( nRecs, FLDCOUNT( "SOURCE" ) )
      endif
      select SOURCE
      *-- Copy next block
      copy to array aRec rest
      *-- Move from last record copied to first record in next block
      skip
      select ( cTarget )
      * --- note by PEW
      * we could have used the reindex parameter with the append
      * statement or use the CREATE INDEX command after the enddo
      * http://www.dbase.com/help/Xbase/IDH_XBASE_APPEND_FROM_ARRAY.htm
      * (old code)
      * append from array aRec
      append from array aRec reindex

* Rebuilds open indexes after all records have been changed. Without REINDEX, dBASE Plus updates all open indexes
* after appending each record from <array>. When the current table has multiple open indexes or contains many
* records, APPEND FROM ARRAY executes faster with the REINDEX option.

nCopied = nCopied + nRecs
enddo

* --- MY CODE BELOW
* --- if your database does not support reindex paramater
* --- with the append from array command
* http://www.dbase.com/help/Xbase/IDH_XBASE_INDEX_EX.htm

* --- Syntax
* --- CREATE INDEX <index name> ON <table name> (<column name> [, <column name>...])
* --- The following statement creates an index on a DBF table:

CREATE INDEX NAMEX ON employee.dbf (LAST_NAME)

* --- The following statement adds an index called ZIP on the ZIP_POSTAL column of the CUSTOMER table:

* --- another example
CREATE INDEX ZIP ON CUSTOMER (ZIP_POSTAL)

use in SOURCE

return nCopied

"The COPY TO ARRAY command uses the REST scope to copy the next block of records. Because the number of records to copy is known (it?s calculated for the nRec variable), NEXT nRec would also work, but it?s redundant, because the array has been sized to copy the right number of records. The array sizing is important because that determines the number of records that get appended with APPEND FROM ARRAY.

Peter (PEW)