0

We are using ms access2010 and we are having unnecessary 50% increase of the data file problem every day. We use the compact and repair process on a daily basis at every nights. But almost every day, in the middle of day, when this huge increase happens and performance is badly affected we have to run this process again manually,after that this huge size difference disappears. I suspect the problem would be because of the internal behaviour of Access engine while updating data.

Can anyone please explain to me when updating a record how much space is wasted internally by data base engine? For instance, suppose we have a record of 100 bytes, when we update it somehow and the size decreases to 80 how much will the wasted space be? is it 20 or much more than that? Conversely, when we increase a data record by update will it be any wasted space created by the update process in data file?

any idea or suggestion on how to boost the performance would be appreciated.

Jami
  • 579
  • 6
  • 20
  • If database is multi-user then you need to split the database. Also disable row-locking, this is a HUGE source of file bloat. When updating a record, then VERY LITTLE if any bloat or file size increase should occur - (assuming row locking is disabled). So ensure your database is split. File size increase during general use should be rather small, and not be such a large problem. I suspect most of the issue here is improper setup and use (eg: not split, row locking on, and routines that use temp tables in the same file as the working datafile. – Albert D. Kallal Jul 13 '14 at 00:24
  • @kallal . The db file is already splitted. But how would it work correctly with no row locking in place in a multi user environment? – Jami Jul 13 '14 at 00:30

1 Answers1

0

You can run C&R via VBA

Public Sub CompactDB() 
    CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities").Controls("Compact and repair database...").accDoDefaultAction 
End Sub

Reasons your database can bloat (compacting only solves some of this -- decompiling / recompiling is necessary for the rest, if you code / use macros).

  1. MS Access is file-based, not server transaction based, so you're always writing and rewriting to the hard drive for a variable space. To get around this, switch to MS Access ADP files using either MDSE, which you can install from the MS Office Professional CD by browsing to it on the CD (not part of the installation wizard), or, hook the database up to a server, such as SqlServer. You'll have to build a new MS Access document of type ADP (as opposed to MDB). Doing so puts you in a different developmental regime, however, than you're used to, so read about this before doing it.

  2. Compiling. Using macros plus the "compile in background" option is no different than compiling your MS Access project by having coded in Access Basic, Visual Basic for Access, or Visual Basic using the VB Editor that comes with MS Access.

Whatever changes you made last time remain as compiled pseudocode, so you are pancaking one change on top of another, even though you only are playing with the lastest version of your code.

  1. Queries, especially large queries, take up space when they're run which is never reclaimed until you compact. You can make your queries more efficient, but you'll never get away from this completely.

  2. Locktypes, cursortypes, and cursorlocations on ADODB, depending on how you set them up, can take up a lot of space if you choose combinations that are really data intensive. These can be marshalled (configured) in such a way to return only what's necessary. There is a knowledge base article on the MDSN library at microsoft.com detailing how ADODB causes a lot of bloat, and recommends to use DAO, but this is a cop-out; what you do is use ADODB well and you'll get around this, and DAO does not eliminate bloat, either.

  3. DAO functions.

  4. Object creation -- tables, forms, controls, reports -- all take up space. If you create a form and delete it later, the space that the form is not reclaimed until you compact.

  5. Cute pictures. These always take up space, and MS Access does not store them efficiently. A 20K JPEG can wind up like an 800K or 1MB bitmap format once stored in Access, and there's nothing you can do about that in MS Access 97. You can put the image on a form and use subform references of the image where ever you want it, but you still don't get around the inefficient storage format.

  6. OLE Objects. If you have an OLE field and decide to insert, say, a spreadsheet in that field, you take the entire Excel Workbook with it, not just that sheet. Be careful how to use OLE objects.

  7. Table properties with the subtable set to [auto]. Set this property, for all tables, to [none]. Depending on how many tables you have, performance can also perceptibly improve.

You can also get the Jet Compact utility from Microsoft.com for databases that are corrupted.

Source

Mark C.
  • 6,332
  • 4
  • 35
  • 71