slow large database!

[FresnoITAssistant]

I have an employee DB set up with a basis of 4 tables. It has 20 queries, 8 forms, and 10 reports running off of that set of data. I also have a macro set up for front end users (so that the switchboard is all they see). The file appears as 50,000+kb size! I can't understand why. The database is split, and I am running two front ends from the one backend. My back end is really small in comparison (300kb).

It's also running very slow when opening individual reports and forms as well as when you initially open the database. I've removed all packaged pictures and there doesn't appear to be any of the security I originally built in on the file any more. Of course, I have tried compacting several times, but can't seem to get the file size reduced. Compared to other databases on our system that perform similarly, this file is just huge!!! Any suggestions???

 

[Van T. Dinh]

* Try creating a new blank Front-End and import Queries/Forms/Reports...
that you need from the existing Front-End. Re-link Tables as required and
see if there is any improvement re size / speed.

* Have you applied all Service Releases / Service Patches for both the OS
and Microsoft Office / Access? This is VERY important if you use A2K0.

* See also Tony Toews' articles:

http://www.granite.ab.ca/access/bloatfe.htm

and

http://www.granite.ab.ca/access/performancefaq.htm

--
HTH
Van T. Dinh
MVP (Access)

I have an employee DB set up with a basis of 4 tables. It has 20 queries,

8 forms, and 10 reports running off of that set of data. I also have a
macro set up for front end users (so that the switchboard is all they see).
The file appears as 50,000+kb size! I can't understand why. The database
is split, and I am running two front ends from the one backend. My back end
is really small in comparison (300kb).

It's also running very slow when opening individual reports and forms as

well as when you initially open the database. I've removed all packaged
pictures and there doesn't appear to be any of the security I originally
built in on the file any more. Of course, I have tried compacting several
times, but can't seem to get the file size reduced. Compared to other
databases on our system that perform similarly, this file is just huge!!!
Any suggestions???

 

[Tõnu Aas]

The database is split, and I am running two front ends from the one
backend.

My back end is really small in comparison (300kb).
It's also running very slow when opening individual reports and forms as
well as when you initially open the database.

Dont split database into front/back-end. This makes it VERY slow.

Tõnu.

 

[Van T. Dinh]

Tonu

Splitting is recommended by Microsoft and used by most developers. There
may be a tiny performance hit using linked Tables but this is more than
compensated by the reduced network traffic (and hence faster retrieval of
data).

Note that in a split database application, the Back-End should be on a
shared directory and EACH user should have a copy of the Front-End on his /
her work-station. Basically, each user has exclusive use of his/her copy of
the Front-End and users only share the Back-End where the data resides.

See also Tony Toews' Web site:

http://www.granite.ab.ca/access/splitapp/index.htm

--
HTH
Van T. Dinh
MVP (Access)

 

[Tõnu Aas]

Splitting is recommended by Microsoft and used by most developers.

And I think that MS recommends also to upgrade your OS and buy
best machine available to compensate slow-down.
Splitting is good for developer, but not for user. On good & fast computers
it can be done.

See also Tony Toews' Web site:
http://www.granite.ab.ca/access/splitapp/index.htm

From this website:
"One complex form went from a second or so to about eight or ten seconds."

Is this a "tiny performance hit" ??? 8 ... 10 times is tiny perfomance hit
?
If you dont use subforms and all good stuff then you can use paper & pencil
!
If you must overwrite all you database because splitting then this isnt just
because linked tables but shows big design mistakes in MS product.

Tõnu.

 

[Van T. Dinh]

If you ask Tony, he would split every Access database application he writes.
Tony even wrote the AutoFEUpdater utility to help distributing FE to each
user's work-station.

Remember that Tony wrote about his worst experience and there may be other
causes for the slow performance, e.g. slow incremental corruptions of his
database.

I never had an experience like his. On the contrary, my split applications
generally perform better due to reduced traffic that needs to go through the
networks. However, the advantages of being able to update the Front-End
independent of the users' processing as well as the less chance of the
database getting corrupted far outweights the performance consideration.

 

[Tony Toews]

If you ask Tony, he would split every Access database application he writes.

I do split every Access MDB.

Tony even wrote the AutoFEUpdater utility to help distributing FE to each
user's work-station.

Which is being upgraded on a regular basis.

Remember that Tony wrote about his worst experience

Yup. That was a form with 20 or 30 combo boxes, 12 tabs and 10
subforms.

and there may be other
causes for the slow performance, e.g. slow incremental corruptions of his
database.
Nope.

I never had an experience like his.

BTW this was back in A97 MDB on a system that was then already 60 or
80 Mb in size.

However, the advantages of being able to update the Front-End
independent of the users' processing as well as the less chance of the
database getting corrupted far outweights the performance consideration.

Agreed.

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

 

[Larry Linson]

Yup. That was a form with 20 or 30
combo boxes, 12 tabs and 10
subforms.

"Aiiiiiieeeeeee!," screamed the UI designer, when he saw it, and ran
headlong into the woods to seek solitude. "Aiiiiiieeeeeee!," screamed the
users when faced with that form, and ran headlong out the door to seek other
employment. "Come back," called the developer, "things could be worse." So
they came back, the developer didn't simplify the form, and, surely enough,
things did get worse.

Larry

 

[Tony Toews]

"Aiiiiiieeeeeee!," screamed the UI designer, when he saw it, and ran
headlong into the woods to seek solitude. "Aiiiiiieeeeeee!," screamed the
users when faced with that form, and ran headlong out the door to seek other
employment. "Come back," called the developer, "things could be worse." So
they came back, the developer didn't simplify the form, and, surely enough,
things did get worse.

<chuckle> <shrug> Quite simply there are that many child tables of
that particular item. This table is a piping assembly in a welding
shop. Each piping assembly, of which there, can be up to 15,000 per
job, is designed to fit on a truck for shipping to the refinery, power
plant, whatever. Multiple can fit on one truck but it can, at most,
be about 45' long and 8' wide and tall.

(Frequently these are combined into modules which are about 24' wide,
100' long and 16' feet tall. These are moved by special trucks and
RCMP escort. They basically shut down the highway in 20 or 30 mile
chunks to let these go by. Some jobs can have 100 or 200 of these.)

Each piping assembly has at least 10 or 12 dates associated with it.
And it can have batch issues, sub assemblies, holds, materials -
drawing, material actual, welds, invoices (yes, plural), assembly QC
tests (each weld can have it's own QC tests), labour, rev history,
stock check runs and finally outgoing transmittals.

Each of these items general have at least one layer of "drill down"
for more details. One or two "drill down" two or three more layers.

I was wrong. Only 9 combo boxes but 12 subforms with 13 tabs.

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