I am having trouble with very sluggish response times for users
connecting over a WAN. Local users using my database are able to run
the most common queries in 2-8 seconds (maybe up to 30 seconds during
peek usage.). Users in upstate Minnesota here can easily take 20-60
minutes to do the same exact operation.
The application is fair sized (157 forms, 76 related tables). I've
optimized the queries/form operations as much as I know how, and in
some cases increased performance many times over what it was doing
before, but it isn't enough. I've worked with Access itself for
several years now; I wouldn't call myself a beginner as far as this
tool is concerned. However, when it comes to scaling Access up or
augmenting it with other programs I'm a bit in the dark.
Ultimately, our IT department will be purchasing a 3rd party program
to take care of what this Access DB does, but that will take at least
12 months and is likely to be held up by budget constraints (the cost
is enormous!). Until then, my management would really like me to get
the response times for our upstate users brought down to at least a
useable level, and I'd also love to stop getting hate mail from them
=].
I'm wondering if any of you more experienced people have any
suggestions on what I should be recommending or doing for a short term
solution? I realize the question is a bit ambiguous, but I don't think
it's realistic to try and dump every detail of the application out
here on the forums. I'll give you what details I can:
-The database is split. The backend resides on a file server at our
main building and is approximately 60MB in size with 200,000 or so
total records (spread over many tables)
-The front end is stored individually on each client machine
-The FE is decompiled, recompiled, compact/repaired, and then
redistributed as an .mde every time I make any changes
-The BE is compacted/repaired at least once a week
-The tables are pretty well normalized with few exceptions
-The speed of the overall application is great for all local users
with up to about 10 people in it. Things slow down a bit from here on
out, but otherwise still very usable
-I don't think I can blame the WAN connection, Lotus Notes and
Mainframe applications work great for upstate users
-We are using Access 03 in a Win XP (client) environment over a Novell
network.
-There are usually a maximum of 30 or so people logged into the
application at any one given time. There are over 700 'registered'
users though.
I've considered replication, but from what I've read that is a rats
nest. It would also seem that it is a solution that isn't used too
often these days as there are better alternatives.
I've been reading in other posts about people using Citrix, and
another employee here at work mentioned something about it. How, When,
and Where should this be used? From the little I've gleaned, it seems
that this is used to connect remotely to the server. Any performance
benefits here?
Would there be any real benefit to upgrading the backend to SQL Server
for a database this small? In the posts I've read, you need either a
lot of concurrent users or 1 Million+ records to make it worthwhile.
Thanks in advance for any advice
connecting over a WAN. Local users using my database are able to run
the most common queries in 2-8 seconds (maybe up to 30 seconds during
peek usage.). Users in upstate Minnesota here can easily take 20-60
minutes to do the same exact operation.
The application is fair sized (157 forms, 76 related tables). I've
optimized the queries/form operations as much as I know how, and in
some cases increased performance many times over what it was doing
before, but it isn't enough. I've worked with Access itself for
several years now; I wouldn't call myself a beginner as far as this
tool is concerned. However, when it comes to scaling Access up or
augmenting it with other programs I'm a bit in the dark.
Ultimately, our IT department will be purchasing a 3rd party program
to take care of what this Access DB does, but that will take at least
12 months and is likely to be held up by budget constraints (the cost
is enormous!). Until then, my management would really like me to get
the response times for our upstate users brought down to at least a
useable level, and I'd also love to stop getting hate mail from them
=].
I'm wondering if any of you more experienced people have any
suggestions on what I should be recommending or doing for a short term
solution? I realize the question is a bit ambiguous, but I don't think
it's realistic to try and dump every detail of the application out
here on the forums. I'll give you what details I can:
-The database is split. The backend resides on a file server at our
main building and is approximately 60MB in size with 200,000 or so
total records (spread over many tables)
-The front end is stored individually on each client machine
-The FE is decompiled, recompiled, compact/repaired, and then
redistributed as an .mde every time I make any changes
-The BE is compacted/repaired at least once a week
-The tables are pretty well normalized with few exceptions
-The speed of the overall application is great for all local users
with up to about 10 people in it. Things slow down a bit from here on
out, but otherwise still very usable
-I don't think I can blame the WAN connection, Lotus Notes and
Mainframe applications work great for upstate users
-We are using Access 03 in a Win XP (client) environment over a Novell
network.
-There are usually a maximum of 30 or so people logged into the
application at any one given time. There are over 700 'registered'
users though.
I've considered replication, but from what I've read that is a rats
nest. It would also seem that it is a solution that isn't used too
often these days as there are better alternatives.
I've been reading in other posts about people using Citrix, and
another employee here at work mentioned something about it. How, When,
and Where should this be used? From the little I've gleaned, it seems
that this is used to connect remotely to the server. Any performance
benefits here?
Would there be any real benefit to upgrading the backend to SQL Server
for a database this small? In the posts I've read, you need either a
lot of concurrent users or 1 Million+ records to make it worthwhile.
Thanks in advance for any advice
Comment