Sundial servic 2012-06-24 13:04:50
How much data can a Paradox table conceivably hold? If you’ve got humungous
amounts of data, how can you tell when Paradox is going to start
complaining, or maybe, cease to be an appropriate choice? There are a lot
of different factors which can play a role in these decisions, so rather
than give you a step-by-step tutorial I’m simply going to list each one.
** FACTOR – THE PHYSICAL STORAGE-FORMAT:
A Paradox file logically consists of up to 65,535 “blocks.” Each block is
some multiple of 2K (2,048) bytes, as specified in the BDE/IDAPI
configuration in effect at the time the table was created. (The BDE
support page at http://www.borland.com/devsupport/bde provides utilities to
change this block-size for an existing table.) A block has 6 bytes of
overhead; the rest is used for records. Any fragment of space that’s less
than a full record is wasted. The size, in bytes, of each field-type is
listed in the Paradox documentation.
The /theoretical/ capacity of the table can therefore be calculated rather
simply, _but_ this won’t be nearly the /effective/ capacity of the table.
** FACTOR – BLOCK SPLITS:
A keyed Paradox table is physically sorted in primary-key order. If you
insert a record in the middle of the primary-key sequence, that record will
be inserted in the appropriate position. To make room for the record, if
the target block is already full, the block in which the record belongs
will be “split”: the record and the surrounding records will now be in two
blocks, neither one full. Paradox rarely goes out of its way to “combine”
two blocks: THE GENERAL TRADEOFF TAKEN BY PARADOX FAVORS “SPEED,” NOT
“SPACE.” This is typical and, I think, appropriate.
** FACTOR – PRIMARY INDEX CAPACITY:
Almost every table will have, ex minimis, a primary key. The primary key is
described by a .PX file, which has the same format (and thus the same
architectural restraints) as a DB-file. This file contains the primary-key
value of THE FIRST RECORD IN EACH BLOCK, as well as a “tree” structure that
lets Paradox locate key-values quickly by a ‘divide and conquer’ approach.
Although it’s rare for the primary-index to fill up, theoretically it
** FACTOR – SECONDARY-INDEX CAPACITY:
Secondary indexes consist of two files: structurally they’re like a
separate Paradox table-file with its own primary-index file (hence “the
same architectural restraints as a table”). What they contain is EVERY
secondary-key value AND the primary-key value of that same record.
When a block is /completely/ empty, Paradox will recycle it. The file will
not change size, but the empty-block will be reused before the file grows
again. The key word is: “completely.”
** WHEN THINGS FILL UP; WHAT “PACK TABLE” DOES:
As said before, there is always a tradeoff in computing and it is “EITHER
speed OR space.” If you want a structure to be fast, it’s not going to be
as small as it theoretically could be, and vice-versa. Paradox
consistently favors speed. That means that a table-file, over time, will
have a mixture of partially-full blocks; figure about 50%. While Paradox
will occasionally “join” two blocks that are adjacent and nearly-empty,
don’t count on it.
The “pack table” option of Restructure will create a table with blocks that
are as full as they can be; which, for various reasons, is not always 100%.
It does this literally by creating a new table and copying data into it.
This is something you may need to do from time to time, but you should not
need to do it regularly.
** THE VOLATILITY OF AN INDEX IS MUCH HIGHER THAN THAT OF A TABLE:
When does the physical block-layout of a table-file actually change? Only
when records are inserted or removed. But the same is not true of an
index! Index files change each time the value of an indexed field changes.
The more indexed fields you have, the more changes occur. The more those
field values change, the more changes occur. Therefore, MOST “CAPACITY”
PROBLEMS ARE INDEX-CAPACITY. Likewise, MOST “PERFORMANCE” PROBLEMS ARE
INDEX-PERFORMANCE. Or maybe, TOO DARN MANY INDEXES!
** REGENERATING INDEXES:
Index files are designed to be a self-maintaining structure. But they do
get bounced-around a lot, changing (sometimes substantially) each time you
change a value in an indexed field. If your tables are very volatile, it
may be necessary to rebuild the index-files fairly often, just because the
indexes begin to “slow down.” This can be done with an ObjectPAL script
(or ChimneySweep) without repacking the table.
ChimneySweep(R): Fast(!) table maintenance at a click of the mouse!
Rodney wise 2012-07-01 04:29:05
Good read Mike. Thanks.
Can you tell me if the formulas used in the Form I created :
( http://www.ars-florida.com/TableDetails.zip )
are presenting and “accurate” representation for the “Total Record Capacity”
and the number of “Available Records” in a Table?
Notice, if the Table has a secondary Index, I “Halve” the number of “Total
Record Capacity” (which effects the “Available Records”) because of issues
reported with Tables with Secondary Indexes which exceed 1/2 their potential
“File Size” where the Secondary Index(s) get corrupted.
Maybe I’m adding Apples and Oranges to come up with these numbers…. I
don’t know for sure.
` . . ` . . ` -> rodney