How much space does each FIELD take

[foxup]

Hi,

I have a transactions database that is reaching very close to the 2 GIG mark. I have reduced it down to bare minimum fields that I need to function (10 fields). How much space does each FIELD take on disk? I would like to figure out a way to split the 10 fields evenly so I have 2 separate databases of more or less the same size (approx 1 gig each). Basically I want more or less 2 databases of 1 gig each. is there a way to find out how space each FIELD is taking of the 2 gig so I can get close to an even split?


Thanks,
FOXUP

 

[EzLogic]

how big are the fields and what data type?
how many records do you have in the table?
are some of the data redundant? like example, account type? or city/state/etc..? where you can make it a foreign key to another table?


Ez Logic
Michigan

 

[DSummZZZ]

If you do a web search on VFP field types, one of the first entries is this:

Visual FoxPro Data and Field Types

http://msdn.microsoft.com/en-us/library/ww305zh2(v=vs.80).aspx

-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[ggreen61]

Take a look at the VFP help for each field type. For character fields it is based on the field size; i.e., C(12) uses 12 bytes. The record is a sum of all the fields. There is a set size for the header of the DBF. Memo fields are stored separately in a FPT file. Here it is stored in blocks (you can control the block size). If text is replaced (REPLACE ... WITH) that is a memo field, the original text block(s) is "abandoned" and new one(s) is appended when the block size at the last block is exceeded. The reference to the beginning block number is what is stored in the 'field value' in the DBF file. Abandoned blocks can be recovered by doing a PACK MEMO command. There is also a byte field for marking the record as deleted in the DBF file.

 

[dbMark]

Looking ahead, would you hit the 2GB limit again eventually? If any time soon, you may want to split the fields into more than 2 tables? Also, keep in mind that you need to be able to link the records from one table into the other(s). So likely you'll need to have a field common to all the tables so you can SET RELATION TO.

Or would it work for you to separate the tables by something other than fields? For example, split it apart by month, year, product type, vendor, client, state, country, etc? That way all the table structures would be identical and simplify your code.

Alternatively, have you considered placing the tables into a SQL server which has virtually no size/record limit?

 

[Olaf Doschke]

Hi foxup,

You should also consider putting old data into an archive DBF. I assume you pack this table and don't have much deleted data anyway. It's the nature of transactions to be a full history of data (not only in database transactions, also monetary transactions and others).

You could save some space of char fields, by making them memo fields and have another file with another 2gb limit, which is what ggreen61 indirectly considers. Some avoid FPT files by all means, but if you set blocksize correct and only add to it, a memo file is quite stable also against bloating effects, which only happen with often updates of already existing memo blocks not being reusable, if the updated value is longer, of course.

And it's also pretty easy to see the byte length in the array AFIELDS() generates in the 3rd column. After you decided about splitting you can double check, if recsizes are really similar simply by RECSIZE(). RECSIZE() includes a byte for the deletion mark. The file length of a DBF must be equal to HEADER()+RECCOUNT()*RECSIZE()+1, where 1 is for the EOF byte after the last record. You can check that by comparing with the file size in 2nd column of the ADIR() array.

Bye, Olaf.

 

[Mike Lewis]

Foxup,

You have had some good suggestions. The best one, in my opinion, is Olaf's suggestion of archiving old transaction data to separate files. You would keep all the open transactions in the main table, along with all closed transactions going back to, say, the start of the previous year. Then, once per year, you would copy the older transactions to a table for that specific year, then delete those transactions from the main table, then pack the main table.

If you decide to take this approach, you will need to give the user a facility to access the archived records. But, presumably, this will only be needed in exceptional circumstances, such as an audit or a legal action, so the user interface could probably be minimal.

Not only will this avoid the 2 GB problem, but it will generally reduce clutter within the application and make the system more efficient.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I agree Mike,

if the application doesn't work on older data it's even simpler to split the data this way (horizontally) then split up records/columns (vretically) and have two tables. You may SET RELATION from main table to 1:1 side table, but SQL Queries will need a JOIN and this causes much work. Adding UNIONS where needed or having a minimal seperate module in the application for archived data is most often the simpler change.

You can do as much horizontal splits as you need, while vertical splits obviously end, where each of the single table has one column, if you join them by the record number, and that get's quite dangerous

Bye, Olaf.

 

[foxup]

Hi All,

Thank you all for your great suggestions as usual.

I'm gonna see what I can do for the "Archiving" of 'old' transactions as my first option. If that doesn't pan out, I gonna give SQL a shot.


Thank you,
FOXUP!

 

[Mike Lewis]

If that doesn't pan out, I gonna give SQL a shot

Well, be aware that moving your database to a back-end server is always a big jump - not something to undertake lightly.

There are lots of advantages in using, say, SQL Server for your database, rather than VFP's own tables. But the move will need proper planning, budgeting, and some effort in coding and testing. It's not something you should do merely to solve one relatively small problem.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[foxup]

Very true Mike. SQL will probably be my last option. Thanks again.