jwcolby
jwcolby at colbyconsulting.com
Thu Jan 10 10:09:30 CST 2008
Arthur, I understand and think replication works well in some scenarios. I think a call center is not one of them. If the caller hangs up and decides he needs to call right back and say something, the odds are small that he would get the same person on the phone. Everyone needs access to all changes immediately. As for undoing the changes... the synchronization process adds entirely new GUI IDs, changes sequential autonumbers to random etc. Those changes are NOT trivial to undo. 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 Arthur Fuller Sent: Thursday, January 10, 2008 10:58 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Clients and money I've mentioned this before, but another approach that doesn't cost any money other than your time is to set up Access Replication on the network. The scenario goes approximately like this: 1. Create the Master Replica on your development machine. 2. Set up the Synchronizer either on the server or on any other always-available machine. 3. Create replicas for each machine that taps into the app. 4. Set up the synchronizer to synch the replicas with a suitable frequency. This eliminates about 90% of the network traffic. The entire database resides locally on each machine that needs it. The synchronizer kicks in at the specified interval, and copies data in both directions. That is, in a simplified case, where we have local machines A and B, and server S: At the beginning, A, B and S all have the same data. A adds some rows. B adds some rows. Synchronizer kicks in and exchanges data with A, then B. At this point, B receives A's new data, but A won't receive B's new data until the next synchronization. And so on. Whether this can work in your scenario depends on the granularity of synchronization. If A needs to see B's changes immediately, then this scenario is inappropriate. But if A can wait a few minutes to see B's data, then this approach delivers much better performance than the classic FE/BE scenario in which a whole whack of data is sent over the wire frequently (to populate subforms, dropdowns, etc.). The reason it's so much faster is because all that is transmitted is the new and changed data. Regardless of how fast your data-entry people are, how many rows can they enter every 15 minutes or so (depending on the interval you set)? Further, when viewed this way, how much data is one new row? Typically, due to foreign keys etc., a row is a collection of longs, a couple of text fields, a few dates, a currency value or three... total, maybe 1k per row. So the data exchange that occurs per synchronization (local to server and back) is a few KB at most. How long does that take? Answer: a second or two. This approach is simple to try, and if you don't like the results it's simple to undo. Arthur On 1/10/08, jwcolby <jwcolby at colbyconsulting.com> wrote: > > 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