35  16975 
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.
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?
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?
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.
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?
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?
"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
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?
>
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.
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
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
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
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
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.
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.
"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
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 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
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?
"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.
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?
"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.
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?
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 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 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 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
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
"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.
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? 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 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
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?
"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.
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 :) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: James Franklin |
last post by:
Hi,
I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a database
while it is open regularly fails, seemingly...
|
by: smitty mittlebaum |
last post by:
I have a strange problem that has reared its ugly head in the last few
weeks.
I have the option "Compact on Close" (Tools, Options, General tab)
enabled on my development machine. I did this...
|
by: KEVIN97810 |
last post by:
Hello to all,
Assume my mdb name is Cust.MDB. I want to compact & repair
the same Cust.MDB when the user exit the program. Is there a way you can do it
in code behind the EXIT button. I am...
|
by: Susan Bricker |
last post by:
Greetings. I just discovered the 'Compact on Close' option in
Access-Tools-Options-General Tab. It certainly sounds like a smart
idea to me. But before I turn that option on I thought I'd check...
|
by: Wayne |
last post by:
Does "Compact On Close" do a "Compact and Repair" or just a compact.
Is a compact necessary (or at least a good idea) on a regular basis,
say weekly, for a database that has several hundred records...
|
by: robert demo via AccessMonster.com |
last post by:
In my startup routine, I have the following code:
s = CLng(FileLen(filespec) / 1000000)
If s > 5 Then
'FIRST, BACKUP THE FRONT END
If BackupFrontEnd = False Then
Exit Function
End If
|
by: G Gerard |
last post by:
Hello
The more I use an application ( an mdb) created using MSAccess I notice
that the Byte size of the application keeps on increasing. Once in a while I
do a Compact and Repair Database...
|
by: Ron |
last post by:
New discovery.
If I take a perfectly good database, and "compact/repair" on it with Access
2000 (seems to be at multiple sites--I've tried it with my system here, at
another office on an...
|
by: ARC |
last post by:
Hello all,
Due to some users experiencing severe front end bloating (Normal compacted
front end .accdr is 27MB's, FE can bloat to 80-90+Mb's in a day), I want to
enable the "Compact on Close"...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
