Can we make me better, stronger, FASTER?

[FerryMary]

Sorry about that subject line,,,obvious child of the 70's here.

Anyway, I've split my db, I've replicated my bd I've made the bd mde and it
works so fast locally but on the server it's slower than ,,,,,I want. It's
not really horrible, but it does take about 8 to 12 seconds to load some
forms.

Ok,,I'm using Access 2003 on a Local Intranet Shared Drive. The
'Tools>Analyze>Performance' has no suggestions. Well, except to Relate my
Switchboard Table to other tables in the db. Is there a means to do this and
would it really be beneficial? All options on the switchboard route to
forms, reports or other swtichboards.

Would using a series of smaller front ends copied to local users help?
Currently the switchboard (which is startup option for field users) is
organized about like this.

Primary Screen (6 options)
(opt.1)Inspections has 4 options which includes a return to primary screen.
(opt.2)Preventive Maintenance has 6 options-including return to primary
screen.
(opt.3)Vessel Maintenance has 7 options-including return to pimary screen.
(opt.4)Vessels & Equipment 7 options-w/return to primary screen.
(opt.5)Employees & Facilities 4 options-w/return to primary screen.
(opt.6)Expiry Notices 5 options-w/return to primary screen.

Would smaller front ends with fewer linked tables and forms and queries
help? There would still be common tables required in all front
ends:employee, facilities, vessels etc

Any help would be most appreciated.
Mary

 

[Dirk Goldgar]

Sorry about that subject line,,,obvious child of the 70's here.

Anyway, I've split my db, I've replicated my bd I've made the bd mde
and it works so fast locally but on the server it's slower than
,,,,,I want. It's not really horrible, but it does take about 8 to
12 seconds to load some forms.

Ok,,I'm using Access 2003 on a Local Intranet Shared Drive. The
'Tools>Analyze>Performance' has no suggestions. Well, except to
Relate my Switchboard Table to other tables in the db. Is there a
means to do this and would it really be beneficial? All options on
the switchboard route to forms, reports or other swtichboards.

No, that's a dumb suggestion from the analyzer.

Would using a series of smaller front ends copied to local users help?

Probably not, assuming your current front-end is already on the users'
PCs.

It sounds like your problem is with network response time. One thing
you can do, if you haven't already, is maintain a persistent connection
to the back-end. One way to do this is to have a form always open that
is based on some back-end table -- the form may be hidden if that's
desirable.

Another thing to do is position the back-end as high up in the folder
hierarchy of the server as possible.

If only some forms are loading slowly, make sure you have the Name
AutoCorrect option turned off. That doesn't have anything to do with
the network/local issue, though.

For more performance tips, see Tony Toews' Access Performance FAQ:
http://www.granite.ab.ca/access/performancefaq.htm

 

[FerryMary]

:

Would using a series of smaller front ends copied to local users help?

Probably not, assuming your current front-end is already on the users'
PCs.

Actually I'm in the earliest stages of getting the program out to the other
users so the only front-end out there now is one I've placed at my own
terminal. Since I've not gotten to placing it at the other sites, would a
series of smaller front-ends make things better. Right now the db has 34
tables, 60 queries 58 forms, 0 macros (everything is code). Maybe pages
would be an improvement over forms but I've not gotten results I wanted using
pages.

Any suggestions would be most appreciated.
Mary

 

[John Vinson]

:


Actually I'm in the earliest stages of getting the program out to the other
users so the only front-end out there now is one I've placed at my own
terminal. Since I've not gotten to placing it at the other sites, would a
series of smaller front-ends make things better. Right now the db has 34
tables, 60 queries 58 forms, 0 macros (everything is code). Maybe pages
would be an improvement over forms but I've not gotten results I wanted using
pages.

Any suggestions would be most appreciated.
Mary

PMFJI - I'm not Dirk, but I've got a datapoint that might interest
you.

One of my clients is using a database with 106 forms, 131 reports,
several hundred queries... each user gets their own frontend.

But the worrisome thing in your post is the reference to "sites". A
frontend/backend database architecture works well over a FAST, STABLE
LOCAL AREA NETWORK. It will *not* work over the Internet, an
unreliable network (wireless networks are not reliable in this
context), or especially over the internet. A smaller frontend won't be
of ANY help.

This client is in fact working over a WAN, and for that matter I'm
connecting to the database from a thousand miles away; but we're using
Citrix Terminal Server to do so. The frontend and the backend are both
safely on the same fast, stable LAN, and the remote users are using
the terminal server to connect to it.

John W. Vinson[MVP]

 

[Dirk Goldgar]

:


Actually I'm in the earliest stages of getting the program out to the
other users so the only front-end out there now is one I've placed at
my own terminal. Since I've not gotten to placing it at the other
sites, would a series of smaller front-ends make things better.
Right now the db has 34 tables, 60 queries 58 forms, 0 macros
(everything is code). Maybe pages would be an improvement over forms
but I've not gotten results I wanted using pages.

Any suggestions would be most appreciated.

Your current front-end doesn't sound like it's very big at all, and the
size of the front-end is not going to have any effect on any problem
that is due to network latency. You're saying the database is split,
and the application works fine when both the front-end and back-end are
on your PC, but not when the front-end is on your PC and the back-end is
on a server PC -- right? Assuming this is the case, there's no reason
to think the size of the front-end has anything to do with the problem.

Did you set everything up as suggested in Tony Toews' Access Performance
FAQ, to which I posted the link before? Did you do the bit where you
maintain a persistent connection to the back-end?

Does it take a long time to access other, non-Access, files on the
server?

One further thing: make sure that all your tables have indexes on the
fields that are used for joins, query criteria, and sorting. That will
reduce the amount of data that needs to be brought across the network.
Note that fields that are used in defined relationships between tables
don't have to be indexed again; the relationship will have created
indexes on those fields, though it may be hidden from you.

 

[FerryMary]

:

size of the front-end is not going to have any effect on any problem
that is due to network latency. You're saying the database is split,
and the application works fine when both the front-end and back-end are
on your PC, but not when the front-end is on your PC and the back-end is
on a server PC -- right? Assuming this is the case, there's no reason
to think the size of the front-end has anything to do with the problem.

The backend is on my local drive which is shared between my pc and one other
(at least one more to be added later, but possibly up to 5). The share is
over a Local Intranet.

Did you set everything up as suggested in Tony Toews' Access Performance
FAQ, to which I posted the link before? Did you do the bit where you
maintain a persistent connection to the back-end?

I did. It increased performance a bit, but not as much of an improvement as
I'd like to see.

Does it take a long time to access other, non-Access, files on the
server?

Not as long as this one, usually only a 5 seconds or so for a spreadsheet,
less for Word.

One further thing: make sure that all your tables have indexes on the
fields that are used for joins, query criteria, and sorting. That will
reduce the amount of data that needs to be brought across the network.
Note that fields that are used in defined relationships between tables
don't have to be indexed again; the relationship will have created
indexes on those fields, though it may be hidden from you.

I read in another post that replication is not suggested if you're using
LAN,,,but I was wondering if the lethargy in my Intranet could be sidestepped
if I used Replication. If so could someone point me in a direction for some
step by steps on setting up the Replication. I didn't do it right last time.
Again, it worked like a charm on my computer but failed from other stations.

--I wish I had replied when this thread was fresh, but my son split his
humerus and I've been on 'youngun' watch'.

Thank You for any help.
Mary