Fred,
My tests prove VFP6 SP3 is correctly reusing memo space allocation. VFP5.0a has the serious bug.
In the first test the empty memo size = 512 which is the size of the memo file header. When 1 character memos are inserted the file size goes to 512 + 64 * 100. The next 3 replaces do not affect the file size, because the memo lengths are less than blocksize-8 (there are 8 bytes used at the from of each memo block allocation to hold the memo length 4 bytes and it's deleted status of 4 bytes). As soon as the memo becomes 57 bytes long 57+8 no longer fit in the allocated space, which causes the old blocks to be deleted, and not ever reused again, and 2 blocks to be allocated for each of the new memos so the file size jumps to 512 + 100 * 64 + 100 * ( 2 * 64 ). The 100 repeated replacements of the same size cause no change in size, then going back to one character still keeps the file size the same, then emptying the memo keeps the filesize the same but does set the deleted flag for each block, then when a new 1 character the file goes up another 100 * 64 bytes in size. The test repeats the same type of size changes for all other tested blocksizes.
Looking at the VFP5 results and you see the bug,
no memo block reuse whatsoever.
Here's the test code and results (debstr is available from my website or the Wiki site):
set safety off
activate window "debug output"
debugout version(1)
testit( 64 )
testit( 0 )
testit( 1 )
testit( 1000 )
testit( 2000 )
return
function testit( pnBlockSize )
private lcName, lnTrueBlockSize, lcBlockSize
debugout " "
do case
case pnBlockSize = 0
lnTrueBlockSize = 1
case pnBlockSize < 32
lnTrueBlockSize = pnBlockSize * 512
otherwise
lnTrueBlockSize = pnBlockSize
endcase
lcBlockSize = padl( lnTrueBlockSize, 5, '0' )
lcName = "memobloat" + lcBlockSize
set blocksize to pnBlockSize
create table (lcName) ( iid i, mtest m )
use (lcName) alias testbloat
for i = 1 to 100
insert into testbloat ( iid ) values ( i )
endfor
SizeIt( "no memo contents" )
for i = 1 to 100
goto i
replace mtest with "1"
endfor
SizeIt( "one char pass 1" )
for i = 1 to 100
goto i
replace mtest with "2"
endfor
SizeIt( "one char pass 2" )
for j = max( lnTrueBlockSize-9, 1 ) to lnTrueBlockSize + 2
lcJunk = replicate( "X", j )
for i = 1 to 100
goto i
replace mtest with lcJunk
endfor
SizeIt( "memo length" + str( j ) )
endfor
for j = 1 to 100
for i = 1 to 100
goto i
replace mtest with lcJunk
endfor
endfor
SizeIt( "100 replacements" )
for i = 1 to 100
goto i
replace mtest with "3"
endfor
SizeIt( "one char pass 3" )
for i = 1 to 100
goto i
replace mtest with ""
endfor
SizeIt( "emptied" )
for i = 1 to 100
goto i
replace mtest with "4"
endfor
SizeIt( "one char pass 4" )
use
endproc
function SizeIt( lcTitle )
local i, laJunk[1]
use
=adir( laJunk, lcName+".*" )
debugout debstr( lcTitle, laJunk[1,1], laJunk[1,2], laJunk[2,1], laJunk[2,2] )
use (lcName) alias testbloat
endproc
Visual FoxPro 06.00.8471.00 for Windows [Apr 16 1999 03:38:21] Product ID xxxxx-xxx-xxxxxxx-xx
"no memo contents" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 512
"one char pass 1" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 6912
"one char pass 2" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 6912
"memo length 55" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 6912
"memo length 56" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 6912
"memo length 57" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"memo length 58" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"memo length 59" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"memo length 60" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"memo length 61" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"memo length 62" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"memo length 63" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"memo length 64" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"memo length 65" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"memo length 66" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"100 replacements" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"one char pass 3" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"emptied" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"one char pass 4" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 26112
"no memo contents" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 512
"one char pass 1" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 1412
"one char pass 2" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 1412
"memo length 1" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 1412
"memo length 2" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 2412
"memo length 3" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 3512
"100 replacements" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 3512
"one char pass 3" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 3512
"emptied" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 3512
"one char pass 4" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 4412
"no memo contents" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 512
"one char pass 1" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 51712
"one char pass 2" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 51712
"memo length 503" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 51712
"memo length 504" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 51712
"memo length 505" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"memo length 506" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"memo length 507" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"memo length 508" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"memo length 509" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"memo length 510" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"memo length 511" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"memo length 512" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"memo length 513" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"memo length 514" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"100 replacements" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"one char pass 3" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"emptied" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"one char pass 4" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 205312
"no memo contents" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 512
"one char pass 1" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 101000
"one char pass 2" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 101000
"memo length 991" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 101000
"memo length 992" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 101000
"memo length 993" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"memo length 994" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"memo length 995" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"memo length 996" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"memo length 997" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"memo length 998" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"memo length 999" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"memo length 1000" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"memo length 1001" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"memo length 1002" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"100 replacements" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"one char pass 3" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"emptied" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"one char pass 4" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 401000
"no memo contents" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 512
"one char pass 1" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 202000
"one char pass 2" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 202000
"memo length 1991" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 202000
"memo length 1992" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 202000
"memo length 1993" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"memo length 1994" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"memo length 1995" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"memo length 1996" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"memo length 1997" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"memo length 1998" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"memo length 1999" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"memo length 2000" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"memo length 2001" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"memo length 2002" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"100 replacements" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"one char pass 3" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"emptied" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"one char pass 4" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 802000
Visual FoxPro 05.00.00.0415 for Windows [Oct 21 1997 13:31:18] Product ID 58263-335-3386654-94113
"no memo contents" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 512
"one char pass 1" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 6912
"one char pass 2" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 13312
"memo length 55" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 19712
"memo length 56" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 26112
"memo length 57" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 38912
"memo length 58" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 51712
"memo length 59" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 64512
"memo length 60" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 77312
"memo length 61" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 90112
"memo length 62" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 102912
"memo length 63" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 115712
"memo length 64" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 128512
"memo length 65" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 141312
"memo length 66" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 154112
"100 replacements" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 1434112
"one char pass 3" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 1440512
"emptied" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 1440512
"one char pass 4" "MEMOBLOAT00064.DBF" 1261 "MEMOBLOAT00064.FPT" 1446912
"no memo contents" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 512
"one char pass 1" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 1412
"one char pass 2" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 2312
"memo length 1" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 3212
"memo length 2" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 4212
"memo length 3" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 5312
"100 replacements" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 115312
"one char pass 3" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 116212
"emptied" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 116212
"one char pass 4" "MEMOBLOAT00001.DBF" 1261 "MEMOBLOAT00001.FPT" 117112
"no memo contents" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 512
"one char pass 1" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 51712
"one char pass 2" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 102912
"memo length 503" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 154112
"memo length 504" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 205312
"memo length 505" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 307712
"memo length 506" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 410112
"memo length 507" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 512512
"memo length 508" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 614912
"memo length 509" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 717312
"memo length 510" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 819712
"memo length 511" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 922112
"memo length 512" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 1024512
"memo length 513" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 1126912
"memo length 514" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 1229312
"100 replacements" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 11469312
"one char pass 3" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 11520512
"emptied" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 11520512
"one char pass 4" "MEMOBLOAT00512.DBF" 1261 "MEMOBLOAT00512.FPT" 11571712
"no memo contents" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 512
"one char pass 1" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 101000
"one char pass 2" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 201000
"memo length 991" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 301000
"memo length 992" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 401000
"memo length 993" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 601000
"memo length 994" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 801000
"memo length 995" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 1001000
"memo length 996" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 1201000
"memo length 997" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 1401000
"memo length 998" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 1601000
"memo length 999" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 1801000
"memo length 1000" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 2001000
"memo length 1001" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 2201000
"memo length 1002" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 2401000
"100 replacements" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 22401000
"one char pass 3" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 22501000
"emptied" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 22501000
"one char pass 4" "MEMOBLOAT01000.DBF" 1261 "MEMOBLOAT01000.FPT" 22601000
"no memo contents" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 512
"one char pass 1" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 202000
"one char pass 2" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 402000
"memo length 1991" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 602000
"memo length 1992" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 802000
"memo length 1993" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 1202000
"memo length 1994" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 1602000
"memo length 1995" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 2002000
"memo length 1996" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 2402000
"memo length 1997" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 2802000
"memo length 1998" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 3202000
"memo length 1999" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 3602000
"memo length 2000" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 4002000
"memo length 2001" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 4402000
"memo length 2002" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 4802000
"100 replacements" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 44802000
"one char pass 3" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 45002000
"emptied" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 45002000
"one char pass 4" "MEMOBLOAT02000.DBF" 1261 "MEMOBLOAT02000.FPT" 45202000>>Ed,
>>
>>>Nope - VFP does not reuse blocks in a memo field when the field is updated, so each REPLACE is expensive - it create new blocks to write the entire field content fresh each time you update it.
>>
>>This isn't true. VFP will reuse the allocation if the content still fits. There is a bug in VFP5 which prevents memo reuse but it was fixed in VFP6.
>
>David,
>
>Are you sure it's fixed in VFP6/SP3? I just tried a loop that replaces the contents of a memo field 1000 times:
>
>FOR x=1 TO 1000
> REPLACE memfield WITH REPLICATE('x',100)+STR(x)
>ENDFOR
>>
>There was still substantial bloat that required pack memo to remove. The size of the file didn't change until I closed the table and then re-USEd it.
