[AccessD] Clients and money

  This once again proves the point I've been making to my clients over the
past 15 years; yes, you do need to defrag and defrag on a regular basis.
Microsoft has always suggested that NTFS disks don't need defragging.
That's absolute baloney.  I've had too many cases where I've walked in,
found severe fragmentation on a server and gotten a 10 - 20% boost in
performance just by defragging (especially with older hardware). You want
your CPU and disk spending time processing stuff, not wasting their time
with wasted overhead.

  I thing I like to do is size disks so that "at capacity", they are approx
60% full.  This keeps fragmentation at bay somewhat and defrags will run
quicker (more maneuvering room to work with).

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, January 10, 2008 10:24 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Clients and money

I had an interesting "problem" with the database at a client this week,
where the database response time went to hell very suddenly.  This is the
disability insurance call center software which many users spend their day
taking calls, opening a very complex form to view and edit claim info for
the person they are talking to.

On Friday of last week, the time to open this very complex form went from 4
or 5 seconds to 20 or 30 seconds.  There are old machines where the form
went from 8-10 seconds to 60 or 80 seconds.

Long ago I had a similar problem in this database and I had developed a
class (of course) and a table to log how long the form takes to open, the
time of day, the workstation trying to open the form, how many users are in
the database etc.  So every time this main form opens it logs all this
information in a table.  I then developed a set of queries (long ago) to
show me averages by day / workstation etc.

So... times to open have gone through the roof, it happened on a specific
day last week, and they have remained there.  Of course the client is
calling me with "did you do anything..." kinds of questions.  I had not, and
could tell that by my billing records where I record what I do on what day
for who.

Long story short, after a few days of poking around, the user rebooting the
server, compacting / repair the BE, decompile / compact / repair the FE
etc.... I noticed that the disk volume holding the database was down to
about 15% remaining space (on a 60 gig drive).  I told the client to look at
this and he quickly went in and deleted all kinds of old trash and got us up
to about 50% remaining.  this did make some small impact, but the database
was still abysmally slow.  Last night I went in, rebooted the server,
defragged the C: drive and the D: drive (where the database resides) and
voila, this morning the times are back to normal.

It turns out that the real problem was two fold.  First it was horribly
fragmented, but additionally when the client did a compact repair, something
went wrong and Access created two of those "DB1.MDB" things that it creates
when a compact fails.  The database is about 800 megs compacted, and the
drive was so full that suddenly, with two additional 800 meg files in there,
there was just "no room left".  When I say "no room left", there was
actually about 6 gigs left even after the DB1 copies were created, but the
remaining space was tiny little fragments of space all over the disk.  Which
meant that the database itself was already horribly fragmented and it
couldn't find any room to put new pieces as needed.

So, just an FYI, DEFRAG THE DISK!!!  And do not allow the disk to get too
low on space.

Now to the money thing.  I use a 4 gig RAM drive on one of my servers here
at my office to hold a set of files for the address validation software that
one of my servers runs.  It speeds up that process by 50%, allowing me to
move from about 2.5 million addresses per hour processed up to about 4.5
million.  A startling and impressive increase in speed.  So I advised this
same client (a year ago) to look at doing this for this call center
database.  The main database file is about 800 megs.  In looking over the
"time to open" records this last week I noticed that various employees are
opening claim records using this complex form every 20 to 60 seconds or so
(950 records yesterday).  That is a LOT of data being pulled (and I use JIT
subforms to hold it down).  So I again advised the client to try a couple of
these 4 gig boards in Raid 0 to put just the BE files on, in order to speed
up the database.  I am convinced with this number of transactions per hour,
with the size of the database, and with the way that a RAM disk works, that
a RAM disk could boost this specific application's usability.

The board costs about $150 and another $200 for 4 gigs of memory to put on
it.  $400 shipped to their door for one, $800 for two.  The client just told
me that "due to costs and ... " they will "consider this in the future".  We
are talking about $800 expense (plus implementation) for a company of 60
employees where 30 or so users are in the database all day every day, and
they are deferring it to later.

Clients really are cost conscious, and the smaller the client, the more that
is so IMHO.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com