By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,743 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Compact on close - bad idea?

P: n/a
Is it?
Nov 12 '05 #1
Share this Question
Share on Google+
35 Replies


P: n/a
Mike MacSween wrote:
Is it?


The user might not appreciate it. Then they might take a shortcut and close
the Access window rather than click on your command button. I'm sure there
are technical considerations but I'm don't know what they are.
Nov 12 '05 #2

P: n/a
Most apps I work on are split front/back end, so Compact on close is not
much use. I usually provide a separate Access MDB that provides
maintenance services such as compact-back-end.

On Wed, 17 Sep 2003 21:39:00 +0100, "Mike MacSween"
<mi******************@btinternet.com> wrote:
Is it?


Nov 12 '05 #3

P: n/a
It probably won't HURT anything. But why not compact on a regular schedule
(e.g., compact if the mdb hasn't been compacted in the past n days)? There's
code available to do this in various ways, I think (if not, I can post my
own kludgey code). Why compact if a user opens the database, looks at a
record, and closes it again? The performance loss would be greater than any
benefit from the compacting. You could base the decision to compact on the
number of days since last compacting, the number of uses (have a table with
one record which incremements by one when the database is opened), or
whatever else you want.

The only situation I could see it is a machine that is up all the time,
running a database continuously, but in that situation I'd look for a more
robust solution.

HTH,

Bruce Rusk
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. ..
Is it?

Nov 12 '05 #4

P: n/a
Er, it's an application option. I don't need to set up a command button

"Deano" <ma************@hotmail.com> wrote in message
news:33*******************@wards.force9.net...
Mike MacSween wrote:
Is it?
The user might not appreciate it. Then they might take a shortcut and

close the Access window rather than click on your command button. I'm sure there are technical considerations but I'm don't know what they are.

Nov 12 '05 #5

P: n/a
Mine too. But there are a couple of temp tables in the front end.

How do you schedule your maintenance services then?

Mike

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:c7********************************@4ax.com...
Most apps I work on are split front/back end, so Compact on close is not
much use. I usually provide a separate Access MDB that provides
maintenance services such as compact-back-end.

On Wed, 17 Sep 2003 21:39:00 +0100, "Mike MacSween"
<mi******************@btinternet.com> wrote:
Is it?

Nov 12 '05 #6

P: n/a
It does it at the moment. I don't see any performance impact. I was more
worried about risks of corruption.

Mike

"Bruce Rusk" <ho**********@spamless.ucla.edu> wrote in message
news:bk**********@gladiola.noc.ucla.edu...
It probably won't HURT anything. But why not compact on a regular schedule
(e.g., compact if the mdb hasn't been compacted in the past n days)? There's code available to do this in various ways, I think (if not, I can post my
own kludgey code). Why compact if a user opens the database, looks at a
record, and closes it again? The performance loss would be greater than any benefit from the compacting. You could base the decision to compact on the
number of days since last compacting, the number of uses (have a table with one record which incremements by one when the database is opened), or
whatever else you want.

The only situation I could see it is a machine that is up all the time,
running a database continuously, but in that situation I'd look for a more
robust solution.

HTH,

Bruce Rusk
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. ..
Is it?


Nov 12 '05 #7

P: n/a
"Mike MacSween" <mi******************@btinternet.com> wrote:
Mine too. But there are a couple of temp tables in the front end.


Put them in a seperate MDB. See the Temp Tables page at my website.

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
Nov 12 '05 #8

P: n/a
I try to avoid putting temp tables -in- the front-end. I'll generally try
to create a separate temporary database file on the front-end instead.

On Thu, 18 Sep 2003 03:53:54 +0100, "Mike MacSween"
<mi******************@btinternet.com> wrote:
Mine too. But there are a couple of temp tables in the front end.

How do you schedule your maintenance services then?

Mike

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:c7********************************@4ax.com.. .
Most apps I work on are split front/back end, so Compact on close is not
much use. I usually provide a separate Access MDB that provides
maintenance services such as compact-back-end.

On Wed, 17 Sep 2003 21:39:00 +0100, "Mike MacSween"
<mi******************@btinternet.com> wrote:
>Is it?
>


Nov 12 '05 #9

P: n/a
Mike MacSween wrote:
Er, it's an application option. I don't need to set up a command
button

"Deano" <ma************@hotmail.com> wrote in message
news:33*******************@wards.force9.net...
Mike MacSween wrote:
Is it?


The user might not appreciate it. Then they might take a shortcut
and close the Access window rather than click on your command
button. I'm sure there are technical considerations but I'm don't
know what they are.


Oh, I forgot about that! Well my point was that if it slows down the
experience of using the app then it might hack some people off, even if they
know what it's doing.
Nov 12 '05 #10

P: n/a
I am running a normal split front end / back end system with 15-20
users. The first time anyone logs in each day, I compact the back end
( and also back it up ) before connecting to it. I add a record to an
[Action] Table so no one else does it.
Recently I had the network guy write a script that brings up one
PC at 5:00 AM so that the compacting is all taken care of when people
come to work.

Write me direct if you need more details

Hank Reed
Nov 12 '05 #11

P: n/a
mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>:
Is there a risk of corruption with compact on close?


Not so far as I know, since the front end is disposable, anyway.

Of course, that's the same reason why there's not much need for
compacting a front end in the first place. Indeed, I design my
front ends on the assumption that they will never ever be
compacted.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #12

P: n/a
Just so I'm clear Michael, are you saying that compact on close, or perhaps
periodic compact, will often be a good idea?

Cheers, Mike MacSween

"Michael (michka) Kaplan [MS]" <mi*****@online.microsoft.com> wrote in
message news:3f********@news.microsoft.com...
This is not necessarily the best course of action, though, for at least two reasons:

1) Compact resets queries so they can be recompiled the next time they are
run. Since data changes over times the QPE (query processing engine) may
have a different optimization plan doe ideal data retreival, but if you
never make that recompile happen, you will never recieve that benefit.

2) If you run even moderately complex reports, Access creates a lot of temp queries/SVTs that will bloat up the database a bit. Only a compact can
shrink the size back down. Since there is no way other than by
experimentation to determine when this is the case, it is often best to
assume it may happen and prepare for it.
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.

"David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.86...
mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>:
Is there a risk of corruption with compact on close?


Not so far as I know, since the front end is disposable, anyway.

Of course, that's the same reason why there's not much need for
compacting a front end in the first place. Indeed, I design my
front ends on the assumption that they will never ever be
compacted.

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


Nov 12 '05 #13

P: n/a
I think periodic compact would make sense. You could easily do this by
altering the value of

CurrentDb.Properties("Auto Compact").Value

every 10 times or something.

--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. ..
Just so I'm clear Michael, are you saying that compact on close, or perhaps periodic compact, will often be a good idea?

Cheers, Mike MacSween

"Michael (michka) Kaplan [MS]" <mi*****@online.microsoft.com> wrote in
message news:3f********@news.microsoft.com...
This is not necessarily the best course of action, though, for at least

two
reasons:

1) Compact resets queries so they can be recompiled the next time they are run. Since data changes over times the QPE (query processing engine) may
have a different optimization plan doe ideal data retreival, but if you
never make that recompile happen, you will never recieve that benefit.

2) If you run even moderately complex reports, Access creates a lot of

temp
queries/SVTs that will bloat up the database a bit. Only a compact can
shrink the size back down. Since there is no way other than by
experimentation to determine when this is the case, it is often best to
assume it may happen and prepare for it.
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.

"David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.86...
mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>:

>Is there a risk of corruption with compact on close?

Not so far as I know, since the front end is disposable, anyway.

Of course, that's the same reason why there's not much need for
compacting a front end in the first place. Indeed, I design my
front ends on the assumption that they will never ever be
compacted.

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



Nov 12 '05 #14

P: n/a
Thanks for the info. I guess there is reason to compact the front-end
after all.

On Fri, 19 Sep 2003 05:28:00 -0700, "Michael \(michka\) Kaplan [MS]"
<mi*****@online.microsoft.com> wrote:
This is not necessarily the best course of action, though, for at least two
reasons:

1) Compact resets queries so they can be recompiled the next time they are
run. Since data changes over times the QPE (query processing engine) may
have a different optimization plan doe ideal data retreival, but if you
never make that recompile happen, you will never recieve that benefit.

2) If you run even moderately complex reports, Access creates a lot of temp
queries/SVTs that will bloat up the database a bit. Only a compact can
shrink the size back down. Since there is no way other than by
experimentation to determine when this is the case, it is often best to
assume it may happen and prepare for it.


Nov 12 '05 #15

P: n/a
On Fri, 19 Sep 2003 17:31:29 GMT in comp.databases.ms-access,
dX********@bway.net (David W. Fenton) wrote:
Serious question: who cares if the front end gets big?


Believe it or not, there are people that do. There are still people in
this day and age that have disks that fill up, workstations and
servers.

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #16

P: n/a
"David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.74...
Serious question: who cares if the front end gets big?
Sure, given the usual capacity of hard disks these days, then size probably
doesn't matter (at least that's what I've always been told, maybe they're
just being kind!). But I still like to think I had some control over file
size. We all know it isn't often possible, but I'd like to think that I
could at some point 'let go' of an Access database and not have to return to
it again.

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

Nov 12 '05 #17

P: n/a
bouncer@localhost (Trevor Best) wrote in
<hr********************************@4ax.com>:
On Fri, 19 Sep 2003 17:31:29 GMT in comp.databases.ms-access,
dX********@bway.net (David W. Fenton) wrote:
Serious question: who cares if the front end gets big?


Believe it or not, there are people that do. There are still
people in this day and age that have disks that fill up,
workstations and servers.


I've never had a front end that was properly designed that grew
more than a few MBs beyond its original size. The largest was an
A2K front end under the initial release of A2K that had a bunch of
signatures embedded in reports and in a table stored in the front
end (it was not my design -- I was hired to make it email-friendly,
not to re-architect the design). It would begin life at 12MBs and
grow to 18-20MBs, where it stayed forever and ever.

My current A2K apps start out about 2.5-5MBs and grow a couple of
MBs under daily use and then stop growing.

I don't see this as an issue worth worrying about.

If a front end is growing more than that amount, then there must be
something wrong with the way the front end is designed, and *that*
should be changed, instead of requiring a compact.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #18

P: n/a
no****@nospam.nospam (Steve Jorgensen) wrote in
<75********************************@4ax.com>:
On Fri, 19 Sep 2003 05:28:00 -0700, "Michael \(michka\) Kaplan
[MS]"
<mi*****@online.microsoft.com> wrote:
This is not necessarily the best course of action, though, for at
least two reasons:

1) Compact resets queries so they can be recompiled the next time
they are run. Since data changes over times the QPE (query
processing engine) may have a different optimization plan doe
ideal data retreival, but if you never make that recompile
happen, you will never recieve that benefit.

2) If you run even moderately complex reports, Access creates a
lot of temp queries/SVTs that will bloat up the database a bit.
Only a compact can shrink the size back down. Since there is no
way other than by experimentation to determine when this is the
case, it is often best to assume it may happen and prepare for
it.


Thanks for the info. I guess there is reason to compact the
front-end after all.


Have you seen my response to Michael? Do you not think that I have
some points there?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #19

P: n/a
Michael (michka) Kaplan [MS] wrote:
I think periodic compact would make sense. You could easily do this by
altering the value of

CurrentDb.Properties("Auto Compact").Value

every 10 times or something.

Does this work in Access 2000?
Nov 12 '05 #20

P: n/a
"David W. Fenton" <dX********@bway.net> wrote...
This only matters if the data changes in ways that no longer match
the original optimization during the lifetime of the front end.
This will show up as degraded performance. If a client says their
database is running slowly, we compact the back end (assuming they
don't already have a regulard compact routine in place). If it
still runs slowly, then we compact the front end, or replace it
with a fresh copy (usually the latter, since that process is more
likely to be automated).
Fair enough.
Serious question: who cares if the front end gets big?


I have had customers that do --- and I have seen apps that find themelves
slowing down when they are approaching the 1gb or 2gb borders (depending on
Jet version).

--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.

Nov 12 '05 #21

P: n/a
None that I know of. It will rewset security (just like Compact always
does), but that does not count as a danger....
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.
"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:ng********************************@4ax.com...

Hmmm... Interesting idea for automatic maintenance, Michael....

Thanks!

Oh, any dangers you can think of? Other then the obvious ones (backup
before, etc.)?

On Fri, 19 Sep 2003 10:35:48 -0700, "Michael \(michka\) Kaplan [MS]"
<mi*****@online.microsoft.com> wrote:
I think periodic compact would make sense. You could easily do this by
altering the value of
CurrentDb.Properties("Auto Compact").Value
every 10 times or something.

--
Ever Stop To Think And Forget To Start Again?

Nov 12 '05 #22

P: n/a
"David W. Fenton" <dX********@bway.net> wrote...
My current A2K apps start out about 2.5-5MBs and grow a couple of
MBs under daily use and then stop growing.

I don't see this as an issue worth worrying about.
So perhaps you do not need to worry. Others (including me, formerly) have
different experiences.
If a front end is growing more than that amount, then there must be
something wrong with the way the front end is designed, and *that*
should be changed, instead of requiring a compact.


This is an incorrect statement. There are just cases where Jet does not
properly optimize to use "unused" space prior to compact.

FWIW, I would appreciate if you did not assume that anyone with a different
experience than you must have something wrong in the databases at which they
are looking.
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.

Nov 12 '05 #23

P: n/a
Well, look in Tools|Options to see if there is a "Compact on Close" option.
If there is, then yes. If not, then no.
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.
"Deano" <ma************@hotmail.com> wrote in message
news:Ee********************@wards.force9.net...
Michael (michka) Kaplan [MS] wrote:
I think periodic compact would make sense. You could easily do this by
altering the value of

CurrentDb.Properties("Auto Compact").Value

every 10 times or something.

Does this work in Access 2000?

Nov 12 '05 #24

P: n/a
Does anyone have any working code to check the LDB file as to how many
users are logged in & if only 1 user is logged in, then offer to
Compact ... else inform the user that x,y,z, ... users are currently
logged in & they need to log-out before a Compact can be accomplished.

Thx & Best Rgds,
Prakash.


ha********@aol.com (Hank Reed) wrote in message news:<f4**************************@posting.google. com>...
I am running a normal split front end / back end system with 15-20
users. The first time anyone logs in each day, I compact the back end
( and also back it up ) before connecting to it. I add a record to an
[Action] Table so no one else does it.
Recently I had the network guy write a script that brings up one
PC at 5:00 AM so that the compacting is all taken care of when people
come to work.

Write me direct if you need more details

Hank Reed

Nov 12 '05 #25

P: n/a
mi*****@online.microsoft.com (Michael (michka) Kaplan [MS]) wrote
in <3f********@news.microsoft.com>:
"David W. Fenton" <dX********@bway.net> wrote...
This only matters if the data changes in ways that no longer
match the original optimization during the lifetime of the front
end. This will show up as degraded performance. If a client says
their database is running slowly, we compact the back end
(assuming they don't already have a regulard compact routine in
place). If it still runs slowly, then we compact the front end,
or replace it with a fresh copy (usually the latter, since that
process is more likely to be automated).


Fair enough.


I've encountered this scenario far too infrequently to think it's
worth implementing any kind of automated compacting of the front
end.

Indeed, the only time I've actually found query optimization
problems has been during development with complex queries where a
query with dozens of tables designed at the beginning of the life
of the app could not be edited successfully unless the editing was
preceded by a compact. Without it, any editing would give QUERY TOO
COMPLEX errors. And each additional change required an explicit
SAVE before running to avoid recurrence of the errors.

But this was a very unusual query, to say the least!
Serious question: who cares if the front end gets big?


I have had customers that do --- and I have seen apps that find
themelves slowing down when they are approaching the 1gb or 2gb
borders (depending on Jet version).


The app itself is approaching that size? *boggle*

I can't imagine what could cause a front end to get that big!

Or do you mean that the front end slowed down when the back end
approached that size, and frequent compacting of the front end kept
performance normal? If it's this latter, than that's a very good
thing to know.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #26

P: n/a
mi*****@online.microsoft.com (Michael (michka) Kaplan [MS]) wrote
in <3f******@news.microsoft.com>:
"David W. Fenton" <dX********@bway.net> wrote...
My current A2K apps start out about 2.5-5MBs and grow a couple
of MBs under daily use and then stop growing.

I don't see this as an issue worth worrying about.


So perhaps you do not need to worry. Others (including me,
formerly) have different experiences.


Some specific examples would be very helpful.
If a front end is growing more than that amount, then there must
be something wrong with the way the front end is designed, and
*that* should be changed, instead of requiring a compact.


This is an incorrect statement. There are just cases where Jet
does not properly optimize to use "unused" space prior to compact.

FWIW, I would appreciate if you did not assume that anyone with a
different experience than you must have something wrong in the
databases at which they are looking.


Well, give us some specific scenarios, please.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #27

P: n/a
mi*****@online.microsoft.com (Michael (michka) Kaplan [MS]) wrote
in <3f********@news.microsoft.com>:
"David W. Fenton" <dX********@bway.net> wrote...
>FWIW, I would appreciate if you did not assume that anyone with
>a different experience than you must have something wrong in
>the databases at which they are looking.
Well, give us some specific scenarios, please.


I have not been doing active client development in Microsoft
Access for a long time, as you know. If you are so sure that you
are right that you are unwilling to accept that my past
recollections might have some truth in them from time to time,
then I guess I will have to just accept that you think I am full
of crap.


Well, I'm sorry you don't remember the scenarios, as I was simply
looking for things to watch out for should I encounter them.
Hopefully others who run into this sort of issue and *do* like to
compact -- if not for these issues then to reclaim space after
creating temp tables and other, similar operations that obviously
can bloat up a db fast -- can be glad that an answer has been
provided for them.


Well, my point is that most of the things that bloat a front end
can be designed out of the front end, e.g., temp tables (which are
better placed in a discardable/recreatable temp.mdb). If there are
things that *can't* be designed out of the front end and still lead
to significant bloat (i.e., more than 50% and bloat that continues
to increase with use), I'd surely like to know about them. We
recently discussed setting properties of the new printer object,
and it was said that this leads to lots of bloat. One suggestion to
avoid that was to set only the parameters that had changed, so that
bloat was minimized.

That's the kind of example I'm looking for -- as a way of
evaluating whether or not a technique that is bloat-creating is
worth implementing or not. If such a technique becomes essential,
then it is worth implementing some kind of regular front end
compaction.

I'm simply interested in scenarios to look out for.

But I would say that the most common bloating scenarios can be
avoided with proper design.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #28

P: n/a
The most common scenarios related to reports that were creating temp queries
to do their work -- even moderately complex grouping can require this for
the SVT(s) behind the report.
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:93***************************@24.168.128.74.. .
mi*****@online.microsoft.com (Michael (michka) Kaplan [MS]) wrote
in <3f********@news.microsoft.com>:
"David W. Fenton" <dX********@bway.net> wrote...
>FWIW, I would appreciate if you did not assume that anyone with
>a different experience than you must have something wrong in
>the databases at which they are looking.

Well, give us some specific scenarios, please.


I have not been doing active client development in Microsoft
Access for a long time, as you know. If you are so sure that you
are right that you are unwilling to accept that my past
recollections might have some truth in them from time to time,
then I guess I will have to just accept that you think I am full
of crap.


Well, I'm sorry you don't remember the scenarios, as I was simply
looking for things to watch out for should I encounter them.
Hopefully others who run into this sort of issue and *do* like to
compact -- if not for these issues then to reclaim space after
creating temp tables and other, similar operations that obviously
can bloat up a db fast -- can be glad that an answer has been
provided for them.


Well, my point is that most of the things that bloat a front end
can be designed out of the front end, e.g., temp tables (which are
better placed in a discardable/recreatable temp.mdb). If there are
things that *can't* be designed out of the front end and still lead
to significant bloat (i.e., more than 50% and bloat that continues
to increase with use), I'd surely like to know about them. We
recently discussed setting properties of the new printer object,
and it was said that this leads to lots of bloat. One suggestion to
avoid that was to set only the parameters that had changed, so that
bloat was minimized.

That's the kind of example I'm looking for -- as a way of
evaluating whether or not a technique that is bloat-creating is
worth implementing or not. If such a technique becomes essential,
then it is worth implementing some kind of regular front end
compaction.

I'm simply interested in scenarios to look out for.

But I would say that the most common bloating scenarios can be
avoided with proper design.

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

Nov 12 '05 #29

P: n/a
"David W. Fenton" <dX********@bway.net.invalid> wrote...
The most common scenarios related to reports that were creating
temp queries to do their work -- even moderately complex grouping
can require this for the SVT(s) behind the report.
By "moderately complex grouping" do you mean 5 levels? Ten?

I'm also not certain what SVT refers to.


SVT == segmented virtual table. A construct that Access reports use to
create the various different grouping levels. When combined with sorting,
they can often lead to very processor-intensive reports....
In any event, I've got plenty of reports out there with 5-10 levels
of grouping and haven't seen any bloat beyond what I consider
perfectly safe. Perhaps if I had dozens of such reports that were
run on a daily basis the bloat would be greater? But that doesn't
seem to make sense from my experience, which is that these dbs with
one or two of these complex reports that are run fairly regularly
grow during the first month or so of use to a certain point and
then never grow any larger. I don't consider that a problem -- it's
only if growth were to continue after that point that I'd be
worried.


Well, as I said before, you do not have to worry. I pointed out three
reasons why it would be a good idea to periodically compact the front end;
if you do not believe any of them apply to you then you have little to worry
about.
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.

Nov 12 '05 #30

P: n/a
On Fri, 19 Sep 2003 23:16:09 GMT, dX********@bway.net (David W. Fenton)
wrote:
no****@nospam.nospam (Steve Jorgensen) wrote in
<75********************************@4ax.com>:
On Fri, 19 Sep 2003 05:28:00 -0700, "Michael \(michka\) Kaplan
[MS]"
<mi*****@online.microsoft.com> wrote:
This is not necessarily the best course of action, though, for at
least two reasons:

1) Compact resets queries so they can be recompiled the next time
they are run. Since data changes over times the QPE (query
processing engine) may have a different optimization plan doe
ideal data retreival, but if you never make that recompile
happen, you will never recieve that benefit.

2) If you run even moderately complex reports, Access creates a
lot of temp queries/SVTs that will bloat up the database a bit.
Only a compact can shrink the size back down. Since there is no
way other than by experimentation to determine when this is the
case, it is often best to assume it may happen and prepare for
it.


Thanks for the info. I guess there is reason to compact the
front-end after all.


Have you seen my response to Michael? Do you not think that I have
some points there?


I had not seen your reply, and I'd say you do have some points there.

On the other hand, something I often do (and I know I shouldn't, but ...)
is to deploy a front-end that has never been used against data on the scale
that will be seen after a few months in production. In this case, it would
not be strange for a really suboptimal query plan to be cached with the
deployed front-end. I guess if I attached the front-end to a realistic
data set and ran it through its paces before deployment, this would not be
a worry. I'm just not sure if/when I'll have a set of procedures in place
to see that this happens even most of the time.

With regard to the temporary querydefs created when running reports, I'd
want to know if they continue to stack up ad infinitum, or if the bloating
is contained at some maximum. If it's contained, I'd think a front-end
compact might be overkill, but if it is uncontained... Has anyone tested
this?
Nov 12 '05 #31

P: n/a
mi*****@online.microsoft.com (Michael (michka) Kaplan [MS]) wrote
in <3f********@news.microsoft.com>:
"David W. Fenton" <dX********@bway.net.invalid> wrote...
>The most common scenarios related to reports that were creating
>temp queries to do their work -- even moderately complex
>grouping can require this for the SVT(s) behind the report.


By "moderately complex grouping" do you mean 5 levels? Ten?

I'm also not certain what SVT refers to.


SVT == segmented virtual table. A construct that Access reports
use to create the various different grouping levels. When combined
with sorting, they can often lead to very processor-intensive
reports....


OK, I know what that is. I just didn't recognize the acronym.
In any event, I've got plenty of reports out there with 5-10
levels of grouping and haven't seen any bloat beyond what I
consider perfectly safe. Perhaps if I had dozens of such reports
that were run on a daily basis the bloat would be greater? But
that doesn't seem to make sense from my experience, which is
that these dbs with one or two of these complex reports that are
run fairly regularly grow during the first month or so of use to
a certain point and then never grow any larger. I don't consider
that a problem -- it's only if growth were to continue after
that point that I'd be worried.


Well, as I said before, you do not have to worry. I pointed out
three reasons why it would be a good idea to periodically compact
the front end; if you do not believe any of them apply to you then
you have little to worry about.


I don't know whether or not any of them apply to me, but my
question was more of the nature "under what circumstances *would*
it be likely to occur?"

Since you seem to be more interested in having a snit-fit about me
asking the question and won't give a direct answer, I may never
know.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #32

P: n/a
no****@nospam.nospam (Steve Jorgensen) wrote in
<t1********************************@4ax.com>:
On Fri, 19 Sep 2003 23:16:09 GMT, dX********@bway.net (David W.
Fenton) wrote:
no****@nospam.nospam (Steve Jorgensen) wrote in
<75********************************@4ax.com>:
On Fri, 19 Sep 2003 05:28:00 -0700, "Michael \(michka\) Kaplan
[MS]"
<mi*****@online.microsoft.com> wrote:

This is not necessarily the best course of action, though, for
at least two reasons:

1) Compact resets queries so they can be recompiled the next
time they are run. Since data changes over times the QPE (query
processing engine) may have a different optimization plan doe
ideal data retreival, but if you never make that recompile
happen, you will never recieve that benefit.

2) If you run even moderately complex reports, Access creates a
lot of temp queries/SVTs that will bloat up the database a bit.
Only a compact can shrink the size back down. Since there is no
way other than by experimentation to determine when this is the
case, it is often best to assume it may happen and prepare for
it.

Thanks for the info. I guess there is reason to compact the
front-end after all.
Have you seen my response to Michael? Do you not think that I
have some points there?


I had not seen your reply, and I'd say you do have some points
there.

On the other hand, something I often do (and I know I shouldn't,
but ...) is to deploy a front-end that has never been used against
data on the scale that will be seen after a few months in
production. In this case, it would not be strange for a really
suboptimal query plan to be cached with the deployed front-end. I
guess if I attached the front-end to a realistic data set and ran
it through its paces before deployment, this would not be a worry.
I'm just not sure if/when I'll have a set of procedures in place
to see that this happens even most of the time.


Well, if I knew that data growth was going to be large enough
during deployment to majorly invalidate queryplans, then it would
be obvious that I'd need to either include some compacting or have
end users know that if things slow down, try compacting.

I've never deployed in that kind of situation, as almost every app
I've ever created has been running against existing data, and new
data is only a small percentage of the total.
With regard to the temporary querydefs created when running
reports, I'd want to know if they continue to stack up ad
infinitum, or if the bloating is contained at some maximum. If
it's contained, I'd think a front-end compact might be overkill,
but if it is uncontained... Has anyone tested this?


Michael does not seem to be interested in answering the question,
and I don't know that I have any mechanism for testing it, since I
don't know the exact circumstances that produce the bloat.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #33

P: n/a
Thanks for the replies folks. Some useful info there.

Cheers, Mike

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. ..
Is it?

Nov 12 '05 #34

P: n/a
"David W. Fenton" <dX********@bway.net.invalid> wrote...
Michael does not seem to be interested in answering the question,
and I don't know that I have any mechanism for testing it, since I
don't know the exact circumstances that produce the bloat.


Sigh.... David, this is not what I said.

I have not been a member of the Access team for nearly five years. I no
longer have access to the bug databases to see the bugs that were
reported/investigated so all I have is the memory of the ones that I saw and
the ones I investigated.

If you can quote exact repro details of development problems that you faced
half a decade ago that you had no real reason to try to recall, then I will
admit I am somehow inferior to your development skills. Otherwise you could
simply accept that someone claims that there is a possibility here and
either take the advice or choose not to. You have made it clear that it does
not seem to apply to you, no need to make bogus claims about my interests?
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.

Nov 12 '05 #35

P: n/a
Mike MacSween wrote:
Thanks for the replies folks. Some useful info there.

Cheers, Mike

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. ..
Is it?


And some entertaining ones as well. I like the phrase 'snit-fit'. I never
heard this before but it sounds just right :)
Nov 12 '05 #36

This discussion thread is closed

Replies have been disabled for this discussion.