performance rate / drag time

[JohnLute]

I'm running 2003 and recently created a new file for my front end and
imported all objects into it. Compacting/repairing trimmed it down from 99MB
to about 42MB.

It's running fine (actually it ran fine prior to importing - I just wanted
to get its size down) EXCEPT now it "drags". For example, after a form loads
I'll click on a button or other control and it takes a beat or two for the
button to click or for the cursor to move. Very annoying!

Compacting/reparing again eliminates the drag! However when I close the
database and then open it again the dragging returns until I compact and
repair again!

Anyone have any ideas what's going on?

Thanks for your help!

 

[Jerry Whittle]

A 42 mb, much less 99 mb, front end is huge! What all is inside that monster
and what is going on? How big does it get after running it for a while?

 

[JohnLute]

Hi, Jerry.

It's been in development for 7 years now so it's big. I compaact DAILY
however recently, it exploded from about 80MB to 99MB for no apparent reason
so I followed the advice of Gina Whipp and others and imported all objects
into a new file. When I did that and compacted it went down to abou 38MB
however I've made some small changes since and it's expanded again to about
42MB. I can't imagine that my changes added 4MB more so I'm starting to
wonder what exactly is going on. I'm using 2003 with SP3 so I believe I'm
current with Access' latest. Here's a thread that may provide further insight
http://groups.google.com/group/micr...coding/browse_thread/thread/537c09c813ba537d#

Anyway, to address your question more specifically the FE has 300+ forms and
300+ reports not to mention a good number of queries. Most of the forms and
reports are fairly complex. The FE links to 300+ tables.

 

[Albert D. Kallal]

This sounds like track-auto name correct.....

I would turn that off...

Here is a nice list of other things to check:
http://www.granite.ab.ca/access/performancefaq.htm

 

[Jeff Boyce]

How is your form connected to its data? Is the form trying to load the
entire database/table, or a very limited subset?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JohnLute]

Hi, Albert. It is suspiciously that however I've always had it turned off.

 

[JohnLute]

Hi Jeff.

The main form that opens when the database is opened is loading the
"primary" recordset of about 14,000 records along with a listbox that lists
related records. Very minimal.

Keep in mind that this problem did NOT exist until after I created the new
front end and imported all objects into it. Also, keep in mind that it
"clears up" after I compact and repair.

It's all very peculiar. I open the database and it drags. I compact/repair
and it absolutely zooms. I close the database and open again and it drags. I
compact and repair and ZOOM!

That's my problem statement however I fear it's so peculiar that I'll never
figure it out.

 

[John W. Vinson]

It's been in development for 7 years now so it's big. I compaact DAILY
however recently, it exploded from about 80MB to 99MB for no apparent reason
so I followed the advice of Gina Whipp and others and imported all objects
into a new file. When I did that and compacted it went down to abou 38MB
however I've made some small changes since and it's expanded again to about
42MB. I can't imagine that my changes added 4MB more so I'm starting to
wonder what exactly is going on.

Do you routinely compile the VBA project after making changes to it? Consider
making a backup, compacting, decompiling, compacting again, and then
recompiling: what size do you get?

 

[JohnLute]

Hi, John.

Well, I just added "Option Explicit" to the vast majority of objects that
have code and then compiled and compacted/repaired. The database has grewto
54,096KB. I then decompiled, compacted and re-compiled and it's now 54,092KB.

 

[Jerry Whittle]

I'm still astounded by the size of your FE. Do you have any tables in it like
seldom changing lookup tables? Graphics galore?

Do you have a lot of complicated queries such as Union queries or queries
built upon other queries?

Queries with a lot of sorting and grouping? They can cause bloat. Especially
for reports, remove all sorting in the underlying queries as the reports
resort and group everything anyway.

On thing I recommend trying is to temporarly disabling your PCs virus
checker for testing. If that speeds things up, change the virus checker to
exclude the checking of .MD* or .ACC* files.

 

[Jeff Boyce]

John

Yeah, I realized after <send> that you had mentioned that it was OK before.
This may be one of life's little mysteries -- even if you get resolution,
you may never get explanation <g>!

I would point out that transferring 14,000 records to open a form could drag
down performance. As another option, if the form's source is a query, and
that query points to a combobox on the form (empty to start with), then you
could have a form that has NO records when it opens, and one ONE record
after selection in a combobox.

If you'd rather not load 14,000 possibilities into the combobox (I know I
wouldn't!), you could take a look at Allen Browne's website. He has a "how
to NOT load thousands (or tens of thousands) of records into a combobox".
Hint: he delays the assignment of the record source until after the first
'n' characters has been typed in.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dirk Goldgar]

Hi, Albert. It is suspiciously that however I've always had it turned off.

Do check that it's still turned off in the new database, John. That is far
and away the most likely cause of the symptoms you're reporting. If you
imported all objects into a new database, as you said, Name AutoCorrect
wouldn't necessarily be turned off in the new database.

 

[JohnLute]

Hi, Dirk

Do check that it's still turned off in the new database, John. That is far
and away the most likely cause of the symptoms you're reporting. If you
imported all objects into a new database, as you said, Name AutoCorrect
wouldn't necessarily be turned off in the new database.

I was very careful about that and it is indeed off.

After lots of head scratching I began wondering about my importing course of
events. It became clear that I goofed up somewhere along the way and affected
the security. I did this:

1. Imported the tables back into the FE.
2. Compiled, compacted.
3. Created a new security file.
4. Copied and renamed the FE as BE.
5. Deleted all objects out of the BE except the tables.
6. Opened the FE and deleted all of the tables.
7. Linked to the new BE.

Everything seems to be working just SNAPPY now even with the new feature of
playing sounds on clicking certain buttons. I can only guess that I glitched
the security.

Now the FE is about 59MB and the BE just erupted from about 45MB to 65MB!
Compacting these doesn't reduce them in the least.

 

[JohnLute]

Hi, Jerry.

I'm still astounded by the size of your FE. Do you have any tables in it like
seldom changing lookup tables? Graphics galore?

No tables. Links only. No graphics galore, either. There's a few graphics
but they're quite small and probably add up to only 1-2MB at the most.

Do you have a lot of complicated queries such as Union queries or queries
built upon other queries?

I think I have some intensely complicated ones. I suppose it's a matter of
opinion. I've posted my database size and people generally respond with,
"Hmmpf...that's not so grand - I've seen twice that easily." In any case, I
know my queries are complex because a I have a couple to which I can NOT add
any more tables or queries lest the dreaded "Query too complex." monster
jumps up.

I've noticed in the past that when working on these queries that the
database almost instantly doubles in size. Compacting squeezes it back down
however. In time, it just keeps growing regardless.

Queries with a lot of sorting and grouping? They can cause bloat. Especially
for reports, remove all sorting in the underlying queries as the reports
resort and group everything anyway.

I really try to follow that rule when ever possible.

On thing I recommend trying is to temporarly disabling your PCs virus
checker for testing. If that speeds things up, change the virus checker to
exclude the checking of .MD* or .ACC* files.

Thanks. I'll need to look into that.

Ultimately, I think you may have hit on the problem. Perhaps it's those
disgustingly complex queries.

 

[JohnLute]

Hi, Jeff.

Yeah, I realized after <send> that you had mentioned that it was OK before.
This may be one of life's little mysteries -- even if you get resolution,
you may never get explanation <g>!

I think I have resolved it and doubt that I'll ever get an explanation!

I would point out that transferring 14,000 records to open a form could drag
down performance. As another option, if the form's source is a query, and
that query points to a combobox on the form (empty to start with), then you
could have a form that has NO records when it opens, and one ONE record
after selection in a combobox.

I read about this all the time but I've never had an issue with 14,000
records - at least until recently however as I stated it's been resolved and
the app just absolutely ZOOMS now. I may get a SLIGHT hesitation and a flash
of "Calculating..." and then it's POP!!! Ready to run!

If you'd rather not load 14,000 possibilities into the combobox (I know I
wouldn't!), you could take a look at Allen Browne's website. He has a "how
to NOT load thousands (or tens of thousands) of records into a combobox".
Hint: he delays the assignment of the record source until after the first
'n' characters has been typed in.

Good luck!

Thanks, Jeff. I'll keep this in mind for the future when records blow up to
a point when speed is less than optimum.