[AccessD] Number of users in the DB

JWColby jwcolby at colbyconsulting.com
Tue Feb 13 12:29:28 CST 2007


In fact this particular client has virtually no reports.  I created this
capability for one reason only, to allow me to measure the impact on the
number of users on the response time of the database.  As often happens with
a database, once I got in there I was able to see correlations between the
workstation and the response times.  This may allow troubleshooting (seeing)
slow hardware other than the workstation, for example slow legs of a network
etc.  That would require inside knowledge however.  My client is using the
report to just be aware of who has the slowest access to the system.

In fact, the whole system runs off of a pair of classes and a table to store
the data.  If there is sufficient interest I will post the entire system to
the dba website for download.

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 Bobby Heid
Sent: Tuesday, February 13, 2007 1:04 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Number of users in the DB

John,

I would also try to measure the total amount of traffic on the network.  If
many people are using the network (Access program, email, downloads, etc.),
it could be that your network is being saturated.  Like I said in my earlier
mail about this issue, I had a report that had to transfer over 90MB of data
for one report.  Now, it was a very large report from a large database, but
you can see that it would not take too many reports like that being run at
one time to bring the network to a crawl.

Bobby 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Tuesday, February 13, 2007 12:41 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Number of users in the DB

There was a thread last week regarding the response times of the FE as the
number of users in the db went up.  Since all users complain about response
times, and since I belong to the set of developers with users, I decided to
look at this issue at one of my clients.  
 
The client runs systems from old 800mhz (I believe) Dells to modern 3ghz
Dells.
The client recently upgraded all switches to 1gbit.
The file server hosting the BEs is a fairly modern (2 GHz?) machine.
The FORM being opened is extremely complex.  It is bound, with tabs I can
get more complete statistics on the hardware.
 
The machine with the fastest access should be thrown out for statistics
because she does her work mostly in the early morning when there are no
users so the results would be misleading.
 
What I discovered:
 
The Min time to open can be misleading since the time of day when they
arrive, and thus how many users are already in the database will vary from
user to user.  I added the Min() user count and the Max() user count to
discover the extremes.
The Max time to open though is very instructive, but it needs to be compared
to the average as well.  The Max in this data appears to be an aberration,
which I do not have an answer for, but has never been repeated.
This data covers one complete day (yesterday).
This specific client had a maximum of 33 connections to the main database BE
open at once.
The minimum appears to be related to machines left on and in the database
overnight.
 
1) The second fastest user has an average time to open of .90 with a Max
time to open of 1.28.  Notice though that by the time the user started work,
the number of users was quite high.
2) The second slowest user has an average of 4.75 seconds to open with a max
of 7.88 seconds.
3) The times between are a pretty smooth gradient as the times climb from
slowest to fastest.
4) There is a startling difference between the fastest and slowest.
5) There is a definite correlation between the number of users and the times
to open
6) The number of users plays much less of a part than other factors
 
In talking to the client, it appears that the radical speed differences
between machines is largely dependent on the age of the machine, which
equates to the speed of the processor / memory / disks etc.  
 
In summary, I think it is safe to say that even given the small(?) maximum
number of concurrent users (33) the number of connections definitely impacts
the time to open the form.  OTOH, the impact seems to be much less than (in
fact dwarfed by) the impact of the age of the hardware.
 
It would be interesting to see how the numbers scale when the number of
users climbs up towards 100.  I will see if I can emulate that by opening
the BE directly over and over in separate instances of Access on my
workstation there.
 
Anyone desiring to see the totals query behind my results, please email me
offline and I will send it in a spreadsheet.
 
 
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




More information about the AccessD mailing list