Database Change Slows Server--A LOT--Help!

[Jim Zoetewey]

I'm a sysadmin not an ACCESS developer. More to the point, our developer
recently made a change to our database that slowed the system down
immensely. Ordinarily, I'd just suggest that he change it back, but it's
a little more complicated than that.

Here's the situation: One of the forms was unbearably slow. He changed
it from being a query composed of 3 joins to one table in the hope that
that table would be faster to work with. It isn't. The form is now
slower. On the bright side, some things related to the form now work better.

Not only is the form now slower, but the total CPU usage jumped from a
range of 5-20% to 20-60% usage. I say "total CPU" usage because we have
4 2.0 GHZ CPU's (Xeons) on the machine. Whereas before one was running a
little higher than the others (10% when the others were at 1% or
something), now one of them is always running flat out at 100%. Which
one that is changes randomly (from my viewpoint). The other CPU's range
from 0% to 60%in their usage.

The developer wondered if MS Access might be interacting strangely with
Terminal Server (which is what we use to make the db accessible over the
internet), but I doubt it. TS is made to work well with multiple processors.

Beyond that of course, the problems appeared the day he uploaded the
changes and we've been using Terminal Services for 2 years now.

My theories go like this:

Theory 1: The Jet engine isn't multi-threaded or somehow doesn't use
multiple processors efficiently.

That's the impression I got from the following link:
http://groups.google.com/group/micr...0+multithreaded&rnum=5&hl=en#4d0eeeb2b3a6402d

If so, we'd be better off switching to SQL Server or some other database.

Theory 2: The changes he made just made obvious the fact that the
database is just too big. It's 285 MB. One of the tables actually has
more than one million rows in it. When I look at the logs, there's a lot
of data transfer going on. That, in combination with Jet, might also
explain the problem.

I thought about that possibility after reading this discussion:
http://groups.google.com/group/micr...42ec0?tvc=1&q=multiprocessor#7748f38fc0542ec0

If that's true, I'm thinking we might double our RAM, but I'm also
thinking that it might be worth splitting the old data off into a new
database and accessing it only when absolutely necessary. That would
solve the size problem.

For what it's worth, we're using Acess 2000 (on Server 2003) and the
object model used is DAO.

Though I think that both of my theories are pretty good, neither really
explains why everything slowed down after his changes to my
satisfaction. "Theory 3" would be that something in his changes is
really screwed up, but he's got a lot more experience as a developer
than I do as a sysadmin and I don't feel comfortable suggesting that one...

Any suggestions as to what might be the problem and/or possible
solutions would be appreciated.

Jim Zoetewey

 

[Sylvain Lafontaine]

The fact that the CPU usage has rised might indicate a lock contention
problem caused by the fact that three tables have been reunited into a
single one: multiple queries are now battling to gain access at a row level
for this single table while they were isolated in the previous design.
However, as we don't have access to your design/schema of the database, it's
only a wild guess.

Another possibility would be an index problem. Also, make sure that you
don't have a virus problem.

Finally, using an Access application on a Quad Xeon with Terminal Server is
a Good/Bad design: it's an easy solution, quick to implement and will also
work over the Internet; however, it cannot replace a true Client/Server
application and will show its limitation when things starts to become really
big. However, with 285 Mgs, you should still be on the safe side but again,
this is only a guess because we don't have access to your design.

 

[Albert D.Kallal]

Is the database split?, do you deploy a mde to EACH user that logs on to the
Terminal server?

I would check the above...as the locking and contention might not be
data...but the actual application that is tripping over its self....

Disabling tack-autoname correct is a common fix also..but converting the
application to a mde would also fix this...

So, have you tried using a mde? (of course, the application has to be split
for that to work!!!).

 

[Jim Zoetewey]

Here we run into some of my limitations... My database development
experience used MySQL and Oracle. I've done some work with Access but I
don't do professional Access development.

Here's what I can tell you: The database seems to be split. I don't see
an mde in each user's folder. I do see an mdw.

From what I tell, there's a .bat script opens up a copy of the database
and a copy of the data for each user.

Hopefully that will tell you what you'd like to know...

Thanks,

Jim

 

[Albert D.Kallal]

Here we run into some of my limitations... My database development
experience used MySQL and Oracle. I've done some work with Access but I
don't do professional Access development.

Here's what I can tell you: The database seems to be split. I don't see an
mde in each user's folder. I do see an mdw.

The mdw is what we call a security file (this holds passwords, and users log
on. In fact, it don't make sense to give EACH user a workgroup file, since
if you change a password, or modify security settings, you would in theory
have to deploy a NEW workgroup file to each user. So, no..the workgroup file
is NOT the front end, or what we call the application.

From what I tell, there's a .bat script opens up a copy of the database
and a copy of the data for each user.

Hum, a copy of the application might make sense...but if each user gets a
complete new copy of the data...then how would the data be shared...since
all users would thus not see information edited, or added by other
users.....

So, likely each user receives a copy of the application part that is LINKED
to the data part.

So, that being the case, I would suggest to the developer to try a mde file
in this case, as that forces the application code to be compiled BEFORE the
appcation is deployed. (often, un-compiled code can cause the problem you
mention).

 

[Jim Zoetewey]

I'm fairly sure that it's not a virus problem at least. It's scanned
daily, downloads anti-virus updates regularly, and is up to the most
recent Server 2003 updates.

The rest though, I'll definitely pass along to the developer.
Personally, I'm biased toward a client/server solution, but bearing in
mind budget constraints (and my relative inexperience in Access
development), I'm not sure that rewriting that much of the application
will be workable. Who knows, though?

Jim