Access performance under stress

[Guest]

I built a Database application that used Access as its DB backend for a
library. Initially, the application was meant to support only routine tasks
of the library staff, and was not available to the public. However, it has
been so successful that the library has decided to allow people to use it for
searching the library books.

Suddenly, there are more than 8 clients accessing the server which I am told
can rise further. Now with the file-serving nature of Access, is it possible
for it to support so many clients? The application broadly has data access
queries, & updation transactions, where a tranasaction on an average has 3-4
updation/insertion queries. How much transaction throughput should I expect,
where the library roughly has 30000 books in its main table.

Moreover, I have a reservation. With the file-serving nature of Access, is
it always possible for it to ensure ACID properties of transactions with
multiple clients updating the DB simultaneously. I mean, what if it has
served the DB file to 2 clients, which update the same record. Obviously,
only one updation has to get through. How would it resolve the outcome?

 

[Arvin Meyer [MVP]]

If all they are doing is searching, you can have up to 255 (according to the
specifications) I have had more than 53 instances going at once with the
users actively editing. 30K records are a pittance My databases are tested
with 1 million records and typically there are as many as 100K records in
the main table.

 

[Guest]

When you say instances, you mean database connections, that can be local or
over the network. Moreover, thay can be using the Access interface itself,
from a .NET application or any other app?. Right???

And what about the ACID properties?

 

[Arvin Meyer [MVP]]

When you say instances, you mean database connections, that can be local
or
over the network. Moreover, thay can be using the Access interface itself,
from a .NET application or any other app?. Right???

And what about the ACID properties?

File Server databases without transaction logs do not completely conform to
ACID properties. That said, in 15 years I have never had a problem with
incomplete transactions except once when a broken connection was made during
a replication. 20 records were corrupted of which 12 records were recovered
and 8 more had to be re-entered.

The 53 instances (actually more, but I didn't count how many users had
multiple front ends connected) consisted of 39 LAN users with Access
front-ends, 8 asp users over the Internet, and 6 Terminal Server users using
Access front-ends. I have never used any .NET front-ends with a JET backend,
although others have.

The number of users just doing searching can probably be as many as a 100. I
wouldn't try using the 255 limit. And of course the whole thing depends upon
the quality and strength of the server and the design of the database.
Applications which do not properly use indexes in their searching can expect
poor performance with even a few users.

 

[Guest]

The library server (I although doubt that you would consider it as a
server!!) is a PIV 3.0 GHz machine with 1GB of DDR2 Ram, a 80 GB PATA disk (I
am not sure of its RPM).

Well, I would not be looking at supporting more than 15 clients at any point
of time, although typically I do not expect more than 5-6 connections
simultaneously. Almost all of these would be exclusively through .NET 2.0
application using OLEDB data provider classes. (Although switching to ODBC
would not be too difficult, if you suggest that to be more fast).

What should be the minimum transaction throughput I can expect, given that
most transactions are a mix of 3-4 updation/insertion/deletion queries?
Till now, there were barely 3 systems on the network, which has now risen to
5 & will rise to 9 in coming days. The staff has already reported noticeable
decline in performance. Can you point me to a resource advicing maximising
Access transaction throughput? 8-10 users seem normal by the figures you have
provided, so transaction throughput is what I am aiming at.

Also, all tables have indexes on commonly accessed fields, but I dont
believe I have to do anything special to to make my application use those
indexes.
Am I correct in my belief?

I know they are too many questions, but I would be really grateful if
someone can answer those, or atleast direct me to appropriate resources.

 

[David W. Fenton]

Well, I would not be looking at supporting more than 15 clients at
any point of time, although typically I do not expect more than
5-6 connections simultaneously. Almost all of these would be
exclusively through .NET 2.0 application using OLEDB data provider
classes.

It's rather important when you're posting in an ACCESS newsgroup
that you indicate that you're not even USING Access -- you're using
Jet. The answers to your questions will be more illuminating if your
readers don't cast their answers in terms that will be meaningless
to you (as is the case with my most recent answer).