Gustav Brock
Gustav at cactus.dk
Thu Jan 10 10:01:58 CST 2008
Hi John At such clients that behave like buying a server is like buying a house, we often compare the costs with those for a car for one of the salesmen or with the monthly salary for an average employee. Quite often that ends the discussion; but for some, IT equipment is nothing more than an unwanted expenditure where you have to give up. As for the defragmentation, that scenario you describe wouldn't happen with a NetWare server ... /gustav >>> jwcolby at colbyconsulting.com 10-01-2008 16:24 >>> 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