Understanding text record memory usage

[NotSoRandomOne]

Doing some Access work again, and wanted to make sure I understand how memory is used in text records, in order to design a DB as efficiently as possible.

If I am wrong in the following, please correct me. If I'm not wrong, let me know and I'll continue on my journey. Thanks!

In Access, each record is something like a liked list, or an arbitrarily-wide tree in C++ or another language. The values for things like numbers and currency may actually be held in the root node of the record - that isn't important. But for text values the node would hold a pointer to the actual text string (like a std::string in c++). If the string is not used, querying it would return 'True' for an IsNull test, and no memory would be used.

If the string is used, on the other hand, only enough memory would be allocated to actually hold the string. So even if the text size is specified at '255' in the table definition, a value of "Hello World" would only take up 11 bytes or so (~22 if Unicode), + any overhead for the string itself.

I am led to this conclusion by John Vinson's post in this thread: https://answers.microsoft.com/en-us...s/73515d8b-af69-4058-b882-279038ee1b99?page=1.

Thanks for any feedback!

PS - using Access 2010

 

[theDBguy]

Hi. I don't have any reference, but I do agree with your assessment.

 

[NotSoRandomOne]

Thanks!

 

[arnelgp]

Access specifications - Access (microsoft.com)
the 4K limit (based on my test) does not only apply to Text field
but also for other fields (not including Memo and Ole, Attachments, MVF).
that include short text, numeric fields, autonumbers.

 

[NotSoRandomOne]

My main concern was whether the 2GB limit was filled up by blank memory on each record containing a text field, or if it was optimized to just use the memory required for the actual text lengths. Thankfully, it seems to be the latter of these, unless you know conflicting information.

 

[The_Doc_Man]

If the string is not used, querying it would return 'True' for an IsNull test, and no memory would be used.

Strings can exist as zero length. Depending on how the record is created, it might not have nulls. It will return true for an IsEmpty test. Memory is ALWAYS used in the record for a string because of a structure called a DESCRIPTOR. (You can look that up on the web.) We have to admit that the internal specifications for Access do not tell us EVERYTHING about the structure of such records, but basically, from a few articles I have seen over the years, a record looks like this:

Each field in a record is described by a single data type entry with a field name and a field type and a field offset/position. This description occurs ONCE for each field in the FieldDefs collection applicable to that record.

For short text fields, the data type "Short Text" leads to a secondary descriptor that always takes up the same amount of space. This descriptor contains a size and an offset that gets added to the position of the last "natural" byte of the record. So if you have a record that is [Integer, ShortText, Long Integer, ShortText, Yes/No field] you actually have

{binary integer}{descriptor with size and offset to s1}{binary long integer}{descriptor with size and offset to S2}{binary byte}{s1}{s2}

or something very much like that. IF the record was created normally, it will not be a null. It will be a descriptor showing zero for the size. The only time I think you can ever truly get nulls is if you have a LEFT JOIN and a string would have come from the dependent table if there had been a record in place - but there isn't. THEN you would see the null. For an INNER JOIN or a non-joined record, I wouldn't bet on having a null.

My main concern was whether the 2GB limit was filled up by blank memory on each record containing a text field, or if it was optimized to just use the memory required for the actual text lengths.

You are correct. Access tends to use "sparse" techniques that just leave a size indicator of 0 (in which case the offset is immaterial). NOTE that this is true only for text-oriented fields. An embedded "blank" image storage size would depend on the image's encoding method.

 

[NotSoRandomOne]

Thank you The_Doc_Man! Extremely informative!