If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
Free for All: help me speed up my Access Database
"Albert D. Kallal" wrote
For example, I asked a 92 year old grandmother if it was really stupid that bank machine would first download EVERYONE'S account number, and then YOU ENTER you account number (PIN) to use the teller machine. that 92 year old grandmother INSTANTLY could recognize that it is MUCH BETTER to first ask the user their PIN number, and then ONLY download that persons information into the teller machine. What a great example! -- Darryl Kerkeslager |
#32
|
|||
|
|||
Free for All: help me speed up my Access Database
I'll have to remember that example about the teller machine. Analogies can
be very helpful. "Albert D. Kallal" wrote in message ... As others have said, this subject is bit much for a single newsgroup post. however, MOST performance gains are accomplished with good designs. For example, I asked a 92 year old grandmother if it was really stupid that bank machine would first download EVERYONE'S account number, and then YOU ENTER you account number (PIN) to use the teller machine. that 92 year old grandmother INSTANTLY could recognize that it is MUCH BETTER to first ask the user their PIN number, and then ONLY download that persons information into the teller machine. So, if 92 year old grandmothers can tell me that is REALLY dumb to load up a bunch of information you don't need, then you going to have to show me that your forms don't load up unnecessary data. In other words, if any of your forms simply load up and are attached to a large table, then you have a bad design. Why would you attached a form to a large table, and then load the form? It is downright crazy to do this, and even 92 year old grandmothers can grasp this concept. yet, time and time again, I look at a ms-access applications, and sure enough...the user has forms attached directly to large tables, and has MADE NO EFFORT to restrict the records loaded. Terrible designs..... 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. 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 can't scale to 15 users with such small tables in the 75,000 record range. So, ok..small tiny tables of 75,000 reocrds, and 5 users, and sure, things should aboslore screem in terms of perfoamcne. 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 appcltions): http://www.members.shaw.ca/AlbertKal...rch/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,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 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!) -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
#33
|
|||
|
|||
Free for All: help me speed up my Access Database
"Anthony" wrote in message
... What does EVERYONE recommend I do to make my database run as close to lightning speed as possible? Give it Paula Radcliffe's legs. Keith. |
#34
|
|||
|
|||
Free for All: help me speed up my Access Database
Anthony wrote:
What does EVERYONE recommend I do to make my database run as close to lightning speed as possible? After all the other comments, about all I can add is that you might gain some improvement from occasionally compacting the msb files. Regardless of how effective any quick fix happens to be, good design is absolutely crucial. -- Marsh MVP [MS Access] |
#35
|
|||
|
|||
Free for All: help me speed up my Access Database
"Marshall Barton" wrote
from occasionally compacting the msb files. Marsh, you slipped up typing this... msb files are supposed to be _secret_... Microsoft Secret Base files! Now you've let the cat out of the bag. :-) Larry |
#36
|
|||
|
|||
Free for All: help me speed up my Access Database
Larry Linson wrote:
"Marshall Barton" wrote from occasionally compacting the msb files. Marsh, you slipped up typing this... msb files are supposed to be _secret_... Microsoft Secret Base files! Now you've let the cat out of the bag. :-) There's only a minor twitch betwixt them, most folks won't even notice the difference ;-) -- Marsh MVP [MS Access] |
#37
|
|||
|
|||
Free for All: help me speed up my Access Database
again, to everyone, dang! ( i decided not to cuss, so I wont say damn.)
oh, damn. I mean dang. "Marshall Barton" wrote in message ... Larry Linson wrote: "Marshall Barton" wrote from occasionally compacting the msb files. Marsh, you slipped up typing this... msb files are supposed to be _secret_... Microsoft Secret Base files! Now you've let the cat out of the bag. :-) There's only a minor twitch betwixt them, most folks won't even notice the difference ;-) -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|