What causes a database file size to bloat (file size increase)?

[Cosmos75]

I was wondering what can contribute to a database file bloating?

Here's what I think I know..

1) Adding then deleting files.

2) Opening a recordset using DAO and not closing it (I don't even know what that means, never used it, I don't think...).

3) Using temporary tables. (What I create a table using a make-table query pulling infor from a table and adding an new fields with a default value of zero for use as a recordsource in a form. Then use that new field for calculations for display in the form and every time that form is opened that table is again created and the extra field is set back to zero. Is that a temporary table?)

Anything else?

Compiling and Saving all modules and then Compacting will reduce the file size to it's minimum size?

Also, there is mission2java_78's post for Access 2000? http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=8948&highlight=reducing+size
Any similiar trick for an Access 97 database?


Or is there more to it?

 

[FoFa]

I think using it.
I've noticed on those I don't use, the file size remains the same.

 

[Cosmos75]

LOL!

Nice answer. I like your answer to this question as well!
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=48746

 

[Tim K.]

Here are some more.

1. Add OLE Objects into a db. If you embed pictures or documents to a db, the size will be increased in no time.

2. Open 97 db in 2000 or higher without converting it. This will add some features in so you can open a 97 db in a higher version without a prompt for options how to open a 97 db.

3. Add objects in the References. If you don't need dll, ocx, olb, tlb, etc., remove it out.

 

[FoFa]

Access uses 2K pages to store data. The database is expanded as needed to create these 2K pages for storing data. If a page is completely emptied the page is marked as FREE and added to a FREE LIST that access checks for storing data (reuse). If the FREE LIST is empty (or some lower limit unknown to me), Access grabs more space and creates FREE pages. FREE pages are not returned to the OS for reuse (reclaimed). Only compacting the DB will restore the FREE pages to the OS. Of course a Compact is really a copy of the DB rather than a release of the FREE PAGES. Once compacted Access leaves some FREE pages (I have not seen how many) and if you use them up, it adds more. Oracle and Sqlserver work on the same concept (kewl huh). I think Compacting is not needed unless you did some BIG one time things in the DB, or a whole bunch of small things (fragmented pages result with a bunch partially filled). I usually compact about once or twice a year just to limit page fragmentation.

 

[Pat Hartman]

Databases that are under development grow very rapidly and should be compacted frequently. Once they are deployed, that growth slows down except for certain types of programming practices that cause bloat.

The most common causes of db bloat are over-use of temporary tables and over-use of non-querydef SQL. Whenever you code an SQL string outside of a querydef, Access must "bind" that statement EVERY time it is run. This process takes a small amount of time and uses a large amount of workspace that is not recovered until you compact the db. Querydef's are "bound" when they are saved and when the db is compacted. Access saves its execution plan at that time and uses the saved plan at run time.

 

[bauer]

I am a little curious.

Saving a querydef, is pretty much saving a query in Access' Queries tab?

B/c I have a bloating problem, and even after turning all of my SQL strings to queries with parameters (making functions to get those parameters), and closing all of my DAO database and recordset objects, I still had pretty much the same amount of bloating. (All the queries I am running are update queries, though I'm not sure if this makes a difference)

db.Execute "qryName"

(I tested it by running a certain operation 10x before and after, and my back end grew by almost 1MB)

Thanks

-Bauer

 

[Cosmos75]

Definition of querydef

Thank you all for your replies!

I'm sorry if this is a stupid question but here goes...

What is a querydef?

 

[Pat Hartman]

A querydef is one of the stored queries that you see when you choose the query tab from the database container window.

 

[dc_sc]

File Size Growing

I thought i would reactivate this thread since it seems closest to my problem: I have an access2000 db that queries another db every few seconds to create what appears to be real-time data on a form. This is used for dispatching. I use a make-table query to pull the most recent data from the other server's huge table, then run some update queries from that data to come up with my end product table. It runs great, but the file size of the access db expands at an alarming rate, reaching 2 gigs in a few days and crashing.

I guess from reading below that i am creating temp files everytime I query, and they never go away until compacted. Is there another way to do these queries without expanding my file size. If the answer to that is no, is there a way to automatically compact every few hours or so. I was unable to create a macro or code that compacts the db.

Thanks!

 

[Pat Hartman]

You can probably do what you need to do without using make-table queries. It is the constantly saving and deleting the tables that causes the bloat. You'll need to go into more details before we can offer specific suggestions.

 

[rglman]

How to pass a variable from a module to a querydef?

I have a database with several modules that execute sql statements, and I per the information in this thread I wanted to see if I could reduce the bloating of my db by calling a querydef instead. However, I will need to pass the querydef a variable.

I'm not a VBA power-coder; What would the basic syntax for passing a variable to the querydef look like?

Any help would be appreciated!

rglman

 

[Pat Hartman]

This post has the DAO code sample you need.
http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=176367#post176367

I don't have an ADO sample.

 

[rglman]

How to pass a variable from a module to a querydef?

Thanks! I think it'll do the trick!

 

[Thales750]

Databases that are under development grow very rapidly and should be compacted frequently. Once they are deployed, that growth slows down except for certain types of programming practices that cause bloat.

The most common causes of db bloat are over-use of temporary tables and over-use of non-querydef SQL. Whenever you code an SQL string outside of a querydef, Access must "bind" that statement EVERY time it is run. This process takes a small amount of time and uses a large amount of workspace that is not recovered until you compact the db. Querydef's are "bound" when they are saved and when the db is compacted. Access saves its execution plan at that time and uses the saved plan at run time.

The opposite is true.

 

[Pat Hartman]

The opposite is true.

The opposite of what?

 

[CJ_London]

Really? Querying an 18 year old thread?

 

[Thales750]

Really? Querying an 18 year old thread?

I was working on the other similar thread and this one popped up. If the system is showing old threads shouldn't they have information that is proven to be correct?

 

[GPGeorge]

I was working on the other similar thread and this one popped up. If the system is showing old threads shouldn't they have information that is proven to be correct?

Maybe you would be able to specify what you consider to be correct, then. And what you consider to be incorrect.

If you want to provide "information that is proven to be correct", please have a go at it.

 

[Thales750]

Maybe you would be able to specify what you consider to be correct, then. And what you consider to be incorrect.

If you want to provide "information that is proven to be correct", please have a go at it.

We just had a discussion where no consensus was reached on bloat caused by querydefs. I'm not qualified to "rule" on that. But unproven opinions should be challenged.
I was specifically referring to Pat's statement that Querydefs don't create bloat. Like I said, I don't know, but many seem to think the opposite is correct.