Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Memo Size vs. Text Size (A2K-SR1)

    I have a table of 10 fields, 2 of which are memo fields. The memo fields are not used on all records. What I would like to know is: When I get up into the hundreds of thousands of records will there be any difference to the final compacted size between if I use maximum size text fields or memo fields and/or if I split them out into separate tables. Testing with only a few thousand records I cannot see any difference between the four different scenarios. Whats the real deal?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo Size vs. Text Size (A2K-SR1)

    To the best of my knowledge, a text field can be a maximum of 255 records and if you put 2 of those text fields in a table, the dbms will be saving all that space in every record, whether you have used them at all, used them to capacity or not. Potentially, a great resource waster.

    Memo fields are handled differently. The dbms will, transparent to you, will create other table(s) for your memo entries, only creating a record when a memo field is used.

    So it seems that a memo field will do what you want in the most efficient manner.
    Someone else may have a more technical explanation.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo Size vs. Text Size (A2K-SR1)

    ThomasW, that's what I thought, but, I just received a private email that said both memo and text fields only store the data entered, regardless of the field size setting. Blank text or memo fields do not reserve space or add to the size of the DB. Separating the fields out to other tables would actually increase the size due to storing additional recordID's to connect the tables.
    Can anyone confirm or refute this?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memo Size vs. Text Size (A2K-SR1)

    The straight scoop is as follows:
    Text fields require 1 byte for every character in the field (assuming you are not using UNICode which takes two bytes), plus a few overhead bytes for the field. So a text field with size 255 only takes that much room if it contains 250+ characters. A memo field actually always takes at least 16 bytes regardless of it size. Memo fields are actually stored on linked pages (2KB I believe) that are separate from the page the record is stored on, and can be very large. So if you had lots of records with no data, the text version would be a little bit smaller.

    The decision really revolves around whether you need fields longer than 255, and whether you want to risk the add fragileness of memo fields - they do tend to corrupt more easily than text fields. Hope this helps.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Memo Size vs. Text Size (A2K-SR1)

    ... And having two memo fields in the same table just doubles your chances of corruption.
    Charlotte

  6. #6
    Lounger
    Join Date
    Nov 2001
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo Size vs. Text Size (A2K-SR1)

    What would cause them to become corrupted?

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo Size vs. Text Size (A2K-SR1)

    Well, it looks like text fields it is. Do you think that splitting them out to separate tables will be any significant space savings or is it more trouble than its worth? (These fields are for comments and will be used only about 10 percent of the time.)

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Memo Size vs. Text Size (A2K-SR1)

    If the fields are only used occasionally, then creating a separate table with a one-to-one relationship to the first would allow you to only create comments records when you needed them and to easily link them to their parent record.
    Charlotte

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memo Size vs. Text Size (A2K-SR1)

    Corruption usually occurs as a result of the following:
    <UL><LI>a lockup and reboot of a workstation connected to the database, or the server where the database resides crashing
    <LI>a power failure which takes either a workstation or the server down
    <LI>a network problem which causes a workstation to loose the connection to the database[/list]It's not entirely clear why memo fields are more susceptible, but my guess is that it is because they are stored on separate pages, and there are links to be established and maintained, which takes longer than simply writing a record into a single page. Hope this sheds a little light on things.
    Wendell

  10. #10
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo Size vs. Text Size (A2K-SR1)

    Thank you all for helping to clear this up for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •