DB Bloat: Suspect Something To Do With I/O?

[(Pete Cresswell)]

I *know* I've been here before some years back, but can't for the life of me
recall the details.

My application is growing from about five megs to over a hundred megs in the
course of a 6-8 hour batch run that performs something on the order of 1.2
million computations.

The back end also grows (from about 50 megs to a half gig), but I would expect
this and plan to compress it after the process.

What's bothering me is the app's growth.

- All tables are links to the back end and/or a work DB.
- I've stepped through the I/O code (and there isn't much of it) and it looks
like every time I open a recordset, I .Close it and set it = Nothing before
exiting the routine.
- Ditto query objects. Whenever do a Set myQuery=, it's balanced out by a Set
myQuery=Nothing .

The only suspicious thing I found is one routine where I Dim a recordset, then
..Close it and set it = Nothing in the exit routine (with no error trapping - so
it doesn't throw an error bc nothing's open)...but never use it in the routine.
It's a "Dead" object so-to-speak.
In the situation I dimly remember from years past, the DB was picking up about
4k every time I did *something*.... but I can't remember what...

Anybody?
--
PeteCresswell

[David W. Fenton]

"(Pete Cresswell)" <[e-mail] d> wrote in
news:dn******** *************** *********@4ax.c om:

- I've stepped through the I/O code (and there isn't much of it)
and it looks like every time I open a recordset, I .Close it and
set it = Nothing before exiting the routine.
- Ditto query objects. Whenever do a Set myQuery=, it's balanced
out by a Set myQuery=Nothing .

Are you using QueryDefs? If so, I know that people say that if you
use temporary ones (no name), they don't bloat the db, but my
experience says that is not the case.

The other thing I might wonder about is if your recordsets are using
saved queries -- perhaps they are recompiling during the process?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[PeteCresswell]

On Wed, 02 Mar 2005 02:17:27 GMT, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote:

Are you using QueryDefs? If so, I know that people say that if you
use temporary ones (no name), they don't bloat the db, but my
experience says that is not the case.
I am using pre-defined queries, instantiating them, setting the parms,
and then opening a recordset based on same

The other thing I might wonder about is if your recordsets are using
saved queries -- perhaps they are recompiling during the process?

You went over my head on that one. As above, I'm basing recordsets
on pre-defined/saved/named queries. Sounds like maybe the app is
compiling the query on-the-fly?

[David W. Fenton]

PeteCresswell <[e-mail] d> wrote in
news:sc******** *************** *********@4ax.c om:

On Wed, 02 Mar 2005 02:17:27 GMT, "David W. Fenton"
<dX********@bw ay.net.invalid> wrote:

Are you using QueryDefs? If so, I know that people say that if you
use temporary ones (no name), they don't bloat the db, but my
experience says that is not the case.

I am using pre-defined queries, instantiating them, setting the
parms, and then opening a recordset based on same

Then they are temporary querydefs that you're opening, since that's
the only way to assign values to parameters. In my experience, that
leads to bloat.

The other thing I might wonder about is if your recordsets are
using saved queries -- perhaps they are recompiling during the
process?

You went over my head on that one. As above, I'm basing
recordsets on pre-defined/saved/named queries. Sounds like maybe
the app is compiling the query on-the-fly?

I don't know when a saved query gets recompiled. I thought it was
only when the query was saved the first time after a compact.

I'm more inclined to suspect the querydef parameter assignment.

I don't use parameters in queries that are opened only in code.
Indeed, I generally don't use saved queries for opening recordsets
at all. What do you think you're getting by using parameter queries
that you wouldn't get with opening recordsets from SQL with a WHERE
clause constructed on-the-fly?

I have a vague feeling that parameters can speed things up (as the
data type of the input is known), but I can't come up with anything
that supports that.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[(Pete Cresswell)]

Per David W. Fenton:

What do you think you're getting by using parameter queries
that you wouldn't get with opening recordsets from SQL with a WHERE
clause constructed on-the-fly?

Mainly indulging a longstanding habit. I always preferred queries as a place
to stash SQL because I can put a label on it and it shows up in a list.
Embedded SQL, in my experience, lessens the maintainability of an app.

But I guess it's time to try a little change here. I wish I'd read your post
about four hours ago...before I kicked off that monster batch job to run
overnite.

Tomorrow, I'll change those few queries to inline SQL, rerun, and see if the
problem goes away.

Meanwhile, mabye I'll try a post in one of the MS Public NGS to see if I can
troll up somebody who knows the mechanism of the bloating effect.

Thanks.
--
PeteCresswell

[David W. Fenton]

"(Pete Cresswell)" <[e-mail] d> wrote in
news:dd******** *************** *********@4ax.c om:

Per David W. Fenton:

What do you think you're getting by using parameter queries
that you wouldn't get with opening recordsets from SQL with a
WHERE clause constructed on-the-fly?

Mainly indulging a longstanding habit. I always preferred
queries as a place to stash SQL because I can put a label on it
and it shows up in a list. Embedded SQL, in my experience, lessens
the maintainability of an app.

But I guess it's time to try a little change here. I wish I'd
read your post about four hours ago...before I kicked off that
monster batch job to run overnite.

Tomorrow, I'll change those few queries to inline SQL, rerun, and
see if the problem goes away.

Well, I think the main issue is the parameters, not that it's saved
SQL.

As to bloat, most people here have denied that opening non-saved
QueryDefs causes bloat, but I've seen it enough to have developed
the habit of avoiding them.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[(Pete Cresswell)]

Per David W. Fenton:

As to bloat, most people here have denied that opening non-saved
QueryDefs causes bloat, but I've seen it enough to have developed
the habit of avoiding them.

I just stumbled across
http://support.microsoft.com/default...;EN-US;q289562 which suggests
that a QueryDef needs to have the .Close method applied to it to prevent growth.

I've always closed RecordSets, but only set QueryDefs = Nothing.

Maybe this is the cause.... Got a little test harness running right now that
sets the QueryDef = Nothing but does not .Close it...
--
PeteCresswell

[Tony Toews]

"(Pete Cresswell)" <[e-mail] d> wrote:

My application is growing from about five megs to over a hundred megs in the
course of a 6-8 hour batch run that performs something on the order of 1.2
million computations.

Also see the Bloating Front End (FE) Microsoft Access MDB/MDEs Tips page at my
website at http://www.granite.ab.ca/access/bloatfe.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[(Pete Cresswell)]

Per (Pete Cresswell):

Maybe this is the cause.... Got a little test harness running right now that
sets the QueryDef = Nothing but does not .Close it...

Nope. Got through a quater million reps of setting a local querydef to the
saved querydef, feeding it 3 parms, opening an RS based on it, closing the rs,
and just setting the query = Nothing. No increase in size at all.

Now I'm trying the same loop, but with a .Edit.....Updat e in it.
--
PeteCresswell

[(Pete Cresswell)]

Per Tony Toews:

Also see the Bloating Front End (FE) Microsoft Access MDB/MDEs Tips page at my
website at http://www.granite.ab.ca/access/bloatfe.htm

Thinks....been there courtesy of Google earlier today.

Seems like I'm 100% in compliance so far.

My leading theory of the moment is that I'm lying about doing a .Close all the
time. Gotta get back to the client's site tonite or tomorrow and step through
the code very carefully....ma ybe I missed something the first time through -
under the influence of the "Hong Kong Lite" virus-or-whateverthell-it-is that I
managed to pick up Saturday nite...
--
PeteCresswell