Well, some more details are needed of your setup.
Are you talking about a standard split mdb here? (this usually means a mde
on each pc..and the data mdb on the server).
I would think that your server would have a spike if a user simply copied a
power point file to their local pc..would it not?
In other words...I doubt the load the server is much different then when a
user grabs any amount of data from the server. Does not sound
unusually. Try copying a simple file from the server..and see what
happens.
Of course, your application is VERY careful not to read more records then it
needs for a given situation right?
A few things:
having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), then the performance of
that system should really have screamed.
I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don't think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 75k records range.
My point here is that technology will NOT solve performance problems.
However, good designs that make careful use of limited bandwidth resources
is the key here. If the application was not written with good
performance in mind..then you might be stuck with a poor design!
I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record).
Here is a few tips:
** You are running a split mdb..right?
** You have gone through the fantastic check list of setup performance
things to check here:
http://www.granite.ab.ca/access/performancefaq.htm
** What kind of network are you using. If you are trying to use a WAN in
place of a LAN..then you REALLY need to read the following:
http://www.attcanada.net/~kallal.msn/Wan/Wans.html
Here is a few more tips:
** Ask the user what they need before you load a form!
The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server applications):
http://www.attcanada.net/~kallal.msn/Search/index.html
My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).
** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice, and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right.
** After how many users does the system starts to slow down
after. I mean, does the system run ok with 1 users, or 2..and then slow down
after 15 users for example? As mentioned, if performance is slow with one
user...what are you going to do when you have 10 users (that is 10 times the
amount of resources).
** It is always possible that the amount of table size and data your are
working
with has exceeded the limits of JET and a file share. Perhaps it is time for
sql
server (this means that you keep your front end in ms-access, but move the
data part to a true client to server based system). In fact, there is a free
one
on the office cd.
