Another database bloat question.

**PC Datasheet** · Nov 12 '05, 05:40 PM

Re: Another database bloat question.

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
resource@pcdata sheet.com

https://www.pcdatasheet.com

"Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in message
news:brb7o7$1ft nb$1@ID-82595.news.uni-berlin.de...[color=blue]
> 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
> dlesandrini@hot mail.com
> http://amazecreations.com
>
>
>[/color]

**Danny J. Lesandrini** · Nov 12 '05, 05:40 PM

Re: Another database bloat question.

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.OpenR ecordset instead of dbs.OpenRecords et.
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
dlesandrini@hot mail.com

404 Not Found

http://amazecreations.com/datafast

"PC Datasheet" <spam@nospam.co m> wrote in message news:n8kCb.357$ X97.186@newsrea d2.news.atl.ear thlink.net...[color=blue]
> 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
> resource@pcdata sheet.com
> www.pcdatasheet.com
>
>
> "Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in message
> news:brb7o7$1ft nb$1@ID-82595.news.uni-berlin.de...[color=green]
> > 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
> > dlesandrini@hot mail.com
> > http://amazecreations.com
> >
> >
> >[/color]
>
>[/color]

**PC Datasheet** · Nov 12 '05, 05:40 PM

Re: Another database bloat question.

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" <dlesandrini@ho tmail.com> wrote in message
news:brckfv$1pf 63$1@ID-82595.news.uni-berlin.de...[color=blue]
> 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.OpenR ecordset instead of dbs.OpenRecords et.
> This doesn't work when iterating through a collection of objects, like[/color]
TableDefs,[color=blue]
> 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
> dlesandrini@hot mail.com
> http://amazecreations.com/datafast
>
>
> "PC Datasheet" <spam@nospam.co m> wrote in message[/color]
news:n8kCb.357$ X97.186@newsrea d2.news.atl.ear thlink.net...[color=blue][color=green]
> > 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,[/color][/color]
the[color=blue][color=green]
> > 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
> > resource@pcdata sheet.com
> > www.pcdatasheet.com
> >
> >
> > "Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in message
> > news:brb7o7$1ft nb$1@ID-82595.news.uni-berlin.de...[color=darkred]
> > > 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[/color][/color][/color]
file?[color=blue][color=green][color=darkred]
> > > --
> > >
> > > Danny J. Lesandrini
> > > dlesandrini@hot mail.com
> > > http://amazecreations.com
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

**Danny J. Lesandrini** · Nov 12 '05, 05:40 PM

Re: Another database bloat question.

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" <spam@nospam.co m> wrote in message news:8slCb.481$ 5B.415@newsread 1.news.atl.eart hlink.net...[color=blue]
> 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
>[/color]

**Albert D. Kallal** · Nov 12 '05, 05:41 PM

Re: Another database bloat question.

"Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in message
news:brckfv$1pf 63$1@ID-82595.news.uni-berlin.de...[color=blue]
> 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[/color]
to a[color=blue]
> table and we can count them.) Invalid orders are cancelled, not deleted.[/color]

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*Reocr dId 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" "NetWorkLog on" "ComputerNa me"
"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
NoooSPAmkallal@ msn.com

http://www.attcanada.net/~kallal.msn

**PC Datasheet** · Nov 12 '05, 05:43 PM

Re: Another database bloat question.

Danny,

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

Steve
PC Datasheet

"Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in message
news:brcqej$1v2 l4$1@ID-82595.news.uni-berlin.de...[color=blue]
> 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" <spam@nospam.co m> wrote in message[/color]
news:8slCb.481$ 5B.415@newsread 1.news.atl.eart hlink.net...[color=blue][color=green]
> > Danny,
> >
> > I don't think the DAO recordset stuff is your problem. When objects are[/color][/color]
dimmed,[color=blue][color=green]
> > you reserve memory to hold the object. If you don't close the objects and[/color][/color]
set[color=blue][color=green]
> > them to nothing, the memory remains reserved. So all you have done is[/color][/color]
reduced[color=blue][color=green]
> > memory resources available for other tasks. Failing to close objects and[/color][/color]
failing[color=blue][color=green]
> > 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
> >[/color]
>
>[/color]

**Danny J. Lesandrini** · Nov 12 '05, 05:43 PM

Re: Another database bloat question.

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
dlesandrini@hot mail.com

404 Not Found

http://amazecreations.com/datafast

"PC Datasheet" <spam@nospam.co m> wrote in message news:t1GCb.78$s W5.40@newsread2 .news.atl.earth link.net...[color=blue]
> Danny,
>
> A Date/Time field uses 8 Bytes but so does Currency and Double.
>
> Steve
> PC Datasheet
>
>
> "Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in message
> news:brcqej$1v2 l4$1@ID-82595.news.uni-berlin.de...[color=green]
> > 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" <spam@nospam.co m> wrote in message[/color]
> news:8slCb.481$ 5B.415@newsread 1.news.atl.eart hlink.net...[color=green][color=darkred]
> > > Danny,
> > >
> > > I don't think the DAO recordset stuff is your problem. When objects are[/color][/color]
> dimmed,[color=green][color=darkred]
> > > you reserve memory to hold the object. If you don't close the objects and[/color][/color]
> set[color=green][color=darkred]
> > > them to nothing, the memory remains reserved. So all you have done is[/color][/color]
> reduced[color=green][color=darkred]
> > > memory resources available for other tasks. Failing to close objects and[/color][/color]
> failing[color=green][color=darkred]
> > > 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
> > >[/color]
> >
> >[/color]
>
>[/color]

Another database bloat question.

Another database bloat question.

Comment

Comment

Comment

Comment

Comment

Comment

Comment