How is the data stored? 1

[tdion]

Something doesn't make sense to me about Access I want someone to explain it.

When a text field is set to 50 and a record only has 2 characters, people in this forum have said that only 2 characters will be stored (and the 48 unused will be ignored and NOT stored.)

What happens when an update is made and the 2 character entry is replaced by a 3 character entry? There is no space to store it (right?) so does the entry get put at the end of the file?

Assuming this is true, does this make a file highly fragmented over time? - Thanks

 

[tdion]

Do you people think that autonumbering with short integers will save more space than autonumbering with long integers? In the latter four bytes is saved per record as opposed to one.

As miniscule as this sounds, I am looking for the best performance possible. If it means shaving 3 bytes per record, maybe it makes a difference.

Lastly, I am interested in performance issues with storing the database completely in the computers memory (with RAMdrive or a simulilar program) ... has anyone ever used primary memory to house a copy of their database to improve access time? I am extremely interested...

 

[dhookom]

Have you checked out Tony's page. There might be some information there. I wasn't aware that you could specify an autonumber in Access that is short integer vs long integer.
I doubt that you would get the most bang for the buck by changing to small integer even if possible. There are so many other performance issues.

A RAMDrive should improve performance. Access does attempt to use as much physical memory as possible so it does have some built-in optimizing. I haven't seen any test results regarding a RAMdrive so perhaps you could post back some test results. I bet Tony would be interested in your results (tell him Duane sent you).

I would expect that if you had so many records in Access that performance became an issue, this file would be sufficiently large that a RAM Drive wouldn't be large enough.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JeremyNYC]

Further, if performance is a huge issue, I would suggest that Access is not the database for you. It's good, even great. But it doens't perform as well (in terms of speed) as other, larger, more capable databases when you are dealing with lots of records.

Jeremy


==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[alr0]

I just ran accross the following note in Access help. Processing speed and ram usage can be relevant too. Another clue as to how things work.

al


"Remarks

You should use the smallest possible FieldSize property setting because smaller data sizes can be processed faster and require less memory."

 

[CajunCenturion]

This is an interesting thread.

It is quite true that fieldsize does not affect actual storage size, but fieldsize does come into play in a few other ways.

Keep in mind that there is an overall maximum record length. Setting all text fields to 255 may open up the possibly that this error creeps into play, at of course,the most inopportune time. Also be aware that the form/report recordset (and user created recordsets) memory buffers are allocated based on the fieldsizes. The larger the field sizes, the larger these memory buffers become, especially if you have lots of fields, and lots of tables open. This could certainly impact performance over a network, and even locally, lead towards a thrashing problem depending on how much physical memory is in the machine.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[BNPMike]

"It is quite true that fieldsize does not affect actual storage size"

I doubt that is true. We've shown this for text fields but almost certainly allocating larger than necessary numeric field formats will waste space.

Your points about text fields are of course very valid.

 

[CajunCenturion]

Thank you for pointing that out BNPMike.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein