The answer if of course it depends!
We often see posts here that a application is too slow with one user. If the
application is too slow with one user..then what can one expect when they
try and run 10 users. That is now 10 times the requirements..
The other issue is how well is the database setup?
We OFTEN SEE people post here that with 5 users they are expericiening daily
corruptions and damage to the file. In fact, I walked into a company with
this kind of setup..and several time a week the system was blowing up.
We split the database (you are running a split database..right?)..and place
a mde on EACH PC. (so, a mde, not a mdb.a.nd they reside on EACH pc to the
back end file on the server). Now, the company has been running for 3+
years..and has NEVER experienced a SINGLE problem.
So, really, the original experience of the company was that 5 users was too
many..but with proper setup..5 users is nothing!
Further..how well are the forms designed?
How well does the application work with 5 users..and then when you jump to
10 users...how much a slow down to you notice?
So, much of the limits are going to depend on how well the system performs
now, and how stable the network and computers are.
The other thing is risk management. I mean, if you have two users in a
system..and it corrupts..then you loose only 2 man days. If you have 20
users..you loose a huge amount of work. With he cost of sql server so
low...then around 15 users is when I start to shift clients over. Note that
I don't shift them over because of performance problems (jet is fine with 15
users). I shift them to sql server because the risk of loosing that much
valuable work in a day means the cost of sql server can be justified. (so,
we keep the ms-access front end..and move the back end to sql server. With
this setup..you can run 100's of users with ms-access). Remember, ms-access
is not the database..but only a tool to CONNECT to the favourite database
engine of your choice. That choice can be a file share via JET, or sql
server. So, in effect your question should not be what the limits of
ms-access are (since there are companies that actually have 1000 users in
the same database running ms-access), but what are the limits of the JET
file share. It is rather nice to know with ms-access you can choose your
database engine. So, if you wish..you can use ms-access with the Oracle
database engine if you wish.
Be aware that the office cd for the last 3 versions has included both JET
and a desktop edition of sql server.
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.
Before Microsoft started "really" selling sql server, they rated JET could
handle easily 50 users. We have credible reports here of people
running 100 users. however, in those cases everything must be
"perfect".
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.
So, with my 5 users..I see no reason why I can't scale to 15 users with
such small tables in the 75,000 record range.
If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server.
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. So, if the application was not written with good
performance in mind..then you kind are 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). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.
Sql server is a robust and more scalable product then is JET. And, security,
backup and host of other reasons are why you switch. However, sql server
will NOT solve a performance problem with dealing with such small tables as
75k records
Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized. But, as mentioned, you
then wind up using those advantages to try and fix something that should not
have performance poorly in the first place! The size, and data sets you are
talking about should have run without problems BEFORE sql server. So, even
as you start to optimize your application..you are starting out with
something that is not very good.
I will how ever give a few tips...these apply when using ms-access as a file
share, or even odbc to sql server:
** 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 instantly 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 don 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 appcltions):
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!).
** Don't use quires that require more then one linked table
(this ONLY
applies to odbc to sql server...you CAN and are FREE to do this with
a mdb file share).
.. When you use
ODBC, one table could be on the corporate server, and the other ODBC might
be a FoxPro table link 3 computers from the left of you. As a result..JET
has a real difficult time joining these tables together..and JET can not
assume that the two tables are on the same box..and thus have the "box" join
the tables. Thus,while jet does it best..these types of joins can often be
real slow. The simple solution in these cases is to change the query to
view..and link to that. This is the least amount of work, and means the
joins occur on the server side. This also applies to combo boxes. Most
combos boxes has sql embedded in them. That sql has to be processed, and
then thrown to a linked odbc table. This is a bit sluggish. (a form can have
maybe one, or two combos..but after that ..it will start to load slow). So,
remove the sql from the combo box, build a view..and link the combo box
direct to that view (JUST USE the view name...the sort, and any sql need to
be in the view). The result is quite good combo box load performance. (and
again, not very much work. There are other approaches that can even speed
this up more..but we have to balanced the benefits VS. the amount of work
and coding. I don't think once should re-code all combo boxes to a call back
with a pass-through reocrdset..but that can be a solution also).
** Of course, if you do have sql with more then one table..then pass-though
is the best if using odbc. (again..this does NOT apply to a mdb JET file
share).
** 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,a nd
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.
