Very Slow Multi-User Access

[The_Doc_Man]

Turns out our traffic was being routed from Birmingham to Coventry and then back to Birmingham.

In our case, the traffic was from New Orleans, LA to Norfolk, VA and back again. Same concept, about 6 network hops counting the routers involved. Excruciatingly slow - and then we changed sub-domains to keep it all local. Had to change mail from Outlook to CDO, lost the ability to automatically send digitally signed e-mails, but everything else worked about 50-100 times faster.

 

[iamchemist]

The concept of a persistent connection is not related to the hardware.

A persistent connection, in this context, refers to keeping a table in the back end open continually from a form in the front end.

The problem is, as the_Doc_Man outlined, that each time the front end needs to use a table in the back end, it has to negotiate a connection with other front ends which also want to use the back end. By keeping a form which is bound to a small table in the back end open constantly in the front end, that negotiation for a shared connection -- or reconnection -- is avoided, which should speed things up.

Many developers do this by binding a form to a small table and opening that form when the accdb first opens. The form is often hidden. It's only function is to maintain that persistent connection. See also the comments in Post #11.

Again, not a factor of routers, ethernet ports, etc. It has to do the the interactions between the multiple front end databases.

I appreciate the explanation of Persistent Connection! I will work on making that happen.

However, I am confused in that I thought several of you had told me "the Ethernet Switch definitely has to go to speed up the Database Operation". I thought that advice had to do with Persistent Connection. If it does not, then is there any good reason for the Ethernet Switch to go away in our Network Configuration at all?

 

[The_Doc_Man]

The question is, what kind of switch do you have? Can you give us a name brand and model number so we can look it up? Because your prior description of what it does is the source of our concern. To be honest, I've never heard of a switch doing what you described. I need to know what it really does in order to correctly diagnose this problem.

As to "the Ethernet Switch definitely has to go to speed up the Database Operation" - EITHER the Ethernet is or is not the source of the problem, but until we know which kind of switch it is, "keep it" or "toss it" is up in the air.

 

[gemma-the-husky]

@iamchemist

I think there is a bit of a "duck test". I tried to add a link to a "duck test", but the short cut was longer than I wanted. Anyway. If it walks like a duck, and talks like a duck, it most likely is a duck.

You are describing a situation exactly like one that is caused by the absence of a persistent connection. One user works fine. Concurrent users experience a slow down.

Our conjecture is leading us to suppose that your setup is not enforcing persistent connections. Maybe it's not easy to test. However, In a corporate environment you would have thought you would have a server shared by all users on the same LAN without too much trouble or cost. A NAS device might suffice. One of your PCs might be able to act as host. I am not exactly sure how to set it up, but I am sure you deal with someone who does know how. If not you most likely need an IT advisor to manage aspects of your computer organisation.

Note that using access requires all concurrent users to share a LAN. You won't get adequate speed either if all users are not on the same LAN, which is why terminal services are recommended if you have users working from remote locations.

 

[The_Doc_Man]

@iamchemist

For some reason, you replied by Reporting Dave's response as though you had a complaint. This is what you said:

My Ethernet Swithc is a Trendnet Gigabit switch model TEG-S8g.

So, the question is "is this Ethernet Switch causing Persistent Connection" problems in Access.

As I have said major forms take 4-5 sec. to load with one user and 8-9 sec. with two users.

Sorry this took so long to get posted. I dutifully brought home the info. about the Switch and then lost it. So, it took time to retrieve it all again.

Having looked up the switch in question online, I very seriously doubt that it can cause an interruption that would affect persistent connections. It looks like an ordinary Ethernet switch with some energy-saving abilities, but there is no evidence of any switch-initiated "few millisecond" session disconnects. I have to believe that you misunderstood what you either heard or read about this device's properties.

You guys have been giving me advice that my slow database situation probably results from NOT having a "persistent connection" to Access, because there are two Laptops talking to a Desktop Server via Ethernet Cables THROUGH an Ethernet switch. That switch means that each of my two users is disconnected for Access for a few milliseconds every few minutes, while the other User/Laptop talks to Access.

If you have two users plugged in through that ordinary but slightly more energy-efficient switch, BOTH are continuously connected because either port will respond on demand within a very low number of milliseconds, and network time-outs take 30 seconds if the drivers are all set up according to standard configuration options.

The only disruptive event of note in Ethernet usage - for ANY Ethernet network, not just yours - is due to collisions (two Ethernet users sending simultaneously within microseconds of each other). If there is a network collision , the rules of Ethernet networks allow what is called "evasion" so that both conflicting senders have the chance to send their data again VERY quickly. After a collision, they re-transmit a few milliseconds apart from each other because each port on an Ethernet has different delay times. Since this is a Gigabit network running a TCP/IP-based traffic load that has the typical 1500-byte packet limits, messages will occupy MICROseconds - not milliseconds. Depending on byte formats, a full packet with NO compression and with normal 8-bit bytes with a parity bit will take less than 15 microseconds on a true Gigabit network.

With only a couple of users, the odds are highly against having enough of a collision-based outage to drop a connection. A "re-transmit after collision" doesn't mean a "disconnect for a few milliseconds every few minutes." The only way that you would get a session disconnect would be if there was a failing network I/F card. We don't have to get into failure modes because from your other posts, both network cards are working.

As to the Report, I am going to call that "Resolved" and make it inactive.

As to whether that switch is causing you problems, I won't say 100% NO on that, but it is a high 99+% NO, the only possible exception being a physically faulty switch - but your users are getting through, so that is ALSO unlikely.

I think it is clear - we are looking at a software or networking protocols consideration as the source of your slowdown. The "persistent connection" is the best path to initially pursue. Checking for having forms or queries set up for pessimistic locking would be MY second choice as a potential problem, but others may disagree with me by thinking of other sources of software interference.

By the way, Dave's reference to "duck" test is for this kind of test:

"If it looks like a duck and swims like a duck and quacks like a duck and waddles when it walks, the odds are that it is, indeed, a duck."

 

[cheekybuddha]

If it does not, then is there any good reason for the Ethernet Switch to go away in our Network Configuration at all?

Are you confusing this with the recommendation that Access only be used over a wired LAN, as opposed to clients connecting via wifi which can be too unstable and lead to data corruption issues?

 

[iamchemist]

@iamchemist

For some reason, you replied by Reporting Dave's response as though you had a complaint. This is what you said:



Having looked up the switch in question online, I very seriously doubt that it can cause an interruption that would affect persistent connections. It looks like an ordinary Ethernet switch with some energy-saving abilities, but there is no evidence of any switch-initiated "few millisecond" session disconnects. I have to believe that you misunderstood what you either heard or read about this device's properties.



If you have two users plugged in through that ordinary but slightly more energy-efficient switch, BOTH are continuously connected because either port will respond on demand within a very low number of milliseconds, and network time-outs take 30 seconds if the drivers are all set up according to standard configuration options.

The only disruptive event of note in Ethernet usage - for ANY Ethernet network, not just yours - is due to collisions (two Ethernet users sending simultaneously within microseconds of each other). If there is a network collision , the rules of Ethernet networks allow what is called "evasion" so that both conflicting senders have the chance to send their data again VERY quickly. After a collision, they re-transmit a few milliseconds apart from each other because each port on an Ethernet has different delay times. Since this is a Gigabit network running a TCP/IP-based traffic load that has the typical 1500-byte packet limits, messages will occupy MICROseconds - not milliseconds. Depending on byte formats, a full packet with NO compression and with normal 8-bit bytes with a parity bit will take less than 15 microseconds on a true Gigabit network.

With only a couple of users, the odds are highly against having enough of a collision-based outage to drop a connection. A "re-transmit after collision" doesn't mean a "disconnect for a few milliseconds every few minutes." The only way that you would get a session disconnect would be if there was a failing network I/F card. We don't have to get into failure modes because from your other posts, both network cards are working.

As to the Report, I am going to call that "Resolved" and make it inactive.

As to whether that switch is causing you problems, I won't say 100% NO on that, but it is a high 99+% NO, the only possible exception being a physically faulty switch - but your users are getting through, so that is ALSO unlikely.

I think it is clear - we are looking at a software or networking protocols consideration as the source of your slowdown. The "persistent connection" is the best path to initially pursue. Checking for having forms or queries set up for pessimistic locking would be MY second choice as a potential problem, but others may disagree with me by thinking of other sources of software interference.

By the way, Dave's reference to "duck" test is for this kind of test:

"If it looks like a duck and swims like a duck and quacks like a duck and waddles when it walks, the odds are that it is, indeed, a duck."

Doc Man,

I am terribly sorry that my reply seemed like a complaint to you. It was certainly not meant that way! I simply provided information about our Ethernet Switch, as was requested. I honestly don't even see anything in what I wrote that could be taken as a complaint.

Thank you again for all the information. If it looks very unlikely that the Switch is a problem, I will insert the suggested VBA code to maintain a persistent connection, and see if it does any good.

It does puzzle me that for the extra several seconds, while we are waiting for a form to load with two users on the System, I see an error message in the top left of the laptop screen which says "Access Not Responding". This would certainly indicate that Access is busy doing some kind of housekeeping task.

 

[Gasman]

I get 'Not responding' on a single user system now and again.
So I would look at what else is going on in task manager/resource manager.

 

[iamchemist]

I get 'Not responding' on a single user system now and again.
So I would look at what else is going on in task manager/resource manager.

I am confident that, at the time I see "Access Not Responding", no other task is running on the back-end Desktop Computer.

 

[Gasman]

I am confident that, at the time I see "Access Not Responding", no other task is running on the back-end Desktop Computer.

Doesn't have to be the backend computer? , as explained by the fact that I get it on my laptop where both BE and FE exist?

 

[Bennet]

My solution to multi-user performance issues has been as follows:

1. Design the database from the outset to reference the back end the absolute minimum it has to. As part of the autoexec procedure, download all commonly used reference tables and refer to them locally. This makes launching slower, but everything thereafter is faster.

2. For all SELECT queries I use something I call "disconnected recordsets" which is where your query talks to the back end, gets the recordset, then disconnects from the source tables so it actually deliberately breaks the link and frees up the back end for other users. The recordset can still be displayed on a form or combobox or whatever, but doing so doesn't hold an open connection to the back end. It's honestly been a bit of a revolution for me and I haven't looked back since. You can't update recordsets, but I just design around that.

If you go to the folder that contains any one of my back ends, you'll rarely see a lock file, even though users are using the applications. It appears briefly and then disappears if you watch long enough. Any long lasting lock file is a sign I've done something wrong.

Since adopting the above two points for all of my applications everything works really nicely. No more slowness, no more back end corruption. Happy users.

I know others will strongly disagree with this approach. Just sharing in case it's of any interest to the OP.

 

[The_Doc_Man]

I honestly don't even see anything in what I wrote that could be taken as a complaint.

It wasn't what you said, but that your reply was found in our "Complaint Reporting" section that is a moderator-only area. Might have been just the way you clicked on something. It has already been handled, no worries.

 

[The_Doc_Man]

I am confident that, at the time I see "Access Not Responding", no other task is running on the back-end Desktop Computer.

If, when you see that, you can IMMEDIATELY open Windows Task Manager, then in the Processes list look down the left-side column for a process running MSACCESS.EXE and click on that. The stats to the right will tell you if something is saturated. I'm betting dollars to donuts against memory being 100% (because THAT would be a sign of broken hardware), but either CPU or Disk or Network can trigger that "Not Responding" message. Once WTM is up and running, you can switch between Processes and Performance to see if something is running at 100% utilization.

When you see "Access Not Responding" that can be due to some sort of query that has an unusually large "result set" (the set of all records NOT filtered out by the WHERE clause). BUT it could also be based on a recordset-involved VBA loop. Do you have anything like that? OR do you have any kind of multi-table query? In the multi-table query what I would consider as a possibility would be that the query SHOULD have been, but was NOT, a proper JOIN of the tables. With improper JOINs you get HUGE result sets that slow down darned near anything.

 

[iamchemist]

I think the description probably was a misinterpretation of the suggested need for "a persistent connection" because it is so unlikely to be the case.

Unfortunately, those Access developers with many years of experience tend to overlook the fact that newbies and less involved Access developers do not have what might be called insider knowledge about such terms. "Persistent connection", like "split database" and many other terms we all take for granted can be, and often are, misunderstood in that situation. I hope the OP merely defaulted to the most likely interpretation of the term from their perspective. I fervently hope that's the case.

A General Thank You for everyone who helped me and our Food Pantry with Persistent Connection. I have installed the needed code, etc. at our Food Pantry, and forms are opening much, much faster. What used to take 8-9 seconds to open now only takes 2-3 seconds.

Thank you all SO MUCH for your help. Everyone at our Non-Profit thanks you profusely!

 

[The_Doc_Man]