JWColby
jwcolby at colbyconsulting.com
Tue Feb 13 14:49:14 CST 2007
>I'll bet the number escalates should you choose to open a form with a combo or three. Nope. In fact the form that I am discussing has a query based on about TEN tables, has TWENTY NINE combos loaded when the form loads (I just counted), and TWENTY tabs, 16 of which have JIT subforms which load when the tab is clicked on and unload when the tab is clicked of off. This ain't your daddy's database! This form is the center of the Call Center Universe for this company. And no, the number is ONE connection for each instance of the FE and one connection each for a handful of "server applications" which upload / download FTP files and email attachments. That is NOT the number of LOCKS as discussed in another email thread last week. 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 artful at rogers.com Sent: Tuesday, February 13, 2007 3:22 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Number of users in the DB I'll bet the number escalates should you choose to open a form with a combo or three. Arthur Fuller Technical Writer, Data Modeler, SQL Sensei Artful Databases Organization www.artfulsoftware.com ----- Original Message ---- From: JWColby <jwcolby at colbyconsulting.com> To: Access Developers discussion and problem solving <accessd at databaseadvisors.com> Sent: Tuesday, February 13, 2007 2:24:04 PM Subject: Re: [AccessD] Number of users in the DB The number of connections is counted with some code I found somewhere. I modified it (made it slightly more complex) in order to get it to return a string of users in one case and a connection count in another. I really should have (and will likely go back and do it) just created two different functions. The function basically opens the ldb file and parses out the data in that file to find the workstation names, and then counts them. However, it turns out that the number increases according to how many instances of access are connected to the specific be, even if the access instances ARE ON THE SAME MACHINE. IOW, in order to test the response times of my client's users when there are more connections, I simply opened the BE 20 more times on my local machine. Open access / open the BE from within Access / repeat. Thus one single machine (my workstation) is showing up as TWENTY connections. 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 Dan Waters Sent: Tuesday, February 13, 2007 1:48 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Number of users in the DB John, How did you count the number of connections? Was this equal to the number of users? The local machine age being a strong factor makes sense since the BE machine doesn't do any processing. Great data! Dan Waters -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Tuesday, February 13, 2007 11:41 AM 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 -- 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