Your database server does not run slower or faster when MS access, vb.net,
or usign c++.
Of course the problem is that the Internet is 100 times slower then your
typical office LAN.
What this means is that you just simply have to take very careful
consideration in terms of how much data you load into your forms.
I speak a little bit about using MS access over the Internet here, I'll give
you some ideas of the performance issues you have to deal with:
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html
some 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.
Here is a some screen shots and example of what I mean by "asking" the
user what they need:
http://www.members.shaw.ca/AlbertKallal/Search/index.html
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 appcltions):
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!).
** Don't use quires that require more then one linked table
(this ONLY applies to odbc to sql server/mysql etc,...you CAN and are FREE
to do this with a mdb JET file share..and also with ADP projects to sql
server).
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, 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.
** 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 100's 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 reducfing the bandwith
(amount of data) goes hand in hand. So, better applications
treat the usres well..and run faster! (this is good news!)
