microoptimizing the field size of a text (VARCHAR) field

[HerrSober]

Hi there!

If you have a field which contains only VARCHAR below 30 characters:

Would it then make sense to set Field size to 30? (to get better performance).


...or is it better to leave the default field size settings?

 

[Joe4]

I always reduce it, whenever possible. It believe it should use less memory.

 

[Micron]

I always reduce it, whenever possible. It believe it should use less memory.

I have a different take. Assuming a character is 8 bits (1 byte), then 255 characters * 8 bits = 2040 bits: the maximum size of a text field in terms of storage, not memory. I realize that this depends on the character set being used. There was a time when it was important, like when processor top speeds were maxed out at 256 Mhz, a lot of RAM was 128 Mb and disk speeds were a fraction of what they are today. Now a typical laptop is, or exceeds 2.4Ghz (desktops maybe exceeding 3.5?), disk speeds are lightning fast, swap files are maybe 10 times the size that hard drives used to be and RAM is typically 4Gb or more. Unless you are dealing with millions of records, I do not think a text field limited to 25 characters (200 bits) versus 255 (2040 bits) will have a noticeable improvement on speed. You would need to use the computer processor clock to calculate the difference when retrieving tens or hundreds of thousands of records because you'd never notice it otherwise. If you do, I'd say something is not optimized. If you were scanning millions of records, you would be using SQL Server, not Access. The key is that limiting the field size affects storage, not memory allocation. BTW, I think varchar fields are sql server thing, but am not sure.

The only time I worry about the field size for text (not memo) is if I want to enforce a maximum number of characters allowed - say in a postal code field.

 

[Joe4]

Actually, the reason Micron mentions is often the driving force for me (more so then space), to help with Data Validation and limit the size of fields which I know should not be over a certain amount of characters.

 

[james_lankford]

yeah, this makes no sense

created a new Access 2010 database

created one table with an autonumber field and a 5 length text field

created one module with one sub

the sub had a loop that did one million inserts

Option Compare Database
Option Explicit


Sub doit()


Dim i As Long


For i = 1 To 1000000
    CurrentDb.Execute "insert into table1 (v) values ( 'asdfg' )"
Next


End Sub

I ran the sub and then compacted the database
then closed the database and right clicked properties and saw "size on disk" = 36,241,408

then I reopened the database, changed the size of the text field to 255
deleted the contents of the table
reran the sub
compacted and closed the database

"size on disk" = 36,237,312

so it's actually SMALLER with the larger field ???

 

[Micron]

I would not consider the measure to be an accurate accounting of the file size. Size on disk includes the effects of overhead, cluster size, allocation units and compression - depending on the file system in use by the Windows version and whether or not the drive supports compression. In NTFS, parts of a file can even be stored in the master allocation table. You should only consider file storage numbers to be a rough estimate of a file's size or space left/used on a disk. On some systems, a file size is only determined after the file handle is released, and is only reported to the OS on opening, which means you don't ever really know it's exact size. Not even sure if you could know exactly even if you used a disk management tool, but that's a bit beyond my realm of understanding.