Access Performance issues.

[steve12173]

I have an Access database that has about 40 users between two locations, say
location A & B. Locations are about 3000 miles apart and the server that
houses the database is in location A. The users in location B complain about
the database running very slow, sometime taking 3-5 minutes just to open a
form to input data and, queries take even longer. Users in location A have
no problems whatsoever. Any ideas to boost performance or is this a server
issue?

 

[Jeff Boyce]

You may want to look at some of Albert Kallal's analyses of Wide Area
Network (WAN) issues. Basically, Access is not suitable for WAN use, for
exactly the reasons you've described.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have an Access database that has about 40 users between two locations, say
location A & B. Locations are about 3000 miles apart and the server that
houses the database is in location A. The users in location B complain about
the database running very slow, sometime taking 3-5 minutes just to open a
form to input data and, queries take even longer. Users in location A have
no problems whatsoever. Any ideas to boost performance or is this a server
issue?

Access simply isn't suited to this kind of use.

Albert's website describing the issues about Access on a WAN is at
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

I'd really recommend having your Site B users use Windows Terminal Service or
Citrix Server or some such tool to actually log on to a computer on Site A to
use the database; or move the data to SQL/Server and use Access as a frontend
to that.

 

[Dale Fye]

Agree with Jeff on having Access as a backend on a WAN. I tried this and
performance sucked!

You might want to consider using SQL Server or SQL Server Express as the
backend. Then you can optimize your application to take advantage of letting
the server return as little information at a time as possible.

Do all of the users have the frontend on their local machines, linked to the
backend? If not, you definately need to do this.

Another option would be to consider implementing a terminal Server. But I
think Alberts' white paper
(http://members.shaw.ca/albertkallal/Wan/Wans.html) covers that as well.

 

[David W. Fenton]

I'd really recommend having your Site B users use Windows Terminal
Service or Citrix Server or some such tool to actually log on to a
computer on Site A to use the database; or move the data to
SQL/Server and use Access as a frontend to that.

Let me say that I would give 10 votes to the WTS/Citrix approach and
only 1 to the SQL Server approach. It's not that it's terribly
difficult to upsize, but for running over a WAN, you really need to
tune things well, and this means quite a bit of work. WTS/Citrix
means no work at all in terms of altering the existing application.
Once the sysadmins set up the server, it's seamless and easy. And
you don't have to worry about desktop installations of Access, and
getting the front end on to the workstations and so forth.

To me, it's night and day -- both work, but one is far, far easier
than the other.

 

[David W. Fenton]

Agree with Jeff on having Access as a backend on a WAN. I tried
this and performance sucked!

I don't quite see how anyone who understand how Access/Jet/ACE works
would ever think it could perform properly across a WAN, unless it's
one of those high-end ones with gigabit speeds (I've worked for a
wealthy NJ school district that has just such a WAN between their
schools, and they run an Access app across it with no problems at
all because the bandwidth is several times greater than a standard
100BaseT LAN).

 

[david]

Let me re-enforce the observation that switching to SQL Server
is not a magic bullet. To make SQL Server run well, you need to
re-write your application.

If the users don't need to see everything everywhere immediately,
the third way to make it run well is to use replication (either
SQL Server replication or Jet replication)

(david)

 

[Tony Toews [MVP]]

Let me re-enforce the observation that switching to SQL Server
is not a magic bullet. To make SQL Server run well, you need to
re-write your application.

That is wrong. Yes, you have to tweak things. Yes, creating views
helps a great deal with what were slow running things before. But you
do *NOT* have to re-write your application.

If the users don't need to see everything everywhere immediately,
the third way to make it run well is to use replication (either
SQL Server replication or Jet replication)

Replication has it's own set of problems err features.

Tony

 

[Gina Whipp]

David,

I've *upsized* several applications to SQL Server and never did it require
me to REwrite my application. Yes, I few tweaks here and there but nothing
like what you are suggesting.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Tom Wickerath]

That is wrong. Yes, you have to tweak things. Yes, creating views
helps a great deal with what were slow running things before. But you
do *NOT* have to re-write your application.

I've *upsized* several applications to SQL Server and never did it require
me to REwrite my application. Yes, I few tweaks here and there but nothing
like what you are suggesting.

Honestly, I think David is correct. Some re-writing will be required, more
so than just minor "tweeks", if you want to take advantage of the strengths
of SQL Server. I think creating stored procedures is more than a minor tweek.
What possible market would there have been for this book, if only very minor
tweeks are ever required?

Consider the following quote from page 6 of the book titled "Microsoft
Access Developer's Guide to SQL Server", written by Mary Chipman and Andy
Baron (SAMS Publishing):

<Begin Quote>
"Many people think that upsizing from the Jet database engine to SQL Server
is a universal panacea for whatever is ailing their Access databases. It's
not. In fact, just the opposite is usually true. If your Access application
is a dog, then most likely it will still be a dog after you upsize it to SQL
Server--perhaps an even bigger, uglier, shaggier dog! Even a well-designed
Jet database often won't run any faster after the tables are upsized to SQL
Server if you are using the same data access methods you used for your Access
database. In order to successfully convert your Access database, you have to
be clear about why it needs to be upsized, and you need to understand how to
take advantage of the strengths of SQL Server by reworking the data access
elements of your Access application."
</End Quote>


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Paul Shapiro]

There might be some improvement in this case even without any rewriting,
since the local workstation would now send sql statements to SQL Server and
receiving recordsets rather than directly reading and writing the Access
data file. But modest rewriting can probably make a huge improvement. I've
found the biggest impact is changing from the common Access form record
source of 'Select * From MyTable' to making the forms pull up one row at a
time. I use a fixed record source like 'Select * From MyTable Where 1=0',
and then have some control to let the user specify the row they want to work
with. A find-a-row combo box is very simple, and in the combo box
AfterUpdate routine change the record source to 'Select * From MyTable Where
id=' & Me.cboFind.Value. The only complicated part is that if you have more
than 10,000 rows in a table, the combo box may not list all those rows since
Access' default is limiting queries to returning 10,000 rows. For large
tables I use a find-a-row combo box that updates it's row source after the
user types a character or two.

 

[Banana]

Honestly, I think David is correct. Some re-writing will be required, more
so than just minor "tweeks", if you want to take advantage of the strengths
of SQL Server. I think creating stored procedures is more than a minor tweek.
What possible market would there have been for this book, if only very minor
tweeks are ever required?

FWIW, I think it's a matter of perspective. I do wholeheartedly agree
with Mary & Andy that using a RDBMS backend won't be the magic fixit
bullet, but it does not address the advantage that if you need to
upsize, you can quickly do it and tweak the app later. That in itself is
pretty significant. This can be done in a day, if not hours. The
transition can be made even easier if the application is well-designed
from the start.

The fact that such option exists is a luxury that some may deign to not
make use of and that's perfectly fine. But if they want it immediately,
it can be had, especially if they would prefer slow-performing
application over nothing at all. The optimizations can be then made
incrementally.

 

[Gina Whipp]

Tom,

SOME not a complete REwrite as David implied!

And I guess I should put things in prospective because since writing stored
procedures isn't a *do it or else* I don't consider that difficult or a
*complete* REwrite. A properly designed database will show little to no
significant changes when upsized. However, if you want to take advantage of
the SQL Server then yes, tweaks are needed, still not a complete REwrite.

Yep, I have to get a book (or two) hang out in some newsgroups. I never
meant to imply it was easy but it's still not a complete REwrite.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[steve12173]

The database is split with the tables in the backend and every user has there
own front end saved to their local machine. Now, I am not sure what type of
network my company uses. I would assume it's more along the lines of the
gigabit since we are a global financial firm. I might try the terminal
server for now and then look into going to SQL Server.

 

[Tom Wickerath]

Hi Gina,

David simply wrote "re-write" in order to "make SQL Server run well".
In my opinion, he neither said nor implied a "complete REwrite" was
necessary. If anything, the implication he left is that one would not need to
do any rewriting if they were satisfied with SQL Server running in an
inefficient manner.

Perhaps those who deal with small businesses, and upsize Access applications
to use SQL Server, can get away with inefficient use of the server. I can
tell you that that would not fly in the Fortune 100 company that I work at.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Gina Whipp]

Tom,

Are talking about the same David... there are two David's in this thread...

"Let me re-enforce the observation that switching to SQL Server
is not a magic bullet. To make SQL Server run well, you need to
re-write your application."

Also notes, I was not implying that you never have to rewrite but that it
was not necessary to rewrite. However, rewriting, IMHO, would be of the
entire application. And, yes, I will agree that in larger companies you MAY
have to rewrite some, if not all, of your queries but even that is not the
*application*.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Tony Toews [MVP]]

Honestly, I think David is correct. Some re-writing will be required, more
so than just minor "tweeks", if you want to take advantage of the strengths
of SQL Server.

To me the word rewrite all by itself implies throwing out the Access
app completely and totally redoing it in a different environment.

What possible market would there have been for this book, if only very minor
tweeks are ever required?

A hundred hours worth of tweaks on an app that took 2,000 hours to
write is not a rewrite.

Tony

 

[Gina Whipp]

Tony,

That was exactly my point... but I think Tom thought I was referring to
something David Fenton said which was not the case. It was in fact the
OTHER David that prompted my reply.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Jeff Boyce]

Tomayto, tomahto, potayto, potahto, ...let's throw the whole thing out...

Skippy!

 

[David W. Fenton]

That is wrong. Yes, you have to tweak things. Yes, creating
views helps a great deal with what were slow running things
before. But you do *NOT* have to re-write your application.

Indeed it is usually the case that very little needs to be rewritten
at all. You may find a few things bog down and need to be moved
server-side, nor you may want to take advantage of server-side
processing for other reasons (dynamic temp tables are one such
useful feature of most server databases), but you often have to
change very little at all to get a working app.

Replication has it's own set of problems err features.

Replication should *never* ever be implemented to resolve
performance issues. That's not what it's designed for. It is for
editing the same data in two places simultaneously while retaining
the ability to merge the edits without significant programming.

Any other use is probably a mistake.