McGillivray, Don [IT]
Donald.A.McGillivray at sprint.com
Mon Dec 15 19:45:28 CST 2008
Hi, Charlotte Yes, the queries are created in the front end and executed against the back end - no queries in the back end. When I say "temporary back end", I mean that I have a "template" mdb file that contains a small collection of "never-been-used" tables that are used to capture and manipulate data prior to being added to the final system tables. In each processing cycle, I copy the template file to create a "new" collection. Then these tables (linked to the front end) get populated and purged several times. Since their function is transitory, and the populate/purge process would inevitably lead to bloating, I simply disconnect from them when I'm finished with a processing cycle and copy the template over the used collection, and repeat. So those tempoorary tables are never seen by the back end - it's all done in the front end - and the front end doesn't bloat from having to use local temp tables. BTW, the size of the front end is very stable - I'm seeing the bloat only in the back end. I would be interested to know more about the hidden Access overhead, and how some of my design/processing choices may be affecting the size of the back end. Thanks! Don -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Monday, December 15, 2008 3:59 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Back end bloat So the queries are being created in the front end and executed against the back end? That shouldn't cause back end bloat, but if you're running queries that actually execute in the back end, then yes it will bloat the database. I'm not sure what you mean about adding and removing data in a temporary backend. You still have to pass those changes to the backend and any changes against backend data will cause at least some bloat. The later version of Access create their own hidden queries for things and that's where a lot of bloat comes from as well. Stored queries are generally more efficient in Access because they can be optimized by the query engine while on the fly queries take a hit there. Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don [IT] Sent: Monday, December 15, 2008 9:26 AM To: Access Developers discussion and problem solving Subject: [AccessD] Back end bloat Hello Folks I don't know if this is an unexpected problem or not, but the back end DB for an application of mine has been bloating big time, and rather rapidly. It's Access 2003 running on a Win 2000 server, served to my users via Windows remote desktop. So far, I've just been dealing with it by compacting the backend now and then, but I'd like to understand what is going on so that I might fix the underlying problem. When compacted this thing is around 150 Mb, and when it grows to 1.5 Gb or so, I compact it again. Sometimes it bloats to 1.5 Gb within a day or two, and sometimes it takes 2 or 3 weeks. When this app needs to load and remove data, it's done with tables in a temporary back end that is killed and recreated from a template as needed. In the back end, all but two of the tables only get data added to them, and the two that sometimes have records removed do so at a rate of < 200 records a week for both tables combined. These tables have about 8 fields each. Don't know whether it makes a difference, but the app uses very few stored queries to perform its data additions. Most of of the records are added using SQL built on the fly and executed using db.Execute. Also, I've read elsewhere that failure to close recordsets and kill object variables can cause a DB to bloat, but it wasn't clear from the description whether that would be expected in a back end or the front end. (There's no code in the back end.) I've been very careful about killing object variables in my code, but less so about closing recordsets before killing them. Before I go looking for every instance of a recorset not being closed, can anybody help me to understand whether that may be part of the problem? Any leads toward the solution will be greatly apprreciated. Thanks! Don McGillivray This e-mail may contain Sprint Nextel Company proprietary information intended for the sole use of the recipient(s). Any use by others is prohibited. If you are not the intended recipient, please contact the sender and delete all copies of the message. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com This e-mail may contain Sprint Nextel Company proprietary information intended for the sole use of the recipient(s). Any use by others is prohibited. If you are not the intended recipient, please contact the sender and delete all copies of the message.