Number of Records versus Record size

[Ruslan Zasukhin]

On 11/6/05 12:56 AM, "Charles Cohen" <rwc1717 at shaw.ca> wrote:

Hi Charles,

> On 5-Nov-05, at 8:17 AM, Ruslan Zasukhin wrote:
> 
> Because Varchar use less disk space.
> 
> String[500] will use 500 bytes ALWAYS, even if you store there 10 chars.
> 
> Back again...
> 
> After our conversation concluding that VarChars are a more efficient
> storing method....I adapted two of the easiert tables first...
> 
> On one table it should not have changed the size much as there was only
> one field whose data would vary in length....but I switched it to
> VVarChar(9)...the longest string that would be written.

Look. For SHORT strings, 2-20 bytes, Vstring[N] is the best.

For strings as URLs, when can be 200 chars or 10 chars, better to use
varChar.


> The second table contains 8 feilds.....given all these fields can
> contain data of varying length...I switched them all....one was a
> vstring field was set to 100...and there are 102 records...seemd like
> it should make some difference..it did....
> 
> I apologize in advance for the conclusion... the database I began with
> was 2.4 megs...it is now 3.2 megs..:-(.

> I have not done the table we spoke of before.....the one with the two
> vstring(500) fields...
> 
> Given the database is growing all by itself (I have not altered the
> data today)....I fear that if I adapt the remaining table to use
> VarChars instead of vstring... I may fall off of my chair.

Hmm. Then question is:

What size of EMPTY db? If after switch to VarChar it have become 3.2 MB,
I think this is okay, because when you will add MANY records, you will see
win comparing to String[N].

Again,

Let you make String[500] and VarChar[500]

LEt you store there 1000 strings of 20 bytes.

String[500]     in fact eat 1000 * 500 = 500,000 bytes

Varchar[500]    eat (20 + 8) * 1000 = 28,000


 
> It took some difficulty in creating new fields...before removing the
> old one's...I could not edit to my specs inside Studio (2.0Vb16) ie:
> change the length of the varchar fields using the property editor (they
> were set to 20)....

Yes I have see this problem in Vstudio.
Must be fixed.

> I created new fields...wrote the old information to
> them...after using studio to remove the old fields...I then I used
> first studio and then my app to defragment and compact before I checked
> the sizes.  Hopefully this is not the reason that it grew.

Did not you note if compact really have reduce size ?

May be try do XML dump and load.
Then you will get really clean and fresh db
 
> So,  any thoughts on whether I should continue to adapt my vstrings to
> varchars?


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]