SQL server is indeed a high performance system, and also a system that can
scale to many many users.
If you write your application in c++, or VB or in your case with ms-access,
in GENERAL the performance of all of these tools will BE THE SAME.
In other words...sql server is rather nice, and is a standard system used in
the IT industry.
However, sql server will NOT solve your performance issues without efforts
on your part. And, it turns out that MOST of those same efforts also make
your non sql server applications run better.
In fact, we see many posts that mention moving the back end data
to sql server actually slowed things down.
A good question was how well does your application run now?
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?
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?
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), and no sql server, 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 cant 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 make sql server a good choice.
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.
I will give a few tips...these apply when using ms-access as a file
share (without a server), 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 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.members.shaw.ca/AlbertKallal/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!).
** USE CAUTION with quires that require more then one linked table
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
always 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.
I don't think it needs pointing out that if the query in question already
runs
well, then the above can be ignored, but just keep in mind that local
queries
with more then one table based on links to sql server can often run slow.
So,
just be aware of the above.
This view trick also applies well 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 can be a 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. Remember JUST USE the view name
for
the combo box. That means if you have 3 columns, then the view on the sql
server
side must be only 3 columns. Also, the sort, and any sql need to be in the
view
to restrict records are now in the view. The result is your combo box load
performance is simply more snappy. Again, this is not very much work. There
are
other approaches that can even speed this up more such as coding up a
recordset
and writing code to load up the combo box, but that approach IS TOO
expensive.
We have to balance the benefits VS. the amount of work and coding. I don't
think
one should re-code all combo boxes to a call back or use a pass-through
reocrdset,
but these approaches can be an solution also. In fact, if your for only has
one
or two combo boxes, you likely don't have to do any of the above. If your
form
has 5-6 combo boxes, then the view trick should be enough. If you have 10
combo
boxes, then you might go down the custom combo loading code. So, this is not
an all or nothing here..you ONLY do as much work as you need to get
acceptable
performance.
If you are using a ADP access project, the above points about the joins
with more then one table does NOT apply..since all queries execute
on the sql server side.
** Of course, if you do have sql with more then one table..then pass-though
is the best if using odbc.
** 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 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 as good
anyway when done right.
** Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100s of entries). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.
After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducing the bandwidth
(amount of data) goes hand in hand. So, better applications
treat the users well..and run faster! (this is good news!)
so, #1 tip is to reduce the data that you transfer into an form.
This reducing tip works very well for non sql server based applications
also.
