The Need for Speed, Part One: The
I'm writing this on January 4, 2012, the night after the Iowa
Caucuses - the first major political event of the 2012 Presidential
Campaign. I am currently waiting to see an interview with Newt
Gingrich at St. Anselm College in Nashua, NH. As it happens, a
reporter from the New York Times decided to photograph some of the
folks in the audience and, lo and behold, she took a picture of me
writing this as I was waiting! That's me on the far right typing
For those of you who are not tuned in to presidential politics,
New Hampshire has the distinction of holding the first presidential
primary. Although Iowa gets a lot of attention because of the first
caucuses, New Hampshire attracts the eyes of the nation for a period
of several months culminating this year on January 9 when the
primary is held.
Newt is due to speak at 7:00 p.m. and it's now 6:55 p.m. If this
event goes like most political rallies, speeches, and town hall
meetings, this event will not begin on time. As a matter of fact, it
is unlikely that it will start even close to 7:00 PM. The start
time, most likely, will be closer to 8:00 p.m.
These events typically end up resulting in a lot of people
waiting and waiting, frustration slowly growing, until the candidate
finally appears, at which point the supporters go wild, and the
undecided voters get ready to be swayed (or not depending on their
So what, you may ask, has any of this to with databases?
Unfortunately, I've seen too many databases make their users wait
while some cryptic process takes place in the background. This month
begins a series of articles that will help you reduce waiting and
avoid staring at an hourglass by providing techniques to speed
(P.S. The speech actually began at 7:10 p.m. My hat is off to the
Gingrich team for being so prompt. I'm not quite sure how they did
it, but I was impressed.)
|A Simple Step -
Disable Name Autocorrect|
One of the biggest contributors to overall wait time is the
name autocorrect feature. Most folks aren't even aware of this
little tool that does its work silently in the background. Its
job is to watch what you do and any time you change the name
of a table or query, this powerful little routine ripples the
name change throughout the database to help you not to
inadvertently break things. Although this sounds like a good
idea, it pulls heavily on the processing power of the
computer. By default, this feature is on. To turn it off, take
the following steps:
- In 2010, click the File tab. In 2007, click the magic
circle in the upper left-hand corner. In 2003 and earlier
versions, click on the Tools menu and choose options. From
this point on the directions will differ slightly from
version to version. These steps apply to 2010.
- On the left, click Options. The Access Options dialog
- In the left pane of the Access Options dialog box, click
- Under Name AutoCorrect Options, clear all the check
boxes under the name AutoCorrect options section.
This change will cost you if you like to change the names
of objects after you've created them; however, if you use a
consistent naming system it would be very unusual for you to
want to change an object's name.
Complicated Forms - Avoid Information Overkill|
If you have a form that is slow to load, check to see if it
is needlessly complicated. If so, simplify. If your form
contains sub-forms, which in turn contain other sub-forms,
then you have set yourself up for poor performance.
If you have this problem, rethink how you are presenting
data to your users. Rather than try to show them everything
they might want to possibly know about a particular record,
replace all the non-critical data with buttons that will bring
up more detail as they need it.
If you have a form with loads of tabs, then you almost
certainly are asking for trouble. Typically, when a user is
looking at a record s/he is not interested in everything about
the record. Show only the most critical information and fit it
on a single form with no tabs.
By doing this, you can gain a performance pickup. If you
take the various tabs in your forms and convert them into
buttons you will end up with a sleeker, faster design.
|Death by 1000
Frequently, new database designers are enthralled with the
fact that if there are thousands of records in the database,
they can make every single one of those records available on a
single form. Although this would seem to be a good technique,
it really isn't.
Think about it. do you or your user want to see a thousand
records? Most likely you and the user are interested in a
single record and that's what you should retrieve from the
database. If you are working across a network and your form
brings all the records in a table to the form then you have
dragged thousands of records across the network that will do
nothing but take up bandwidth and slow things down. You're
better off providing a search function that allows the user to
determine which record they are interested in and then, after
they make their selection, bring only that single record to
This is the first of several articles that will help you
reduce your wait time while the database does its thing.
Although designing a speedy database, particularly one with
complicated data, can be difficult, you will find that your
application will be easier to use if you spend the time and
effort to make things faster.
Just as waiting for a campaign speech makes people
irritable, staring at an hourglass turns users into unhappy
campers. Take the time to tune your database for speed. If you
do it right, your users won't even know that the database
contains an hourglass.
Tip of the Month
- If Your Users Have to Wait, Give Them Something to Look
Even streamlined databases occasionally may have a process
that is so computationally intense or manipulates so many
records that a wait is inevitable.
By all means, avoid the default hourglass. That does
nothing to indicate any details about what is going on behind
the scenes. It just says something is happening, and that the
user should wait. This is not only discourteous, but also
annoying. The user doesn't know if the computer has gone into
an endless loop, or is broken. They don't know if they're
being asked to wait 10 seconds, 10 minutes, or an hour. If the
delay is going to be more than 10 or 15 seconds, then you can
make it seem shorter by a very simple technique: Give them
something to look at and let them know what is going on behind
The easiest thing to do is to include a progress bar that
advances as your process moves forward. Provide periodic
messages to the users about what's going on behind the scenes
as things progress. Telling them how many records are being
processed and how far along the process is will make the wait