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)
--
Proudly developing Quality Cross Platform Open Source Games
Since 1970 with a Commodore PET 4016 with 16 KRAM
http://pews-freeware-games.org (<--- brand new)
--
_______________________________________________
Lazarus mailing list
[email protected]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
