469,266 Members | 1,679 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

Another database bloat question.

I know this has been discussed before, as I've seen the Google posts,
but they all leave me with an unanswered question:

Does my DAO code executed in the front end cause the back end to bloat?
(May also substitute UPDATE and/or DELETE queries for DAO code.)

I was just brought on to a project with Access 97 where the all data is
kept on the server. It doubles in size each day, from 80 mb to 160 mb.
The data file contains only tables and some queries which are used by a
VB app that retrieves data for use in a Tree-View control.

ALL users are on Windows XP but I can't say for sure whether or not they
have the latest JET service pack ... if that makes any difference.

I've just never seen this kind of bloat for a data file before, but I know
that the client app has lots of places where recordsets are not closed
properly nor set to nothing. Could the client be bloating the server file?
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com

Nov 12 '05 #1
7 2443
Danny,

Some possible causes of bloat I see in your description --
1. If you have FE code deleting data and then refilling tables in the BE, the
BE will bloat
2. Are the queries in the BE deleting data and refilling tables in the BE
3. Are users opening the BE directly, especially more than one at a time
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br************@ID-82595.news.uni-berlin.de...
I know this has been discussed before, as I've seen the Google posts,
but they all leave me with an unanswered question:

Does my DAO code executed in the front end cause the back end to bloat?
(May also substitute UPDATE and/or DELETE queries for DAO code.)

I was just brought on to a project with Access 97 where the all data is
kept on the server. It doubles in size each day, from 80 mb to 160 mb.
The data file contains only tables and some queries which are used by a
VB app that retrieves data for use in a Tree-View control.

ALL users are on Windows XP but I can't say for sure whether or not they
have the latest JET service pack ... if that makes any difference.

I've just never seen this kind of bloat for a data file before, but I know
that the client app has lots of places where recordsets are not closed
properly nor set to nothing. Could the client be bloating the server file?
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com

Nov 12 '05 #2
PC:

Thanks for the comments, but I don't think the app(s) are guilty of any of
these transgressions.

As a rule, nothing can be deleted from the client interface, except order
line items, and that happens rarely. (In fact, delete actions are logged to a
table and we can count them.) Invalid orders are cancelled, not deleted.

There are no temp tables. In fact, there are no tables at all in the client.
Some static data are kept in an additional local mdb file of local tables.

All queries in the back end are SELECT queries, used by the VB app I
mentioned. The queries are just there to simplify data access. The sql
statements could be embedded in the VB app, but calling prepared queries
was easier for the developer to program and design. They might even enhance
performance, who knows.

All users access the data from their own local client mdb file with linked
tables. No one (besides developers) ever has reason to open the back end.
Some power users have Excel spreadsheets linked to the data, but if that
sort of access happens 3 times a day, I'd be surprised. We only have a
total of 20 office personel who use the data.

The one thing I know is going on is the DAO recordset stuff. The developer
who created this thing was of the opinion that when the proc ended, all
objects would simply go out of scope, so he pratically NEVER closes or
sets to nothing Recordset, Querydef, Tabledef or Database objects.

Also, he seldom used a database object. As often as he could get away
with it, he'd use CurrentDB.OpenRecordset instead of dbs.OpenRecordset.
This doesn't work when iterating through a collection of objects, like TableDefs,
but he used it everywhere else.

There are no images in the database.

There are 5 memo fields in the OrderDetails table (the only one that allows
records to be deleted), and 5 more in the Orders and Customers tables.
I didn't count them, but I was told there are 17 date fields and the bloat
seemed to worsen after adding additional date fields.

Not sure if any of this helps you. I'm of the opinion we should get rid of
some of these memo fields, but I can't say for sure that would help. What
do you think of removing some date fields?
--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"PC Datasheet" <sp**@nospam.com> wrote in message news:n8***************@newsread2.news.atl.earthlin k.net...
Danny,

Some possible causes of bloat I see in your description --
1. If you have FE code deleting data and then refilling tables in the BE, the
BE will bloat
2. Are the queries in the BE deleting data and refilling tables in the BE
3. Are users opening the BE directly, especially more than one at a time
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br************@ID-82595.news.uni-berlin.de...
I know this has been discussed before, as I've seen the Google posts,
but they all leave me with an unanswered question:

Does my DAO code executed in the front end cause the back end to bloat?
(May also substitute UPDATE and/or DELETE queries for DAO code.)

I was just brought on to a project with Access 97 where the all data is
kept on the server. It doubles in size each day, from 80 mb to 160 mb.
The data file contains only tables and some queries which are used by a
VB app that retrieves data for use in a Tree-View control.

ALL users are on Windows XP but I can't say for sure whether or not they
have the latest JET service pack ... if that makes any difference.

I've just never seen this kind of bloat for a data file before, but I know
that the client app has lots of places where recordsets are not closed
properly nor set to nothing. Could the client be bloating the server file?
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com


Nov 12 '05 #3
Danny,

I don't think the DAO recordset stuff is your problem. When objects are dimmed,
you reserve memory to hold the object. If you don't close the objects and set
them to nothing, the memory remains reserved. So all you have done is reduced
memory resources available for other tasks. Failing to close objects and failing
to set them to nothing is not directly connected to the database file.

<<What do you think of removing some date fields?>>
Did you mean memo fields? I'm with you, not so sure this would be of any
benefit.

Steve
PC Datasheet

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br************@ID-82595.news.uni-berlin.de...
PC:

Thanks for the comments, but I don't think the app(s) are guilty of any of
these transgressions.

As a rule, nothing can be deleted from the client interface, except order
line items, and that happens rarely. (In fact, delete actions are logged to a
table and we can count them.) Invalid orders are cancelled, not deleted.

There are no temp tables. In fact, there are no tables at all in the client.
Some static data are kept in an additional local mdb file of local tables.

All queries in the back end are SELECT queries, used by the VB app I
mentioned. The queries are just there to simplify data access. The sql
statements could be embedded in the VB app, but calling prepared queries
was easier for the developer to program and design. They might even enhance
performance, who knows.

All users access the data from their own local client mdb file with linked
tables. No one (besides developers) ever has reason to open the back end.
Some power users have Excel spreadsheets linked to the data, but if that
sort of access happens 3 times a day, I'd be surprised. We only have a
total of 20 office personel who use the data.

The one thing I know is going on is the DAO recordset stuff. The developer
who created this thing was of the opinion that when the proc ended, all
objects would simply go out of scope, so he pratically NEVER closes or
sets to nothing Recordset, Querydef, Tabledef or Database objects.

Also, he seldom used a database object. As often as he could get away
with it, he'd use CurrentDB.OpenRecordset instead of dbs.OpenRecordset.
This doesn't work when iterating through a collection of objects, like TableDefs, but he used it everywhere else.

There are no images in the database.

There are 5 memo fields in the OrderDetails table (the only one that allows
records to be deleted), and 5 more in the Orders and Customers tables.
I didn't count them, but I was told there are 17 date fields and the bloat
seemed to worsen after adding additional date fields.

Not sure if any of this helps you. I'm of the opinion we should get rid of
some of these memo fields, but I can't say for sure that would help. What
do you think of removing some date fields?
--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"PC Datasheet" <sp**@nospam.com> wrote in message

news:n8***************@newsread2.news.atl.earthlin k.net...
Danny,

Some possible causes of bloat I see in your description --
1. If you have FE code deleting data and then refilling tables in the BE, the BE will bloat
2. Are the queries in the BE deleting data and refilling tables in the BE
3. Are users opening the BE directly, especially more than one at a time
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br************@ID-82595.news.uni-berlin.de...
I know this has been discussed before, as I've seen the Google posts,
but they all leave me with an unanswered question:

Does my DAO code executed in the front end cause the back end to bloat?
(May also substitute UPDATE and/or DELETE queries for DAO code.)

I was just brought on to a project with Access 97 where the all data is
kept on the server. It doubles in size each day, from 80 mb to 160 mb.
The data file contains only tables and some queries which are used by a
VB app that retrieves data for use in a Tree-View control.

ALL users are on Windows XP but I can't say for sure whether or not they
have the latest JET service pack ... if that makes any difference.

I've just never seen this kind of bloat for a data file before, but I know
that the client app has lots of places where recordsets are not closed
properly nor set to nothing. Could the client be bloating the server file? --

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com



Nov 12 '05 #4
Well, I'd like to remove the memo fields, or at least convert them to Text
fields, but I also have a quesiton about the "weight" of date fields. Someone
told me they carry a large overhead, but it doesn't seem that way to me.

--
Danny J. Lesandrini
"PC Datasheet" <sp**@nospam.com> wrote in message news:8s**************@newsread1.news.atl.earthlink .net...
Danny,

I don't think the DAO recordset stuff is your problem. When objects are dimmed,
you reserve memory to hold the object. If you don't close the objects and set
them to nothing, the memory remains reserved. So all you have done is reduced
memory resources available for other tasks. Failing to close objects and failing
to set them to nothing is not directly connected to the database file.

<<What do you think of removing some date fields?>>
Did you mean memo fields? I'm with you, not so sure this would be of any
benefit.

Steve
PC Datasheet

Nov 12 '05 #5
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br************@ID-82595.news.uni-berlin.de...
PC:

As a rule, nothing can be deleted from the client interface, except order
line items, and that happens rarely. (In fact, delete actions are logged to a table and we can count them.) Invalid orders are cancelled, not deleted.


It is still very possible that the developer's code is creating, and
deleting records. You don't have to use a temp table, but can still do a
lot of deleting.
Further, it is MOST CERTAINLY a know and well document issue that failure to
close recordsets can cause bloat. In addition, if using JET 4.0, the service
updates to JET fixes several well known bloating problems also. So, no, you
can't write out the fact that the code may be un-necessarily writing out,
and copying records. Further, as mentioned, the issue of NOT closing
recordsets is also well documents. And, also, it well documents that several
bloat issues are fixed by the updates to JET.

There is no doubt that as a developer, we MUST TAKE VERY large efforts to
avoid un-necessary deleting and creating of records in a table. However,
since for the last 20 years the major database systems (dbase/Foxpro, and
now JET) did not recycle deleted record space, then any developer would make
HUGE EFFORTS to avoid unnecessary deleting, and creating of records.

For example, I wrote my own custom record locking routines for a ms-access
application. Normally, you have a table called tblLocks. You can simply add
a new record. All I did was make a keyId field (that is the
tablename*ReocrdId as the key). Since the field is set to not allow
duplicates, then I two users can't add the same record with the same
table/key name. This is gives me a nice locking routine (else a error will
occur).

Of course, the problem with the above design is that this design requires
one to delete records, and add records. Each time I lock a record, I would
have to "add" a new record to this table. And, then when I un-lock, I would
have to delete the record. Again, as developer I should be REALLY be scolded
here. Bad Bad Albert!.

So, what did I do?

Well, very simply I created a table with the above fields, but instead of
deleting the records when done, I simply update that table/id field to
blank. In other words, I don't EVER delete from that locking table. When
need to make a lock, I FIRST SEARCH for the current user name, and
workstation name, AND THE LOCKID = null. That way, if any locking record
exists, but not used, then I grab it, and use that record. If the user have
several records locked, then yes...I might wind up adding a new record. So,
my table looks like:

"Lockitem" "NetWorkLogon" "ComputerName"
"tour*423" "Groups" "TAMMYPC"
"tour*393" "Groups" "TAMMYPC"
"BOOK*5037" "reception" "RECEPTION"
"tour*452" "Groups" "TAMMYPC"
"" "Groups" "TAMMYPC"
"" "Staff" "RONNIE"
"" "Staff" "RONNIE"
"" "Groups" "TAMMYPC"
So, what happens if TammpyPc needs to lock a reocrd, I FIRST do a query for
a reocrd with her workstation name, and her computer name. If find a record
with LockItem = null, then I USE THAT RECORD first. Of course the above
design allow a user to have more then one record locked, and from different
tables.

In other words, by JUST slightly modify my design, then above now DOES NOT
CREATE ANY database bloat. You can imagine with 5, or 6 users editing, and
working on 100's of records a day, the above table would cause a
considerable amount of bloat as my code locks, and un-locks records.
However, since I do NOT DELETE the record when done to un-lock, then I don't
have to try and recover the space!. I simply set the lockitem field to null.
Next time I need a locking record, I query the above table. Since I re-cycle
the records, then virtually no bloat occurs with this design.

I mean, we all know that deleting, and re-creating records means that space
will NOT be recovered. Since we all know this, then modifying our designs to
take this into account is just common sense.

So, I am not really convinced that un-necessary records are being deleting,a
and recreated in this application. If this is not occurring, then the
failure to close the reocrdets, or not having applied the JET service packs
is to blame.
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
No************@msn.com
http://www.attcanada.net/~kallal.msn

Nov 12 '05 #6
Danny,

A Date/Time field uses 8 Bytes but so does Currency and Double.

Steve
PC Datasheet
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br************@ID-82595.news.uni-berlin.de...
Well, I'd like to remove the memo fields, or at least convert them to Text
fields, but I also have a quesiton about the "weight" of date fields. Someone
told me they carry a large overhead, but it doesn't seem that way to me.

--
Danny J. Lesandrini
"PC Datasheet" <sp**@nospam.com> wrote in message

news:8s**************@newsread1.news.atl.earthlink .net...
Danny,

I don't think the DAO recordset stuff is your problem. When objects are dimmed, you reserve memory to hold the object. If you don't close the objects and set them to nothing, the memory remains reserved. So all you have done is reduced memory resources available for other tasks. Failing to close objects and failing to set them to nothing is not directly connected to the database file.

<<What do you think of removing some date fields?>>
Did you mean memo fields? I'm with you, not so sure this would be of any
benefit.

Steve
PC Datasheet


Nov 12 '05 #7
Yea ... I know. Doesn't seem like that would be it, but what if adding
a couple pages pushed the record size past the 2k page limit ... by just
a couple bytes? Would that extra wasted page give the impression that
you're using more space than you really need ... and would compacting
recover it?
--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"PC Datasheet" <sp**@nospam.com> wrote in message news:t1*************@newsread2.news.atl.earthlink. net...
Danny,

A Date/Time field uses 8 Bytes but so does Currency and Double.

Steve
PC Datasheet
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br************@ID-82595.news.uni-berlin.de...
Well, I'd like to remove the memo fields, or at least convert them to Text
fields, but I also have a quesiton about the "weight" of date fields. Someone
told me they carry a large overhead, but it doesn't seem that way to me.

--
Danny J. Lesandrini
"PC Datasheet" <sp**@nospam.com> wrote in message

news:8s**************@newsread1.news.atl.earthlink .net...
Danny,

I don't think the DAO recordset stuff is your problem. When objects are dimmed, you reserve memory to hold the object. If you don't close the objects and set them to nothing, the memory remains reserved. So all you have done is reduced memory resources available for other tasks. Failing to close objects and failing to set them to nothing is not directly connected to the database file.

<<What do you think of removing some date fields?>>
Did you mean memo fields? I'm with you, not so sure this would be of any
benefit.

Steve
PC Datasheet



Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Salvador I. Ducros | last post: by
2 posts views Thread by Rosy Moss | last post: by
10 posts views Thread by john_liu | last post: by
4 posts views Thread by Cindy | last post: by
6 posts views Thread by Randy Shore | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.