Compact & Repair/DB Size

[Clifford Bass]

<tongue-in-cheek>Wrong forum for that!</tongue-in-cheek>

But seriously, you could try http://forums.thunderbirdforum.com/

Clifford Bass

 

[Arvin Meyer [MVP]]

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. ; )

Disconnect the seat belt warning. Works every time.

 

[David W. Fenton]

Compile is the correct terminology.

True. But "decompile" is another matter. The queries are not
decompiled -- it's just that the data pages storing the compilation
are discarded in a compact operation.

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.

But query compilation cannot possible contribute any large amount to
that bloat, in my opinion.

[]

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.

For what it's worth, I've never seen the compiled query performance
improvement to be worth it to make me transfer SQL in code into
saved querydefs, or to regularly compact front ends (in order to
recompile with recent table stats). It just doesn't seem like that
much of an issue in an app when most of the time is taken up waiting
for the user to take the next action in the UI.

 

[Arvin Meyer [MVP]]

True. But "decompile" is another matter. The queries are not
decompiled -- it's just that the data pages storing the compilation
are discarded in a compact operation.

Not to be pedantic, but but if the stored compilation were discarded
wouldn't that decompile the query?

For what it's worth, I've never seen the compiled query performance
improvement to be worth it to make me transfer SQL in code into
saved querydefs, or to regularly compact front ends (in order to
recompile with recent table stats). It just doesn't seem like that
much of an issue in an app when most of the time is taken up waiting
for the user to take the next action in the UI.

Compilation is so fast, that I'll agree, the extra second at most is
negligible. But I disagree that recompilation doesn't measurable improve
query performance. Most queries won't benefit very much, but the difference
in performance in queries that use large tables, particularly tables that
deal with thousands of deletes and appends can be improved by 10 to 20%.

 

[David W. Fenton]

Not to be pedantic, but but if the stored compilation were
discarded wouldn't that decompile the query?

Effectively. And, to be fair, that's how /decompile decompiles VBA
-- it just discards the p-code and keeps the canonical code.

Again, though, there is a commandline switch called "decompile" that
decompiles VBA code. That's all it does -- a 1:1 relationship
between operation and result.

You can't issue a command that decompiles your saved queries.
Indeed, you can't even tell if your query is compiled or not, and
without walking the QueryDefs collection, you can't tell anything
about the compiled SQL for form recordsources and combo/list box
recordsources. I guess you could decompile the latter by deleting
all the "~sq_" QueryDefs, but that wouldn't decompile everything.
And the space wouldn't be recovered until a compact.

Now, the space isn't recovered from a decompile until the compact,
but the point is it's the *compact* that recovers the space. In the
case of VBA bloat, you have to decompile to get maximum benefit. In
the case of compiled SQL, you just have to compact.

Compilation is so fast, that I'll agree, the extra second at most
is negligible. But I disagree that recompilation doesn't
measurable improve query performance. Most queries won't benefit
very much, but the difference in performance in queries that use
large tables, particularly tables that deal with thousands of
deletes and appends can be improved by 10 to 20%.

In my professional Access development (starting in 1996) I've never
seen a performance problem that would have benefited from regular
compacting of the front end.

 

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

David;

you're not a professional Access developer, you use Jet.

Any Access developer with a clue would be using SQL Server / ADP.

It's silly that you sit there and preach Jet replication and Jet
security, when Jet is nothing but a bloated, unstable, poorly
performing spreadsheet.

 

[Arvin Meyer [MVP]]

message
David;

you're not a professional Access developer, you use Jet.

Any Access developer with a clue would be using SQL Server / ADP.

It's silly that you sit there and preach Jet replication and Jet
security, when Jet is nothing but a bloated, unstable, poorly
performing spreadsheet.

================================================
While David and I occasionally disagree on methodology, I can assure you
that he is a professional developer in every sense of the word. He also has
considerably more experience, as well as intelligence than you. By even
spending time in this group, you define yourself as a neophyte with little
more than a big mouth. If, as you seem to believe, SQL-Server is the only
database worthy of mention, why is it that you do not spend time in those
newsgroups? Could it be because they laugh at your abysmal attempts to post?
We are laughing at you here as well.

 

[David W. Fenton]

"a a r o n . k e m p f @ g m a i l . c o m" <[email protected]>
wrote in
:

[nothing worth reading]

You know, if you weren't so phenomenally stupid, you might actually
be amusing.

But you're not.

You have no credibility and you keep making it worse with the idiocy
that you post. You're clearly mentally unbalanced and should seek
help. Certainly, nobody gives any credence to anything you post
here.

 

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

David;

At least I'm not a crybaby that uses a database that's been obsolete
for a decade.

Why dont' you go and play with Security and Replication.. oh yah, they
removed those features from Access 2007 (for you) right?

Personally, I can replication and Integrated Windows Authentication.

-Aaron

 

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

Bullshit Arvin;

you guys are both Jet crybabies.

Just because Jet might be more popular _IN_THIS_NEWSGROUP_ SQL Server
has won this war.

www.microsoft.com/sql - this is where Microsoft states that SQL Server
is the worlds most popular database.

why don't you keep your data in Excel, friggin wuss.
At least Excel can handle 20,000 records without puking!