Is there a tool that analyses a table then reconfigures the data field size to be optimum? (1 Viewer)

[peskywinnets]

Maybe, my grasp is insufficient, but let's say a table was initially created with a lot of short text fields - but at the time of creation it wasn't known how long the text to be stored there would be.... so 255 bytes was selected as a default Field Size.

Am I correct in thinking that if 255 is chosen that every record for that field will have 255 bytes 'reserved'?

but then, what if it transpires the actual text stored in that field was ultimately only say 32 bytes max .... surely it would then make sense to change the value of that field to something like 40 bytes (allowing a bit of headroom)....this would reduce the size of the table?

So, is there a tool/utility that scours the table, checking the actual maximum length of string stored in a short text field... and then gives the option to change the setting of that table field automatically - thereby reducing the size of the database?

or is my line of thinking wrong?

 

[theDBguy]

Hi. You don't need to worry. If the field size is set to 255, that is just the upper limit, but the field only occupies the actual size of the text value.

 

[CJ_London]

Am I correct in thinking that if 255 is chosen that every record for that field will have 255 bytes 'reserved

No. It is just a maximum for the short text datatype. It will take up 2 bytes per character plus (I seem to recall) 2 bytes to define the length

When a record is updated, it is rewritten to disk, leaving a ‘gap’ where it was originally which is why the db grows. Compacting rewrites the records to a new file removing these gaps

 

[isladogs]

I agree with the previous comments. Spend your time doing other types of optimisation.

The only time I ever did this was for a huge table with data for 2.6 million UK Postcodes and with well over 50 fields.
The table size was over 1.6 GB and I wanted to reduce it as much as possible due to the total 2 GB limit for Access databases.

So I changed all number fields to the smallest possible for each field e.g double to single, long to int, int to byte.
At the same time, I also reduced the field sizes for text fields, as in this case, the data structure guaranteed the maximum lengths for perpetuity.
The result of all the changes made was to reduce the table size by 210 MB.
That was definitely worthwhile but it was an extreme case.

 

[peskywinnets]

Ok, thanks guys...case closed! (though it does make me wonder why we have to even set it, if Access doesn't actually reserve the space)

 

[plog]

No tool, needed, just a query:

SELECT MAX(LEN(FieldName1)) AS FieldName1MaxLength
MAX(LEN(FieldName2)) AS FieldName2MaxLength
MAX(LEN(FieldName3)) AS FieldName3MaxLength
...
FROM YourTableName

 

[The_Doc_Man]

When you use a field type of Short Text or Long Text, the estimated / declared size doesn't actually matter for storage purposes. What REALLY matters is that the two text options use different methods of storage. Short Text uses an internal length/offset pointer to tell you how many characters were actually stored and where they were stored, always near the end of the record. Long Text needs a bigger pointer structure because the long text field isn't necessarily stored adjacent to the literal record. It is a more complex pointer.

This is not to say there is NO value in shortening the length of a text field if you wanted to impose a limit as a quality-control issue, i.e. .declare a constraint on the field. The number you put in as the maximum length IS checked when you have data entry via form, code, or query, and an error WOULD be raised if you said "16 bytes" and then tried to store 17 bytes. But structurally, whether you said 16 bytes or 255 bytes doesn't matter.

 

[RonPaii]

When you use a field type of Short Text or Long Text, the estimated / declared size doesn't actually matter for storage purposes. What REALLY matters is that the two text options use different methods of storage. Short Text uses an internal length/offset pointer to tell you how many characters were actually stored and where they were stored, always near the end of the record. Long Text needs a bigger pointer structure because the long text field isn't necessarily stored adjacent to the literal record. It is a more complex pointer.

This is not to say there is NO value in shortening the length of a text field if you wanted to impose a limit as a quality-control issue, i.e. .declare a constraint on the field. The number you put in as the maximum length IS checked when you have data entry via form, code, or query, and an error WOULD be raised if you said "16 bytes" and then tried to store 17 bytes. But structurally, whether you said 16 bytes or 255 bytes doesn't matter.

I don't believe you can index a long text column.

 

[tvanstiphout]

Ok, thanks guys...case closed! (though it does make me wonder why we have to even set it, if Access doesn't actually reserve the space)

I use the length as a constraint. E.g. in the US the StateAbbrev field would have a length of 2 (see also: Northwind 2 template databases). The principle used is to "always" use the smallest data type that fits the expected data.
"always" in quotes because for the Number field I don't often select the subtype of Byte or Integer, knowing that 32-bit values are handled most efficiently by the CPU, and I typically have a different (home-grown) range validator on such data types anyway.

 

[isladogs]

I don't believe you can index a long text column.

I don't believe you should do so but Access does allow it.

If you convert an indexed short text field to long text, Access will tell you that long text fields cannot be indexed and then remove it.
However it will let you reinstate the index afterwards. It will also let you add an index to a long text field.
In both cases with no error message

Whether that is a good idea is another matter.
I expect it would not improve performance in searches and might well have the opposite effect

 

[DHookom]

The last I checked, only the first 255 characters of the long text field were included in the index.

 

[isladogs]

The last I checked, only the first 255 characters of the long text field were included in the index.

I also haven't checked recently but that may well still be the case
For long text fields with a large number od characters, the data may not all be stored in the same location