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
im****ts, 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. ; )
"Arvin Meyer [MVP]" <arvinm@[EMAIL PROTECTED]
> wrote in message
news:eUgOBV7UJHA.1164@[EMAIL PROTECTED]
> "David W. Fenton" <XXXusenet@[EMAIL PROTECTED]
> wrote in message
> news:Xns9B66BF659E401f99a49ed1d0c49c5bbb2@[EMAIL PROTECTED]
>
>> Actually, MS does use the phrase "compiled query plan" in its
>> do***entation, 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
> tem****ary 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.
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
|
