On 10/28/2017 8:58 AM, [email protected] wrote:
On 2017-10-28 07:17, Charlie-gm wrote:
Whatever the case, please don't do something idiotic like declare
"VARCHAR(4000)" for fields that will never have more than 20
...
I don't do that but I thought using varchar(n) fields were supposed to
be space-efficient in a sense that it just used a extra 1 or 2 bytes
up front at the head of the record to identify the true length? It's
not really allocating the space like a CHAR(n) field would...right?
The main problem is the database engine, and anything that has to
allocate memory from data from the database engine, has to constantly
assume the field size could jump up to 4000 (or max length, etc), at any
time.
While space is not immediately allocated on disk, memory, etc, the
engine has to assume it will be at any given instant. Some of them do
"guesses" and watch "utilization" of fields, etc and will allocate
chunks according to those guesses (recall that most DB servers do not
just allocate disk space on each row/field update, they do it in
chunks). But it's a waste of processing power and disk storage compared
to knowing your data and doing even just a little data analysis.
I've seen "enterprise" app databases that get up to 1TB in size, and the
dev team says "we need more disk space!!! we're running out!!!"... I
then, took that database design, did a little database analysis on it,
and got it into about 10GB of VFP tables (including indexes). Of course,
that does not account for engine transaction logs, etc. But those were
regularly purged (data archives were kept on different servers
altogether). In the DB Server design, I saw varchar('max <whatever that
value is>') all over the place. Now this was an Oracle database server,
so there may be some differences between engines.
So while some claim that varchar will only use up disk space (and
memory) based on the number of characters used, in practice that is not
completely accurate. And while char can also be inefficient if too large
for what is used, it will "scale" at a predictable rate and require less
"processing" burden on the engine.
And, of course, there is a ton of additional "theory" and discussion
about this across the web, so you may want to do more research.
-Charlie
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
