Extremely large .FPT memo files being created by xBase++ app using FOXCDX database engine

Tim Callahan
Tim Callahan used Ask the Experts™
on
I have an xBase++ app that creates a table that has memo fields. The
table is created by consolidating information from several source
tables.

When I run the app complied with Clipper 5.2 the resulting .FPT file
is about 220 MB.

When I run the same app compiled with xBase++ the resulting FPT file
is approximately 20 GB.

I have the driver set up as follows in the xBase app:

// Build DBE
DbeLoad( "FOXDBE", .T.)
DbeLoad( "CDXDBE",.T.)
DbeBuild( "FOXCDX", "FOXDBE", "CDXDBE" )
DbeSetDefault( "FOXCDX" )
 
// Set up for FOX 2.x and Comix compatability
DbeInfo(COMPONENT_DATA, FOXDBE_CREATE_2X, .T. )
DbeInfo(COMPONENT_DATA, FOXDBE_LOCKMODE, FOXDBE_LOCKMODE_CLIPPER)
DbeInfo( COMPONENT_ORDER, CDXDBE_MODE, CDXDBE_COMIX )

Any help is much appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This is dependent on the way how you are creating these memo fields and also on the memo block size.

Xbase++ uses memo block size 512 bytes (probably) which can cause memo file large size when you have many short memos.
Clipper and VFP can use smaller blocks.

Also if you are updating existing memo fields then it can be done in two ways:
1) Reuse the space occupied by old memo blocks
2) Leave old memo blocks untouched in memo file and append the updated memo at the end - this generates large memo files

Does Xbase++ support some memo file PACKing? If yes then try it.
Tim CallahanPrincipal

Author

Commented:
Thanks for the input.

- by default the xBase driver should use 64 byte memo blocks but I will try setting the explicitly

- xbase does support packing but I *think* it just removes deleted records - I will give that a try

- The memos are created in the new table by copying memos from the source tables so I don't think I am appending?

- Also testing on different machines to see if it is something related to the physical disk setup

More news to come....
Tim CallahanPrincipal

Author

Commented:
p.s. the code is pretty simple - here's a condensed version:

do while ! comment->(eof())

   cmntout->(rlock())
   cmntout->note := comment->note
   cmntout->(dbunlock())

   comment->(dbskip())

enddo
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

I don't know Xbase++ language but the above code seems to replace just one row in the cmntout table or the append just disappeared when condensing the code. :-)

If memo fields are just appended then the problem is purely in memo block size or in the space reserved in memo file for each db record.
Tim CallahanPrincipal

Author

Commented:
Oops on the code. Yes the append was omittied :)

I have set the blocksize to 64 explicitly now in the database driver setup and get the same results.

It happens on both Win7 and Win Server 2016 so I don't think it is machine/OS related.

Working with xBase++ support now - will post any results as soon as I get them.
I am also interested - 20 gigs should be explained.

Thanks.
Tim CallahanPrincipal

Author

Commented:
This was user error.

xBase++ database drivers have a table component (COMPONENT_DATA) and an index component (COMPONENT_ORDER.)

I was setting the parameters on the driver as follows;
 
   DbeInfo(COMPONENT_DATA, FOXDBE_MEMOBLOCKSIZE, 64 )
   DbeInfo(COMPONENT_DATA, CDXDBE_LOCKRETRY, 1000000)

The LOCKRETRY setting should be applied to the ORDER component not the DATA component (I can't say how many times I locked at the code and docs and never caught this.)

Turns out the FOXDBE_MEMOBLOCKSIZE and CDXDBE_LOCKRETRY defines have the same numeric value. Result was I was setting a block size of 1MM.
Great it is solved!  Sounds to me like points should go to Xbase++ support :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial