I have a split database with each user having their own copy of the front end, and the backend hosted on a network folder. On startup, I open a hidden linked form to the backend to maintain a consistent connection and also open a switchboard for the user to interact with. When there is only one user, this switchboard loads in about 5 seconds from when you double click on access. When >1 one users it takes about 30 and it's not a linear time increase, as it's the same speed when it's 2 users or 10 users.
I've tried tons of stuff from a bunch of different websites to speed up the loading times, but none of them have had any effect. The only thing that I can think of is that there is something with the locking file of the backend that is bottlenecking it. Anybody have any ideas? Record locking is also set to optimistic.
If the lock file were the problem, no one would be able to get in after the first user. Lock resolution is usually handled by the Windows file system. I've had systems with 10 users in a similar configuration as you described and didn't see that kind of slowdown.
How "deep" is the shared folder in that file system? Top-level? Three layers deep?
What version of Windows?
When you use linked tables, are you directly opening the tables or do you use a query? I have found that I got best results if I had a single-table query matching each table and opened the query rather than the table - even though it was the same exact fields. I was able to select the locking style for each query that way. So for the "keep alive" query that never read or wrote anything, I used the query with Optimistic locking and minimum locking and whatever else I could set.
For some reason that I never actually quite understood, the "direct table open" was always slower than the "single-table query open."
If the lock file were the problem, no one would be able to get in after the first user. Lock resolution is usually handled by the Windows file system. I've had systems with 10 users in a similar configuration as you described and didn't see that kind of slowdown.
How "deep" is the shared folder in that file system? Top-level? Three layers deep?
When you use linked tables, are you directly opening the tables or do you use a query? I have found that I got best results if I had a single-table query matching each table and opened the query rather than the table - even though it was the same exact fields. I was able to select the locking style for each query that way. So for the "keep alive" query that never read or wrote anything, I used the query with Optimistic locking and minimum locking and whatever else I could set.
The database was designed in such a way that every table is able to use optimistic locking and minimum locking and all are set as such. I just tried using a query, it that improved load time by about 5 seconds, but it's still at like 25 seconds.
Thanks, I've already tried pretty much all of the things in the link. And below is essentially my entire database. There's a couple other tables for stuff that's used in combo boxes, but the majority of stuff that is loaded at startup is between these two tables. We get a single run that needs to be looked at by many reviewers so there's a one to many relationship in these tables.
By inference, you WERE opening tables so complex JOINS weren't an issue. Based on my experience with a similar configuration, this COULD be a network issue but will require some homework. Work with your network guru (which might be necessary because your site network might not allow UDP traffic such as a PING to test response time.)
If you can run two or three tests, this might rule out raw network speed issues.
Test 1: PING the back-end with nobody using the DB.
Test 2: PING the back-end with one user.
Test 3: PING the back-end with > 1 user.
Now compare the times. You SHOULD see PING times for each test in the range of 1-3 milliseconds for typical modern machines and a 1 GB network backbone. If PING time is not affected, it is neither a ROUTE issue nor a problem with the network i/f. If PING time IS affected significantly, you have a problem with routers or firewalls. (I'm betting against it being network problems but you have to actively test that to rule it out.)
The problem with this kind of problem is finding the cause. Usually, the fix is easy. But we don't have a lot of "hooks" into the driver layers to determine their efficiency.
I agree with what Doc_Man says and suggests. Further, I'd like to know more about the database. What exactly is a "run"? Can you tell us about your business so we can see what run and reviews are in context?
It appears that there are several fields in RUN Information that are not visible in your picture. It's better if you expand the tab;les before doing the capture.
Also, for clarity,
We get a single run that needs to be looked at by many reviewers so there's a one to many relationship in these tables.
I agree with what Doc_Man says and suggests. Further, I'd like to know more about the database. What exactly is a "run"? Can you tell us about your business so we can see what run and reviews are in context?
I almost hate to do this because it's so darned tedious to explain, but what you need to do is to "instrument" your forms.
Start by making a test copy of the FE file that looks JUST LIKE the "normal" FE. But you will add a feature. To streamline this for future use, use a general module for the code I will describe.
1. Create a FRONT END table, call it tblTracer for event logging. The front-end table should have fields similar to:
CallNum: Autonumber (and make it the Prime Key)
CallDtTm: Date
CallMSec: LONG
CallText: Short Text (80)
2. Write support subroutines; one to start the trace process, one that when called, appends a record to this table. If the active routine was called TraceCall you would use it like this:
TraceCall "Entering Form_Load event"
In the proposed support module, In the global declaration section (before the first routine)
Code:
Public Tracing as Boolean
Public rsTRec as DAO.Recordset
Public dbX as DAO.Database
In the code body, define some routines:
Code:
Public Sub StartTrace()
Set dbX = CurrentDB
dbX.Execute "DELETE * FROM tblTracer ;"
Set rsTRec = dbX.OpenRecordset( "tblTracer", dbOpenTable )
Tracing = True
End Sub
Public Sub TraceCall( EventText as String )
If Tracing Then
lTimer = Timer()
dtCall = Now()
With rsTRec
.AddNew
.[CallDtTm] = Now()
.[CallMSec] = Timer()
.[CallText] = EventText
.Update
End if
End Sub
To keep this from growing crazy, add a button to the form to set the Tracing button to FALSE so that you stop tracing after you click that. Close the rsTRec recordset and set both rsTRec and dbX to Nothing.
Now you can just open table Tracing in datasheet view. Because it has a PK in the form of an autonumber, the records you store will appear in the order of the calls that stored them.
Now, the "instrumentation" final steps...
In your switchboard form's Form_Open event, as early as possible in the event code for that, call the StartTrace subroutine. For various parts of the Form_Open, Form_Load, Form_Current, Form_Enter, Form_Activate, ... whatever events you have declared, do a TraceCall invocation with unique text that will show you what step of form activity was going on at the moment of the call. At the very minimum, place a TraceCall at the start and end of each event subroutine. It won't hurt (for testing) to have calls around any common subroutines called from the event code.
The call to Timer() used by TraceCall gives you milliseconds since midnight of today, so the difference between those fields for two successive records is the elapsed milliseconds between those two events. Some events will be so fast that even in units of milliseconds, they will show up as 0 difference - and that's OK. You are looking for events that take THOUSANDS of milliseconds. Don't sweat the short gaps.
Note also that if your form goes "idle" because it is waiting for user action, the comment you record for the event handler leading to that status should include "User Think Time Starts Now" or something like that so you know that the big time gap following THAT interval doesn't count.
It sounds like a lot of work - and it IS, no disagreement here - but this will tell you what step is taking time. You are looking for large anomalies between the times for the 1 user and multi-user cases. If there ARE none then the problem HAS to be purely network-centric. Stated another way... if there are no significant differences for the times recorded this way for the 1-user and many-user cases, then the problem started BEFORE you ever executed the Form_Open routine for the opening form. However, since this is a split DB, the local entities will open first, which should be BEFORE you actually "touch" the back end tables.
The support module can be an innocuous part of the FE file - but only your TEST copy should actually call the little routine that starts tracing.
This is the only way I have ever found (so far) to know where time is going in a particular form. And yes, I used this in my own database that was similar in topography to the one you described. It helped me prove that we were getting clobbered by network issues.
I almost hate to do this because it's so darned tedious to explain, but what you need to do is to "instrument" your forms.
Start by making a test copy of the FE file that looks JUST LIKE the "normal" FE. But you will add a feature. To streamline this for future use, use a general module for the code I will describe.
1. Create a FRONT END table, call it tblTracer for event logging. The front-end table should have fields similar to:
CallNum: Autonumber (and make it the Prime Key)
CallDtTm: Date
CallMSec: LONG
CallText: Short Text (80)
2. Write support subroutines; one to start the trace process, one that when called, appends a record to this table. If the active routine was called TraceCall you would use it like this:
TraceCall "Entering Form_Load event"
In the proposed support module, In the global declaration section (before the first routine)
Code:
Public Tracing as Boolean
Public rsTRec as DAO.Recordset
Public dbX as DAO.Database
In the code body, define some routines:
Code:
Public Sub StartTrace()
Set dbX = CurrentDB
dbX.Execute "DELETE * FROM tblTracer ;"
Set rsTRec = dbX.OpenRecordset( "tblTracer", dbOpenTable )
Tracing = True
End Sub
Public Sub TraceCall( EventText as String )
If Tracing Then
lTimer = Timer()
dtCall = Now()
With rsTRec
.AddNew
.[CallDtTm] = Now()
.[CallMSec] = Timer()
.[CallText] = EventText
.Update
End if
End Sub
To keep this from growing crazy, add a button to the form to set the Tracing button to FALSE so that you stop tracing after you click that. Close the rsTRec recordset and set both rsTRec and dbX to Nothing.
Now you can just open table Tracing in datasheet view. Because it has a PK in the form of an autonumber, the records you store will appear in the order of the calls that stored them.
Now, the "instrumentation" final steps...
In your switchboard form's Form_Open event, as early as possible in the event code for that, call the StartTrace subroutine. For various parts of the Form_Open, Form_Load, Form_Current, Form_Enter, Form_Activate, ... whatever events you have declared, do a TraceCall invocation with unique text that will show you what step of form activity was going on at the moment of the call. At the very minimum, place a TraceCall at the start and end of each event subroutine. It won't hurt (for testing) to have calls around any common subroutines called from the event code.
The call to Timer() used by TraceCall gives you milliseconds since midnight of today, so the difference between those fields for two successive records is the elapsed milliseconds between those two events. Some events will be so fast that even in units of milliseconds, they will show up as 0 difference - and that's OK. You are looking for events that take THOUSANDS of milliseconds. Don't sweat the short gaps.
Note also that if your form goes "idle" because it is waiting for user action, the comment you record for the event handler leading to that status should include "User Think Time Starts Now" or something like that so you know that the big time gap following THAT interval doesn't count.
It sounds like a lot of work - and it IS, no disagreement here - but this will tell you what step is taking time. You are looking for large anomalies between the times for the 1 user and multi-user cases. If there ARE none then the problem HAS to be purely network-centric. Stated another way... if there are no significant differences for the times recorded this way for the 1-user and many-user cases, then the problem started BEFORE you ever executed the Form_Open routine for the opening form. However, since this is a split DB, the local entities will open first, which should be BEFORE you actually "touch" the back end tables.
The support module can be an innocuous part of the FE file - but only your TEST copy should actually call the little routine that starts tracing.
This is the only way I have ever found (so far) to know where time is going in a particular form. And yes, I used this in my own database that was similar in topography to the one you described. It helped me prove that we were getting clobbered by network issues.
I get what you're going at here. I've tried something similar by stripping away each specific part of the form and seeing if it made any difference in terms of speed to load the form. I wasn't able to pin it down to a specific function, but I'll give this method a shot.
I found the following and, although there is no solution stated (yet), it sounds similar. I thought you might want to see
what others with the slowness issue are doing. This is ongoing @Oct 2016--if I hear of a solution or if the problem can be pinpointed, I'll post the same.
Speed Issues on network just with Access App
Not sure if any of you have had the below issues before, but I haven't in the dozens of years of using access. Driving me nuts, along with the customer. Hope someone can help. Sorry for long post but wanted to share details.
About 15 users w/Access 2000 running a front end access program Backend lives on a server in a shared folder (both FE/BE are MDB's) Gigabit Ethernet Switch Workstations are (were) XP Pro SP3, Win7Pro SP1 64Bit XP machines are Dell Optiplex 360's and 380's Win7Pro machines are Dell Optiplex 3020 Small Form Factor and Micro, and one Opti 7010
Above combo has been running fine since pretty much before dinosaurs became extinct. Besides Access, some users have Quickbooks, and everyone has some flavor of Office H&B (2007, 2010, 2013, 2016). None of the H&B come with Access, so only one version of Access on the PC.
In attempt to get remaining users off XP, I bought more Dell's, but they were the Optiplex 3040 Micros, Win7Pro 64Bit. Not too long after putting these on the network, some, but not all, of the 3040 users experienced slowdowns where a screen that would normally take 2 seconds to open up, now taking 10, 15 sometimes up to 2 minutes. Not consistent, user might work fine for a while and then bang, slow to a crawl, pretty much unusable. Other 3040 users may or may not have same problem, but then some of the 3020 micro users experienced the same problem where they never did before, but not nearly as many times as the 3040 users. Very strangely, the XP users never experience any of this nor the 7010 w/Win7Pro 64Bit, even when the other users are. They just move along. So to with the Opti 7010, no issue. And all machines are running fine with all other apps - QB, Word, Outlook, PDF's, Internet, etc. - Just Access Issue.
Saw various articles describing same problem, even with a standalone workstation running both FE and BE with Windows 7 64Bit. I haven't seen the issue if there is only one user in the system (common on a Saturday). However, I've seen this issue with just 2 people in.
Although I can't tell if every machine needs the below or just ones with issues, I have tried the following based on many posts that I have read:
PC Specific:
Group Policy Change to Turn Off Multicast Name Resolution=Enabled Registry MaxBufferSize=50,000 Turn Autotuning Off (NetSh Interface TCP Set Global Autotuning=Disabled)
Non-PC Specific
I replaced the network switch with an older gigabit model but again, I do not think is it.
Replaced wiring at some workstations
Made sure the Lock file was deleted in the shared folder when all users out so it would get created fresh Access 2k SP1 or SP3 was installed on workstations Checked Tools/References to make sure none were missing on the workstation VBE6.DLL was dated 3/17/2015 on the Win7 Machines Bought a used Opti 7010 with same specs (because only other 7010 has never had an issue to date), set it up and put it on the network. Issue right away with only one other person on the network Probably a few other things I forgot to mention, I've lost track
Customer has brought back some old XP machines because of issue and they are working just fine.
I have a 32bit Win7Pro machine that I am almost done reformatting and will put on the network to see if that does anything.
Any other ideas? I don't think the number of users is the issue. I have a 2002 Insurance app with 65 people in it from three locations all day long and no speed issues.
And here are the follow up suggestions, and the user's responses
Suggestions:
A few.
1. Anti-virus - I would assume it's the same for all, but try turning it
off.
2. NIC drivers - make sure their up to date.
3. Make sure there is no diagnostic protocol loaded for the NIC. If so,
remove it.
4. Make sure the NIC is not set to go to sleep (low power mode)
5. Are you using mapped drives or UNC? If mapped, default settings in Win 7
disconnect a mapped drive after 10 minutes of inactivity. You can disable
this.
6. You should disable ipv6 if your not using it.
That's it for the moment off the top of my head.
Oh and the MaxBuffers, set it to the max of 65535. There was a bug in
some versions of Access when running on a 64 bit multi-core processor. JET
has three background threads by default, one of which was for cache cleanup.
If the threads ended up on different processors, they can block one another.
Setting MaxBuffers to the max alters the cache cleanup process to work
around that.
1) A/V - yes all the same McAfee VirusScan Enterprise and all up-to-date. Can try turning off for sure
2) NIC Drivers - I was wondering if they were too up-to-date. Seems like the threads I found on the net talked about NIC cards and advanced settings of these. Will check these as well
3) Diagnostic Protocol - didn't check this, but will remove if found
4) NIC Sleep - Not sure, but will check
5) Mapped Drives - yes, using Mapped Drives. I know how the little red X's pop up next to a drive when disconnected from the server share and a simple click re-activates it. I can try setting that 10 minute feature off, but I've not seen that issue anywhere before in this scenario.
6) IP6 - can disable
7) MaxBuffers - never had this set on any machine, but did so as I was trying to figure out this issue. I'll bump up to max 65535 as you mention and see where that gets me.
ej256, remember that I have a disclaimer in there. If the problem is caused before the Opening form actually opens, this method won't help because in that case, all the "action" has already occurred. It worked for me because I had queries that exercised delayed binding and they were inefficient - but that might not be what you will find. All this will do is tell you if something takes longer shared vs. stand-alone - but if it DOES reveal something, you can narrow down the point of contention pretty quickly.
I looked over JDraw's reference and went online to see if I could find something that explained the differences between XP and Win 7, but one thing that stands out is that we don't know what version of Windows and what version of Office you are running, or at least I didn't see that on re-reading the posts. Also don't know what kind of machines are involved.
I note that you have domain security in play here. Do you know if you have Network Time Protocol running so that your machines stay time-synched? Oddly enough, for some flavors of domain-based protocol, that can be an issue. I used to see security notices for that all of the time.
