Max Wanadoo
max.wanadoo at gmail.com
Thu Jan 10 11:59:23 CST 2008
My answer is: What are happening to create the fragmentation? 800 = 1 Gig (give or take a byte or two) So it will be easy to fragment a BE that has lots of COPY TO (which is what essentially memo fields do). Are they using memo fields to write all sorts of 'comments' based on the telephone calls. If so, how's about having a table dedicated to memo fields, and link that to the main table and then compact/repair that table each time the form with the table is exited (or sommat like that - lateral thinking, etc)...OR.. Giving each user a FE for the memo fields in a tblMemos table and then copy that as an append to the BE....OR...putting comments into a TEXT file using WRITE? Keep on thinking, Max... Always more than one way to skin a problem.... Max -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Thursday, January 10, 2008 5:39 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Clients and money >Are they doing *other* things on the disk where the BE is stored. LOL. The BE (800 megs compacted) is on a 60 gig hard drive that had 6 gigs left. Now, what is YOUR answer to that question? ;-) John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo Sent: Thursday, January 10, 2008 12:17 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Clients and money Hmmm, interesting. I don't really understand how the BE will be fragmented unless there are lots of memo fields in there. Also, a reasonably large disk should not need defragging for ages. Mine has been going years and still does not need defragging. Are they doing *other* things on the disk where the BE is stored. If so, it might be worthwhile putting the BE on its own drive. With regard to the COSTS. Ask they question, "Which or your activities generate the most income?" When they start saying anything other than the DB, then ask them "Tell me, if the DB stopped completely, what would the impact be on your bottom line?". Then ask the question "Do you have a Disaster Recovery Plan that is regularly tested and checked?". Take it from there. 2p etc Max -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Thursday, January 10, 2008 3:24 PM 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com