OK, we have this accounting application which we just started using which runs in a client/server setup. The database is very small at the moment (100mb) and there are only 2 users total. We have the database sitting on the data partition on the our 1yr old 2.8ghz Xeon file server's RAID5 volume (Intel 6 channel SATA RAID card) and this is where the server app is running as well.
Even when just one user is connected, they complain of an annoying level of lag when entering data in the fields. I observed this myself. We tried moving the server app and database over to the hard disk of another workstation in the office and it behaved similarily.
We contacted the vendor and get this, they said according to their testing the program runs much better when you store the database on a USB2.0 external hard drive. They even put the following blurb in the product documentation...
I read this and reacted extremly doubtfully. A high end Intel SATA RAID card is a 'very slow disk interface' ? come on! we're using the same hardware on other servers to run far more utilized SQL server instances.
Well, I tried doing as they suggested and it actually WORKED
What I can't understand is why... any ideas?
Running the database off an external USB drive is far from ideal for redundancy purposes.
Even when just one user is connected, they complain of an annoying level of lag when entering data in the fields. I observed this myself. We tried moving the server app and database over to the hard disk of another workstation in the office and it behaved similarily.
We contacted the vendor and get this, they said according to their testing the program runs much better when you store the database on a USB2.0 external hard drive. They even put the following blurb in the product documentation...
Why don't other software products have similar performance problems?
Most often you will not experience performance problems with other software applications such as word
processors, spreadsheets, graphics programs, and so on. These products typically access the disk solely to load
and save files and do not access the disk while carrying out general operations. Even when they do access the
disk, the disk is accessed in a sequential fashion, not with so-called random access.
The software applications that are most likely to expose disk performance problems are database applications
that work on files intensively, using random access.
xxxxxxx is such a disk-intensive database-oriented application. It accesses the disk in a random fashion as you
perform your accounting activities. This high volume of random access is what encounters and exposes potential
performance deficiencies of the disk.
How can external drives perform better than internal drives?
The short answer is that they shouldn't.
But we have discovered that about 80 percent of current computers have a very slow hard drive interface (with
standard Windows drivers for IDE ATA controller). The hard drive may be very fast according to published
specifications, but in reality, the actual data transfer between the drive and Windows is very inefficient, especially
when accessed randomly. When an external drive is plugged into the same machine, xxxxxxx performs up to
10 times faster than with the internal drive. Windows appears to have better drive communication through USB
than through ATA or SATA!
The usual underlying cause is a mis-match of software drivers with drive hardware. Often the computer
manufacturer is unaware of the problem and does not install more up to date or specifically tuned software drivers
for the computer hardware being configured. Often a computer with poor disk performance can be improved by
installing the "right" driver software. However, because most users never run disk-intensive software the problem
usually doesn't surface and the manufacturer never becomes aware of it.
In any case, such a fix is technically complicated, hard to do, and fraught with uncertainty. That is why we
recommend using an external drive. This is sure to work and is very simple to implement.
I read this and reacted extremly doubtfully. A high end Intel SATA RAID card is a 'very slow disk interface' ? come on! we're using the same hardware on other servers to run far more utilized SQL server instances.
Well, I tried doing as they suggested and it actually WORKED
What I can't understand is why... any ideas?
Running the database off an external USB drive is far from ideal for redundancy purposes.