On 2017-10-28 09:58, Charlie-gm wrote:
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.
Yeah, at my last gig, they used varchar(max) all over the place with SQL
Server. I didn't care for that. In the MySQL system I preferred
(before they made me switch to the official corporate choice of SQL
Server), I imported data into tables with a TEXT field, and the wrote a
simple optimization program after that to scan for the largest length in
the columns and then do an ALTER TABLE to make them exactly the max they
needed to be. Couldn't leave it as TEXT (or varchar-max) because they'd
come into VFP as memo fields, which I couldn't export to Excel without
ease.
_______________________________________________
Post Messages to: ProFox@leafe.com
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/39f7ae866c793389ce06845972400...@mbsoftwaresolutions.com
** 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.
