[AccessD] Clients and money

Arthur Fuller fuller.artful at gmail.com
Thu Jan 10 13:01:32 CST 2008


Yes they are. There's an Australian product called Unreplicator that does it
with a couple of clicks. But your point about a call center is well taken.

A.

On 1/10/08, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list