Response time slows to a crawl with mutiple users

Question

I have a BE/FE application, with the BE on a networked server. The app has an unbound data entry form that when the user Saves a record, it does some validation, then updates a raw data table of about 155K records, and also updates a couple of small statistical tables that increment summary data. The summary tables are bound to the switchboard. The raw table has a PK and 2 unique keys.

When one user is on, the Load time is on the order of 0.9 seconds, and the Save time about 1.5 seconds. However, when User #2 is logged in, the times shoot up to 5.5 seconds per load, and 22 seconds per Save. User #2 is not even in the data entry app, only the switchboard.

What should I be looking at to reduce the response time when there are multiple users?

---

Darrell H Burns

Answer 1

Do you maintain a persistent connection to the back-end?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

Search online for "access performance faq". Lots of good tips there.

---

-Tom. Microsoft Access MVP

Answer 3

The switchboard is always open and is bound to 2 tables on the backend. The data entry form uses DAO recordsets connected to the BE to fetch the data and then to save the updates, which is where the delays are occurring.

---

Darrell H Burns

Answer 4

I'd definitely look over Tony Toews' Access perfomance FAQ, as Tom suggested.  Some of that info may be a bit dated, but something in there may be helpful.

Is the amount of data to be saved particularly large?  Is the update taking place within a transaction? The load and save times with only one user, .9 and 1.5 seconds, seems unusually long.  Is the server on the local network, or on a WAN or cloud server?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 5

First I trust that each user has their own separate FE - if not, all bets are off.

Then I would temporarily deactivate the: "also updates a couple of small statistical tables that increment summary data"

So that all that is being done is the record save/update.  There should be no delay noticeable in this task when multiple users are going.  I am assuming you mean that what is being discussed is the user's single record into the table and not a full table update of some kind.

Even if I misunderstand what you mean by the user saving the record - in the end you want to segment and isolate the processes to see which is causing the grief.

Answer 6

 The server is on a LAN. The raw data table (Route Tickets) totals 155K rows. I do the update by looping over the data entry form controls (textboxes and comboboxes) and updating the table via a DAO recordset. There a few steps where I open a DAO recordset to another BE table to fetch additional data for validation purposes. Then I update the summary tables. I have a timer on each step and any update to a backend is on the order of 3-4 seconds with another user logged in. With no other users logged in the elapsed times are sub-second. I don't know if it's a network problem or an Access problem (or a programmer problem).

---

Darrell H Burns

Answer 7

Yes, the FEs are on each workstation.

I've isolated 2 queries to demonstrate the problem:

1. Set rStats = dbBE.OpenRecordset(sqlUncollectedTix)
2. Set rStats = dbBE.OpenRecordset(sqlCollectedTix)

Where dbBE is a DAO database opened on the BackEnd, and rStats is a DAO Recordset.

As shown below, both are SELECT queries. I timed 4 scenarios. As you can see, the response time increased by a factor of about 40 once W/S 2 opened the app. What's especially baffling is that when I closed out the W/S 2, the response time stayed the same. It only dropped again after I closed out W/S 1 and reopened.

I appreciate any suggestions.

---

Darrell H Burns

Answer 8

I don't have the magic bullet. I'm bringing up these items just to generally improve your code.

> baffling
That's probably because of caching.

Since both queries are not updatable, you should open them as snapshot:
Set rStats = dbBE.OpenRecordset(sqlUncollectedTix, dbOpenSnapshot)
The strategy is to always use the most lightweight recordset.
Stored queries are slightly faster than inline queries.

Re q1:
NumUncollected does not need to be computed. I am assuming it is NumTickets - NumCollected.
Rather than the IIf around CollectionDate, you can simply Count the dates; the nulls will be omitted.

Re q2:
Make sure you have an index on BatchID and on CollectionDate.

One more suggestion: in your startup code set a global database object to your BE, and keep it around for the entire session.

Roughly how many rows are returned by these queries?

---

-Tom. Microsoft Access MVP

Answer 9

I appreciate the suggestions, Tom. I did implement each of them but unfortunately there was no discernible impact on response time. As you can see from the run-time stats, it's pretty quick when there are no other users connected.

Only one row is returned for each query.

I have also experimented with inline queries vs saved queries and the impact is negligible.

The fact that it only takes one other user connected to the backend to throttle the read time, could it be possibly be related to record locking?

---

Darrell H Burns

Answer 10

Yes I think record locking has something to do with it, or file locking, but that's true for all multiuser Access applications and we typically don't see this performance drop.

At this point I would run a couple more experiments to collect more data, and see what shakes out.

1. Run two instances on your local machine, connected to BE on local machine.

2. Run two instances on your local machine, connected to BE on server (I believe in the past you had 2 instances on two machines)

3. two instances on your local machine, connected to BE on another workstation or another server with different OS

Try turning off AntiVirus.

Run Resource Monitor (launch from Task Manager > Performance)

---

-Tom. Microsoft Access MVP

Answer 11

OK, here's what I've found...

1. Run two instances on your local machine, connected to BE on local machine.

• Queries ran very fast from both -- around 1/10 second.

2. Run two instances on your local machine, connected to BE on server (I believe in the past you had 2 instances on two machines)

• Same as above,  around 1/10 second

3. two instances on your local machine, connected to BE on another workstation or another server with different OS -- slight variation. I put the BE and FE on a Windows10 machine, and the other FE on a Windows7 machine (this is our standard). With both connected, response times escalated again up to around 4 secs per query for the Win7 machine. The Win10 machine ran much faster -- around 4/10 second -- but not as fast as with one connection.

Try turning off Antivirus

• It was off in all of the above cases

---

Darrell H Burns

Answer 12

So with the Win7 machine in the mix, it is slow. Can that machine be upgraded? You may qualify for a free upgrade to Win10. Not sure it will help, but can't hurt to try.

I don't know how many users you have in total, but a server OS is much better at hosting BE files than a workstation.

Are you running a Workgroup? I suspect so. One of our clients was as well and they had serious performance issues as well. We ended up having everyone run on a Terminal Server in the office. Of course YMMV.

---

-Tom. Microsoft Access MVP