Compact & Repair/DB Size

[Secret Squirrel]

I have a database that is about 11MB in size. When I run the compact and
repair utility it doesn't seem to get any smaller. My question is...will this
run slow on a network because of it's size? Also, when I create a new DB and
then import all the objects from this current DB the size is only 7MB. Does
that mean I have 4MB of bloat? How do I get my DB down to the 7MB? Also, I
could easily just use the new DB that I created but it doesn't import the
permissions. I would have to go through them all over again to reset them.
Any ideas?

 

[Clifford Bass]

Hi,

You could try the decompile that is very nicely documented by Tony at
http://www.granite.ab.ca/access/decompile.htm

Clifford Bass

 

[Secret Squirrel]

Thanks! Worked perfectly! Shrunk it down to 7MB

Hi,

You could try the decompile that is very nicely documented by Tony at
http://www.granite.ab.ca/access/decompile.htm

Clifford Bass

 

[John W. Vinson]

Thanks! Worked perfectly! Shrunk it down to 7MB

Do note that 7 (or 11, or even 50) MB is *tiny*. Several of my frontends are
100MB and up; backends can easily be a gigabyte.

 

[Arvin Meyer [MVP]]

I have a database that is about 11MB in size. When I run the compact and
repair utility it doesn't seem to get any smaller. My question is...will
this
run slow on a network because of it's size? Also, when I create a new DB
and
then import all the objects from this current DB the size is only 7MB.
Does
that mean I have 4MB of bloat? How do I get my DB down to the 7MB? Also, I
could easily just use the new DB that I created but it doesn't import the
permissions. I would have to go through them all over again to reset them.
Any ideas?

In addition to what the others have said, I suspect that your database is
not split. I say this because database bloat that occurs on a backend,
compacts easily, and a front-end should not be on the server. The bloat on a
Front-end can occur if there are lots of queries and SQL statements.
Decompiling eliminates those.

Now if your database is 11 MB on the back-end, and you've designed indexes
and queries correctly, you'll rarely bring more than a MB of data across the
network. Usually less than 10 or 15 K of data, and that occurs in subsecond
times.

 

[Pete D.]

To get file smaller you might try the /decompile option on it but make sure
you have a backup first. After decompile open file, save/compile file and
then do a compact repair on it.

msaccess.exe /decompile "c:\something\OrTheOther.mdb"

 

[David W. Fenton]

The bloat on a
Front-end can occur if there are lots of queries and SQL
statements. Decompiling eliminates those.

Er, that doesn't make any sense.

Decompiling, as in using the /decompile commandline switch, is not
going to have any effect whatsoever on queries and SQL statements
that lead to bloat, as the bloat they cause is because of the
compiled query plans, which are not stored in VBA code, but in
MSysObjects, an Access system table. A compact causes query
compilation to be discarded, but a decompile is not going to have
any effect on that whatsoever.

And saved compiled queries are not going to continually bloat, since
they will only be saved the first time the SQL statement runs, and
won't be recalculated again until the query is changed or the front
end is compacted (which causes all saved queries to be marked for
recompiling).

 

[Arvin Meyer [MVP]]

Actually, I meant compacting which decompiles queries and SQL statements as
well as eliminating the temp queries which are created when SQL statements
are first run. Decompiling, without a compact does not reduce the size of
anything.

As queries are run again, they are again compiled. Since they aren't run all
at the same time, or if an app is decompiled (queries compacted), the
database grows continually until they are all run, then it stops. The
process begins anew, when the database is compacted again.

You and I are saying the same thing.

 

[Arvin Meyer [MVP]]

The preferred method is to decompile, compact, then recompile. And it goes
without saying, always make a copy of the file before decompiling.

 

[Pete D.]

I'm more simplistic, I see a size reduction over just compress repair and
the original suggestion came from here many years ago. And to the original
question 11mb really isn't going to normally cause a network performance
problem anyway so don't sweat the size.

Actually, I meant compacting which decompiles queries and SQL statements
as well as eliminating the temp queries which are created when SQL
statements are first run. Decompiling, without a compact does not reduce
the size of anything.

As queries are run again, they are again compiled. Since they aren't run
all at the same time, or if an app is decompiled (queries compacted), the
database grows continually until they are all run, then it stops. The
process begins anew, when the database is compacted again.

You and I are saying the same thing.

 

[Tony Toews [MVP]]

Actually, I meant compacting which decompiles queries and SQL statements as
well as eliminating the temp queries which are created when SQL statements
are first run. Decompiling, without a compact does not reduce the size of
anything.

As queries are run again, they are again compiled. Since they aren't run all
at the same time, or if an app is decompiled (queries compacted), the
database grows continually until they are all run, then it stops. The
process begins anew, when the database is compacted again.

You and I are saying the same thing.

Sort of. But lets not use the word decompile with it comes to
queries. The proper terminology is query plans or statistics or
something like that.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Secret Squirrel]

You are correct. My DB is not split on the server. The reason being is
because we use citrix in our office so all the desktops are basically "dumb
terminals". I don't know any other way to split it using citrix. Maybe you do?

 

[David W. Fenton]

You and I are saying the same thing.

I think you're using confusing teminology unless you explicitly
state that your talking about "decompiling" queries. I tend not to
use that phrase, as there is no action that decompiles queries by
itself -- it's only one of the many things that a compact does in
discarding temporary data.

I think it would be less confusing all around to reserve the term
"decompile" for the process of decompiling VBA code.

All that said, I think the amount of space taken up by compiled
query plans is minuscule compared to all the other forms of bloat
that are possible.

 

[David W. Fenton]

Sort of. But lets not use the word decompile with it comes to
queries. The proper terminology is query plans or statistics or
something like that.

Actually, MS does use the phrase "compiled query plan" in its
documentation, if I'm not mistaken.

But I agree that we should reserve "decompile" as advice for
reducing bloat to removing compiled VBA p-code.

And I think that a compact will remove far, far more bloat from
other areas than it will in discarding the compiled query plan,
which cannot possibly take up any significant amount of space, even
in a front end with 100s of stored QueryDefs.

Add to that the fact that it's only a temporary step, as the queries
will be recompiled as the app is used and each query is opened, I
think that it's really not of much value at all to consider the
discarding of compiled query plans in regard to the subject of
reducing bloat. It's not a part of bloat that is a *problem* that
needs correction so I think it should be ignored.

 

[David W. Fenton]

=?Utf-8?B?U2VjcmV0IFNxdWlycmVs?=

My DB is not split on the server. The reason being is
because we use citrix in our office so all the desktops are
basically "dumb terminals". I don't know any other way to split it
using citrix. Maybe you do?

You split just like you would if you had non-dumb workstations, but
you put the front end for each user in a folder accessible to that
user (usually, but not always, in the user's profile). Tony Toews's
front-end updater is designed to work with this transparently, so
there's nothing complicated about implementing it at all.

In other words, there really is no proper justification for not
splitting your app.

 

[John W. Vinson]

You are correct. My DB is not split on the server. The reason being is
because we use citrix in our office so all the desktops are basically "dumb
terminals". I don't know any other way to split it using citrix. Maybe you do?

One standard way is to have the backend AND the frontends on the Citrix server
machine, or on another machine on the same fast, stable wired LAN as the
Citrix server. The backend is in a shared folder, and each user has a folder
of their own that they log in to from Citrix, and which contains their copy of
the frontend.

 

[Arvin Meyer [MVP]]

It is especially important to split the app in Citrix, or for that matter in
any Thin Client environment. In Citrix, Access can be corrupted if a
connection breaks on one user with another user in the database. As the
other have suggested, create a separate folder for each user.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

Actually, MS does use the phrase "compiled query plan" in its
documentation, if I'm not mistaken.

But I agree that we should reserve "decompile" as advice for
reducing bloat to removing compiled VBA p-code.

Compile is the correct terminology. SQL must be compiled to run. That said,
the overwhelming bloat is caused by VBA p-code. The fact that an
MDE/ADE/ACCDE still bloats with p-code removed, and after being fully
compiled, is evidence that there are other causes of bloat.

In SQL-Server, Stored Procedures are compiled similar to Access queries,
Views are not compiled (or at least in earlier versions aren't and I haven't
noticed a change in that). So a View plan is discarded at the end of a
session, while a Stored Proc is not. If you've ever seen the terminology
"DROP Procedure ... " that is the reason, the developer wants it to
recompile, so that a new plan will be created. In Access, queries stay
compiled until a compact.

I agree that compiling a query may add only a few hundred bytes (or more
depending upon the complexity), so each query would not add much to bloat,
but a 1000 of them certainly would. In addition, a SQL statement builds a
temporary query, also compiled, which is stored until a compact, so that
adds even more that is cleaned with a compact.

To answer the question about why to do it for queries. The query plan is
designed to be efficient with the number of records it is dealing with, so
if a significantly different number of records are run, it pays, in
performance, to regularly compact the database.

 

[a a r o n . k e m p f]

I'm not so sure I agree with your over-simplification on SQL Server
views:
------------------------------------------------------------------------------------------------------------
http://jmkehayias.blogspot.com/2008/09/sql-server-views-and-performance.html
If you look at the cache now, you should see that this query was
parameterized by the SQL Server, and stored in the plan cache for
reuse.

------------------------------------------------------------------------------------------------------------


and basically, the bottom line is this:

a) if you're tired of bloat
b) if you're tired of queries on top of queries crapping out (the
query is too complex)
c) if you're tired of compact and repair
d) if you're tired of connection strings and linked tables and linked
SQL Passthroughs-

then it's time to move to SQL Sever.

SQL Server just works. SQL Server is intelligent.
Jet is just plain stupid.

 

[Pete D.]

Just my uneducated reasoning. Most files I create are for small workgroups
and to be honest they are never finished. The fact that I am always making
changes to code, tables, queries to tweak something new is the reason I
believe that I create bloat that decompile reduces. And as Secret Squirrel,
from his history of posting, appears to do the same thing is the reason I
suggested it. Fact is my files are not huge but in a 100mb file I have had
reductions of up to 40mb with the decompile and if I leave it alone for
months (no changes) it doesn't re-bloat (is that a real word). Maybe if I
ever go pro I'll stop the bad habits of not cleaning up the files before
putting them to use. In my current world of dealing with old flat file
imports, sucking up excel files and trying to extract information and make
it pretty I don't see an end to it in the near future. Many of my projects
are short lived. I can spend weeks pulling together spreadsheets, csv files
and old Fox Pro or Dbase files to produce statistical info in Access. Then
I get a nod, a thankyou and don't hear about it again until the next end of
fiscal year or an audit. Mine is a real world at the user level using all
of the Office Suite to get what I need. Might be why I know a little about
everything but not a lot of anything. Now if you could help get my 94 Tbird
computer to stop kicking off the active seatbelt warning when I take a left
turn you would all be my hero's. ; )