Sooooooooo Slooooooow

[Andy_Khosravi]

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

[Albert D. Kallal]

I explain your problem, and give some good solutions here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

In a nutshell, using windows terminal services would solve your performance
issues here....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Tony Toews [MVP]]

An***********@bcbsmn.com wrote:

>I am having trouble with very sluggish response times for users
connecting over a WAN.

This is very standard behavior of Access. It's too "chatty" for WANs unless the WAN
connections are at 100 mpbs.

>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?

Or just plain Terminal Server will do although Citrix adds some benefits such as
beter printer handling. Yes, this is much, much better than Access remotely.

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

Yes, this would also help a lot.

Either solution would work. A lot of your decision will rest on what's convenient
for you to use. If using Terminal Server would require spending money but you
already have a SQL Server system that the IT department will let you use then that is
your answer.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Arno R]

"Tony Toews [MVP]" <tt****@telusplanet.netschreef in bericht news:q7********************************@4ax.com...

An***********@bcbsmn.com wrote:

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

Yes, this would also help a lot.

Either solution would work. A lot of your decision will rest on what's convenient
for you to use. If using Terminal Server would require spending money but you
already have a SQL Server system that the IT department will let you use then that is
your answer.

Tony
--

Hi Tony,
Are you sure about this ??

You are saying that *either* solution (TS/Citrix or SQL SERVER) would work ??
Is there a real benefit for the OP when using SQL Server as a backend over a slow Wan ??
Yes, the SQL Server processing will be quicker and such but the connection will still be Sooooo Sloooow I guess.
Or am I missing something here ??

Arno R

[Tony Toews [MVP]]

"Arno R" <ar****************@planet.nlwrote:

>You are saying that *either* solution (TS/Citrix or SQL SERVER) would work ??

Yes.

>Is there a real benefit for the OP when using SQL Server as a backend over a slow Wan ??
Yes, the SQL Server processing will be quicker and such but the connection will still be Sooooo Sloooow I guess.
Or am I missing something here ??

SQL Server is much more efficient sending data down the line than an Access BE.
Former Access MVP Tom Ellison once tried running an ADP over a 56 kpbs modem
connection. It was slow but it worked.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Andy_Khosravi]

Thanks for all the advice guys. I appreciate it.