Why would MDB filesize grow if no data change?
After compacting a particular database, its filesize grows from a user simply logging on, opening/closing a bound form (without changing data), and logging off. For example: Post-compact size = 393,244 KB
- First LogOn/OpenAndCloseForm/Log
- Second LogOn/OpenAndCloseForm/Log
- Third LogOn/OpenAndCloseForm/Log
(Size stops growing at this point, regardless of any additional LogOn/OpenAndCloseForm/Log
This particular form displays 1,000+ actual records, and four bound subforms that also display actual records. The mainform and subforms use query definition objects as their record sources. Similar behavior occurs with other forms.
The environment is 64-bit Windows 7 hosting 32-bit Access 2010 and an unsplit, legacy 2002/2003-format MDB with ULS.
Is this behavior expected, given the internal mechanics of Access? If not, what might be causing it? Any reason for concern?
- First LogOn/OpenAndCloseForm/Log
- Second LogOn/OpenAndCloseForm/Log
- Third LogOn/OpenAndCloseForm/Log
(Size stops growing at this point, regardless of any additional LogOn/OpenAndCloseForm/Log
This particular form displays 1,000+ actual records, and four bound subforms that also display actual records. The mainform and subforms use query definition objects as their record sources. Similar behavior occurs with other forms.
The environment is 64-bit Windows 7 hosting 32-bit Access 2010 and an unsplit, legacy 2002/2003-format MDB with ULS.
Is this behavior expected, given the internal mechanics of Access? If not, what might be causing it? Any reason for concern?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Gustav and Jim,
Thanks for your insight (and reassurance). Before I award points, I would like to ask if this "caching and other stuff" might explain another observation. (I will open another question if you think it appropriate.) In the production environment, the following sequence occurred:
- User A opened a particular form and held the Page Down key to traverse 100+ records with fast performance.
- User B opened the same form, and did the same thing, with similarly fast performance; but when User A tried again (without closing and reopening the form), performance was painfully slow for them, while performance remained fast for User B.
- User A closed the form, reopened it, and got fast performance again; but now performance became painfully slow for User B.
On and on this went, with additional users playing along, as if each could "steal" performance from the others, by simply reopening the form and traversing records. Might this be attributable to the way an MDB caches?
A couple of (extremely naïve) hypotheses I could formulate for the above observation, given caching:
1. The cache created for a given form by a given user is optimized for their particular system, connectivity, etc., and may be sub-optimal for other users.
2. When a user opens a form and loads data, it builds a new "active" cache for that form, and "deactivates" any caches previously built for that same form by other users.
Can either of you (or any other experts) offer any additional insights on this?
Thanks for your insight (and reassurance). Before I award points, I would like to ask if this "caching and other stuff" might explain another observation. (I will open another question if you think it appropriate.) In the production environment, the following sequence occurred:
- User A opened a particular form and held the Page Down key to traverse 100+ records with fast performance.
- User B opened the same form, and did the same thing, with similarly fast performance; but when User A tried again (without closing and reopening the form), performance was painfully slow for them, while performance remained fast for User B.
- User A closed the form, reopened it, and got fast performance again; but now performance became painfully slow for User B.
On and on this went, with additional users playing along, as if each could "steal" performance from the others, by simply reopening the form and traversing records. Might this be attributable to the way an MDB caches?
A couple of (extremely naïve) hypotheses I could formulate for the above observation, given caching:
1. The cache created for a given form by a given user is optimized for their particular system, connectivity, etc., and may be sub-optimal for other users.
2. When a user opens a form and loads data, it builds a new "active" cache for that form, and "deactivates" any caches previously built for that same form by other users.
Can either of you (or any other experts) offer any additional insights on this?
Might this be attributable to the way an MDB caches?
That would more likely be locking.
The cache is local to each machine as is all db engine operations and it's a cache of database pages. Those pages may be records, indexes, long value pages (things like memo fields), or table definitions. In regards to records, they may be yours or system ones.
There are a number of settings that control how long various things are held in the cache and how JET performs record operations (if a record is changed, how quickly is it flushed to disk, does a certain type of operation cause an implicit transaction, etc). There are also settings you set that control the placing of locks for example.
One thing that's very important to keep in mind; with JET, the server is nothing more than a file sharing device. All manipulation of the file is done by each of the clients. That's why MDB/ACCDB's are prone to corruption more so than other RDBM's like SQL Server. With no server side process, any abnormal disconnect does not allow things to roll back.
Jim.
ASKER
Jim,
Thanks for the additional insights. They leave me confused on two points:
1. The original post by Gustav implies the MDB filesize grows because of caching. How can it, if caching occurs on the local machine? Have I misinterpreted his post?
2. When users were "competing" with each other for performance, no data were changed; users simply displayed data as they traversed records. In fact, the mainform controls are locked (and AllowAdditions, AllowDeletions, and AllowEdits are False on subforms) unless the user clicks a mainform button to allow changes to the current record. How do the mechanics of locking lead to the observed behavior?
Bill
PS: Should I open a new question on this?
Thanks for the additional insights. They leave me confused on two points:
1. The original post by Gustav implies the MDB filesize grows because of caching. How can it, if caching occurs on the local machine? Have I misinterpreted his post?
2. When users were "competing" with each other for performance, no data were changed; users simply displayed data as they traversed records. In fact, the mainform controls are locked (and AllowAdditions, AllowDeletions, and AllowEdits are False on subforms) unless the user clicks a mainform button to allow changes to the current record. How do the mechanics of locking lead to the observed behavior?
Bill
PS: Should I open a new question on this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
<<It's hard to point to a specific operation and say what exactly is causing a slowdown to happen, but locking is probably it from what you've described.>>
Locking is a pretty broad concept. In this case:
- Access Options | Client Settings | Advanced | Default Record Locking = No Locks
- Access Options | Client Settings | Advanced | Open Database By Using Record Level Locking = Checked
- Main Form and Subforms | Property Sheet | Data Tab | Recordset Type = Dynaset
- Main Form and Subforms | Property Sheet | Data Tab | Record Locks = No Locks
- Main Form and Subforms | Record Source | Query Definition | Property Sheet | Recordset Type = Dynaset
- Main Form and Subforms | Record Source | Query Definition | Property Sheet | Record Locks = Edited Record
- AutoExec Code | DAO.DBEngine.SetOption Option:=DAO.SetOptionEnum.
Do any of these jump out as obvious issues, or as places to begin tweeking/testing?
Locking is a pretty broad concept. In this case:
- Access Options | Client Settings | Advanced | Default Record Locking = No Locks
- Access Options | Client Settings | Advanced | Open Database By Using Record Level Locking = Checked
- Main Form and Subforms | Property Sheet | Data Tab | Recordset Type = Dynaset
- Main Form and Subforms | Property Sheet | Data Tab | Record Locks = No Locks
- Main Form and Subforms | Record Source | Query Definition | Property Sheet | Recordset Type = Dynaset
- Main Form and Subforms | Record Source | Query Definition | Property Sheet | Record Locks = Edited Record
- AutoExec Code | DAO.DBEngine.SetOption Option:=DAO.SetOptionEnum.
Do any of these jump out as obvious issues, or as places to begin tweeking/testing?
There's no right or wrong answers there, just depends on what you want.
For example Default Record Locking on a form controls the type of lock. "No locks" does not mean that there is no locking, it means "No Edit locks" or in other words, optimistic locking will be performed instead of pessimistic. The difference between the two is when a lock is placed. With the first, it's only when the user actually goes to save the record. With the second, it's as soon as a record starts to be edited and it's not released until the record is saved or undone.
Jim.
For example Default Record Locking on a form controls the type of lock. "No locks" does not mean that there is no locking, it means "No Edit locks" or in other words, optimistic locking will be performed instead of pessimistic. The difference between the two is when a lock is placed. With the first, it's only when the user actually goes to save the record. With the second, it's as soon as a record starts to be edited and it's not released until the record is saved or undone.
Jim.
and by the way, it's certainly time for a new question(s).
Jim.
Jim.
JET has a number of internal operations where it writes to the database even if you have not.
Jim.