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

[Pat Hartman]

Whew, that makes sense. I thought since the record count value was there and editable, I could set it. I agree, the number of rows has little bearing on the time. I just couldn't make sense of the counts and why I kept blowing up the db with so many rows.

The comparison between 1 and 3 is relevant. The other 4 are Access methods which always take longer and can't really be compared to #1. Only #3 can be compared to #1.

All we were talking about here is how much overhead do you get by having to recreate the execution plan each time the query runs rather than being able to use the saved plan using a querydef and since the time the calculation takes is small, you need to have a high loop count. I'm going to change the select query to use the record count control so I can test the effect of the loop without blowing out the size of the databases with 10,000 rows each time.

Thanks

 

[Thales750]

For example, if you prefer keeping all code in one place to allow an overview and easy editing, using SQL statements may be best. Conversely if you wish to keep as much as possible separate from VBA code, using saved queries works well

Religion and Politics on the main board? LOL

 

[isladogs]

Religion and Politics on the main board? LOL

What??????

 

[Thales750]

What??????

You know, making a statement that provokes strong responses.

It was an attempt at humor.

 

[dalerazey]

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.

 

[Gasman]

All these points are well and good, but I have to say, I think a good maintenance routine is essential in ANY DB platform. Because of the frequent imports I have to do and use of temp tables, bloat is something I have come to accept and at least once a month, do a C&R on the BE. At the end of the year, I do a decompile and C&R on both the FE and BE and "push" them out.

Even if there were a way to do away with this, I wouldn't because the sense of well being I get is irreplaceable - not to mention gives the customer the impression that my services are needed.

I *thought* the decompile was for code?
Do you have code in the BE?

 

[The_Doc_Man]

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.

Every query has some "baggage" that relates to making a list of affected records. In the simplest case, the "bloat" is minuscule and you can ignore it for a while. Frequent updates and/or deletes in large numbers will "thrash" your DB, though technically the deletes don't cause bloat right away. They just set you up for bloat at the next append operation since there is no guarantee you will re-use the deleted space immediately and in fact good odds that you WON'T use the deleted space.

Auto-compact CAN occur but is highly NOT recommended because of issues of data safety. For instance, running an auto-C&R from your target DB is tricky since at that moment you actually have that DB open. The nature of the C&R is that it makes copies of everything and then does some file trickery. BUT if you have that file open then that file trickery is not guaranteed to fly, depending on what it means for your DB to be open.

What IS recommended is that if you are going to run a C&R on your DB, you make a backup copy BEFORE you attempt anything else. Compact & Repair operations HAVE been known to fail. Having that preliminary backup copy can keep you going even after a catastrophic failure.

By the way, that was your first post. Welcome to the forums!

Back to the technicalities... How often you need to run C&R depends on bloat growth rate which in turn depends on table size and what percent of that table gets "churned" during regular operations. We can't tell you how often to C&R - you have to decide that for yourself based on disk sizing and the availability of down-time sessions to allow yourself to perform general DB maintenance. This in turn depends on whether you are the sole user or whether the DB is shared among many users. This kind of decision is not trivial because of the number of contributing factors.

 

[Thales750]

Databases that are shared should run on SQL Server. There is a free version; we should all use it. KenHigg made an incredible version management program for keeping frontends current across the enterprise. At the chemical plant where we built an ERP, we used it for about a hundred users, many of them off-site on a VPN and remote desktop.

In that case, all the admin guy has to do is change the version number of the control DB file, and it automatically replaces them with the next loading of the DB. This method does not allow for any data to be stored in the FE. That's generally bad practice anyway.

 

[cheekybuddha]

Databases that are shared should run on SQL Server.

That's a bit of a blanket statement with no qualification.

Additionally it costs a lot of money!

If you are on a LAN then an Access backend can likely do the job as well.

Otherwise, what about MySQL, PostgreSQL, SQL Server Express, etc ... as options?

 

[cheekybuddha]

There is a free version

Oops! Missed that!

 

[Thales750]

That's a bit of a blanket statement with no qualification.

Additionally it costs a lot of money!

If you are on a LAN then an Access backend can likely do the job as well.

Otherwise, what about MySQL, PostgreSQL, SQL Server Express, etc ... as options?

Why is it more expensive?
In no way can a backend do as good of a job. No security, no professional grade User management, and when you get above 20 users you are guaranteed to have data corruption.

I don't disagree about any other of the DBs you mentioned. Just not a file bases system, like Access ACE.

 

[CJ_London]

when you get above 20 users you are guaranteed to have data corruption.

In my experience, that is not the case. I have developed several apps over the years with more than 60 users with both jet and ace back ends, never had a problem with corruption.

Sql server (or express) was not an option because of IT politics - they wanted to own the whole thing, but did not have the resource or budget to develop it.

 

[NauticalGent]

I *thought* the decompile was for code?
Do you have code in the BE?

No, I just added that on since in was talking about my maintenance routine. I do a C&R on both and decompile/recompile the FE at the EoY

 

[Thales750]

In my experience, that is not the case. I have developed several apps over the years with more than 60 users with both jet and ace back ends, never had a problem with corruption.

Sql server (or express) was not an option because of IT politics - they wanted to own the whole thing, but did not have the resource or budget to develop it.

I've been on industrial installation where network issues caused problems. How does using SQL Server Express fall into the category of ownership? Does Microsoft retain ownership?

I went for many years using Access backends. but after biting the bullet and moving to SQL it just made perfect sense. It does have a learning curve, but it also makes the developer more versatile.
I suppose I should have said that the odds of data storage issues increases with lots of users and large numbers of records.

 

[The_Doc_Man]

How does using SQL Server Express fall into the category of ownership?

I ran into this sort of thing with the Navy. It isn't that Microsoft does or doesn't own the SQL Server software. It is that if it is a server, there is always some centralized IT group that says "that server is automatically our jurisdiction and responsibility." Then they take it over and it never works right again because they don't understand the problem being solved by the DB that was being built.

 

[Galaxiom]

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.

I have long argued that putting temp tables in the front end is the equivalent of writing a shopping list in margins of an encyclopedia.

Temp tables belong in a separate database which I call a "Side End". There are multiple ways to implement a side end. The easiest is to keep a pristine file with the required empty tables and copy it to the user's workspace (eg Appdata) when required, then simply delete it when no longer required.

 

[Galaxiom]

there is always some centralized IT group that says "that server is automatically our jurisdiction and responsibility."

I am fortunate to have always been part of that IT team in my workplace with full access to the network configuration and database administration. Eventually I settled into the role of the designated database administrator as we took on more IT staff.

The size of the business I'm in is now very close to the sweet spot for being a database developer and administrator. Small enough to manage and implement virtually it all myself which is great for extending my skills without jumping through hoops to get formal permission to do stuff. But with specialist staff to manage and support the third party applications and let me maintain focus on the database integrity and availability.

The three database application staff and I have the highest respect for each other. It makes a big difference. I've been in a situation where I was not respected by a developer colleague. I realised his disrespect was out of fear that I would recognise he was completely incompetent, something I did know, and fortunately management eventually worked out too without me having to drop him in it.

 

[The_Doc_Man]

Unfortunately, in 28 1/2 years with the U.S. Navy, I have seen more adversarial situations than non-adversarial.

 

[Galaxiom]

Unfortunately, in 28 1/2 years with the U.S. Navy, I have seen more adversarial situations than non-adversarial.

I think that is a fairly common situation. Many database administrators subscribe to hating Access. Some because they have seen troubled caused by Access dabblers who haven't got a clue what they are doing but many just to be part of that culture,

 

[Thales750]

I ran into this sort of thing with the Navy. It isn't that Microsoft does or doesn't own the SQL Server software. It is that if it is a server, there is always some centralized IT group that says "that server is automatically our jurisdiction and responsibility." Then they take it over and it never works right again because they don't understand the problem being solved by the DB that was being built.

When I was at Home Land (TSA Headquarters) we had the same issue. I had forgotten that. We were not allowed to have an SQL Server or even part of theirs. We weren't allowed to share one of our computers as an adhoc server. We were a group of undesirables, they put our backend on high traffic servers until I finally convinced them we need better bandwidth. That was my one and only true political battle, and I won. lol.
Thanks for reminding me of that Doc.