Question split database / multi-user / locking issues (1 Viewer)

[tokash]

So I've been having a multi-user challenge with a split frontend/backend database using MS Access 2010.

The backend resides on a file server network with copies of the frontend on multiple user computer stations.

The issue is that action queries such as updates and appends run fine with a single user. But as soon as a second user connects to the backend, performance dies; i.e. an update query that would typically take 1 second with a single user takes almost 2 minutes with 2 users. Both frontends have a perpetual connection to the backend.

I've googled and researched many-a-forum and thread to figure out what was going on. I came across this one in this "General" thread where the situation seemed similar:
"Split Database sometimes VERY slow (more than 1 user?)" (I apparently can't attach a link yet until I have 10 posts?)

In that thread there was mention of using a handy proggy called "Wireshark" which monitors network traffic. So I thought I'd give it a try as well.

In attempting to troubleshoot, I created a simple split database configuration. The backend contains 2 tables, 1 table to act as a perpetual connection to the frontend while the other is involved with an update query on the frontend. The frontend links to the two backend tables and contains one additional local table and the update query. The update query joins the local frontend table with one of the linked backend tables and performs a simple field update.

To create a perpetual connection to the backend, I opened the frontend on each computer and then manually opened one of the linked tables in both frontends. This linked table is not involved with the one update query in the database.

I then opened up Wireshark and fired the update query on one of the local boxes. Much to my shock and horror, I found that there was a continuous stream of Lock Request / Lock Response in the SMB's...almost 900 transaction per second! The Lock Requests were coming from the file server where the backend resided and the Lock Responses from the local box.

While I believe I found the cause of the slow-down, I still can't figure out the cause of the locking request / response behavior. I tried all the available client setting variations of record locking...but to no avail.

I really hope it's not something like a file server setting that needs to be tweaked because I don't have admin rights to make those types of changes as that falls to our IT department. And getting IT to change anything takes an act of Congress...and we all know how often Congress takes to act on anything! /moan

I've attached the test split frontend / backend databases with sample data I am using to troubleshoot, just in case someone wants to look at them and see if there's something I didn't set up right. But it's pretty straightforward and simple. You would just have to relink the frontend tables to the backend in your own test enivronment.

So would anyone have an idea of what may be going on with the locking requests? :banghead:

 

[llkhoutx]

Welcome to the world of unbound forms and record clocking. Unbound forms will aleviate this issue. Having a bound form open, depending on your programs "locking" properties may lock the entire underlying table, as opposed to a "record." Have you set record locking to "omptimistic?" Take a look at this.

 

[tokash]

Thanks for the feedback llkhoutx. I'm actually not using any forms in my troubleshooting sample database (it's attached to my OP). I simply have one linked table open in the frontend to establish a persistent connection, and then had the update query reference a different linked table to update a field in a local table.

In my OP I also mentioned that I tried all combinations of client-side record locking. I've unchecked and checked "Open databases using record-level locking" with either "No Lock" or "Edited record" selected. All combinations gave me the same results.

 

[zpy2]

I have tried the files you uploaded under virtual machie with 3 clients and no problem happened as you mentioned.
You just want to update a local table according to a link table.so no conflicts need to be considered.

takes almost 2 minutes with 2 users. this not occurs on my pc.

even though two users run the query almost at same time, the query should not so slowly as you described.


of course, another option,You can also first import tbl_code to a local table then run update query based on this local table.

 

[gemma-the-husky]

Investigate persistent connection. That should solve the speed problem

 

[tokash]

I have tried the files you uploaded under virtual machie with 3 clients and no problem happened as you mentioned.
You just want to update a local table according to a link table.so no conflicts need to be considered.

takes almost 2 minutes with 2 users. this not occurs on my pc.

even though two users run the query almost at same time, the query should not so slowly as you described.


of course, another option,You can also first import tbl_code to a local table then run update query based on this local table.

zpy2, thank you SO much for testing it out in a completely different environment. It lets me know that the databases are indeed fine and that it's probably something with the fileserver that's causing the lock request spam. But fileserver stuff is getting beyond my expertise...
I was hoping to avoid having to copy the data locally because I have other situations with links to tables in the backend where I need multiple users to be in at the same time.

 

[tokash]

Investigate persistent connection. That should solve the speed problem

In my OP I state that I simply have each local box open a linked table in the frontend to the backend, which creates the persistent connection for each one.
It seems the lock request spam is occurring because more than one user has a persistent connection.
Once all users disconnect, the lock (.laccdb) file is deleted and then everything is fine again when a single user reconnects.

 

[llkhoutx]

Under that scenario, open and release very quickly.

 

[zpy2]

can you share your mdb file on your local disc with others？
so all server and clients in same Windows environment and unnecessary to share file through file server.

in multi users environment, use ms sql server rather than mdb as back end if possible.

 

[tokash]

can you share your mdb file on your local disc with others？
so all server and clients in same Windows environment and unnecessary to share file through file server.

in multi users environment, use ms sql server rather than mdb as back end if possible.

Actually I tested that scenario as well. I put the backend on another box rather than the file server, relinked the frontends, and still got the Lock Request / Lock Response spam.
The query runs and eventually completes. But it's like for every record update transaction a lock request / response is being sent which slows the query to a snail's pace.

If I could use a SQL server backend, I would. But this is unfortunately all I have to work with right now...

 

[HiTechCoach]

tokash,

Note: I have not looked at your example

The update query joins the local frontend table with one of the linked backend tables and performs a simple field update.

Why do you have a local table joined to a linked table? In my experience this has always cause a drop in performance. This should be avoided. If you were to analyze the execution plan I would export to see that ACE (the database engine for a .accdb) is copying all the data from the back end to a temp local table to process then send back the data. That would explain the extra time.

 

[gemma-the-husky]

I just assumed that the problem related to a persistent connection. Are you positive that the persistent connection remains active?

Occasionally I have databases that either use tables from the front and back ends, or tables from 2 discrete backends, without any performance hit.

I never use pessimistic locking though. Not sure whether you are. That might be a cause.

Otherwise, investigate Boyd's idea.


fwiw, a backend with only 1 table sounds very strange!

 

[zpy2]

I have uploaded the tbl_code to my remote SQL sever and link the table back, run the query, the problem you described happened.very slowly.
so Do you link the table on WLAN instead of office LAN？

 

[tokash]

tokash,

Note: I have not looked at your example

Why do you have a local table joined to a linked table? In my experience this has always cause a drop in performance. This should be avoided. If you were to analyze the execution plan I would export to see that ACE (the database engine for a .accdb) is copying all the data from the back end to a temp local table to process then send back the data. That would explain the extra time.

I agree, linking a backend to a local table is not the most efficient query.
However, the fact that the query in my example performs speedily (in 1 second) with only one user connected tells me it is not the query design that is at fault. So linking the backend to a local table in this particular example is not the direct cause of the issue. It's when a second user connects to the backend (and they are not doing anything except being connected) where the world suddenly moves at a snails pace, and I'm trying to find out why that is.

 

[tokash]

I just assumed that the problem related to a persistent connection. Are you positive that the persistent connection remains active?

Occasionally I have databases that either use tables from the front and back ends, or tables from 2 discrete backends, without any performance hit.

I never use pessimistic locking though. Not sure whether you are. That might be a cause.

Otherwise, investigate Boyd's idea.


fwiw, a backend with only 1 table sounds very strange!

Yes I am sure that both connections are persistent and active.

The backend I am using has far more than just one table. I broke it down in my example to keep things simple and directly related to the issue I am having.

 

[tokash]

I have uploaded the tbl_code to my remote SQL sever and link the table back, run the query, the problem you described happened.very slowly.
so Do you link the table on WLAN instead of office LAN？

I'm using a LAN. Did your slowdown occur over a WLAN?

 

[zpy2]

Sub updatetbl_Codelocal()
Dim xmlServerHttp As New xmlhttp
Dim strSite As String
'Dim strOut As String, stream As ADODB.stream
Dim EntityBody
Dim rs As DAO.Recordset
Dim startTime, endTime
Dim i
startTime = Now
i = 1
Set rs = CurrentDb.OpenRecordset("select code_id,code_desc from tbl_CodeLocal order by code_id")
Do While Not rs.EOF
strSite = "http://shanghaizpy.mvip8.xyz/b/updateData.aspx?id=" & rs("code_id")

xmlServerHttp.Open "GET", strSite, False
xmlServerHttp.Send

EntityBody = xmlServerHttp.responseText

rs.Edit
rs.Fields("code_desc").Value = EntityBody
rs.Update
'Debug.Print EntityBody, rs("code_id")
rs.MoveNext
i = i + 1
'If i > 9000 Then
' Stop
' Exit Do
'End If
Loop
Debug.Print DateDiff("s", startTime, Now)
'MsgBox Now - startTime
'my test is 5 seconds for 22612 records updated
End Sub
please try above codes of a loop way, about 5 seconds to update local table from remote web server.
avoid link table may be better for multi users.

 

[tokash]

zpy2, I very much appreciate your efforts in helping me troubleshoot.

I copied the code, added reference Microsoft XML v6.0, and ran it.

The initial connection and update took 56 seconds.

I then ran it repeatedly for about 10 times after that, each time it took between 7-8 seconds.

I then ran this from two separate boxes at roughly the same time. No problems, they still ran in about 7-8 seconds from both boxes.

Though, I'm not sure if this is replicating the conditions I'm having. Connecting to an Access 2007/2010 backend creates a lock file .laccdb. It's when this file is present and multiple users are in the database that the slowdown happens. Even if the other user disconnects and only one user remains, the performance is still slow until the last user disconnects and the .laccdb file is closed.

I feel the issue I'm having probably has something to do with our fileserver setup or LAN.

 

[zpy2]

Even if the other user disconnects and only one user remains, the performance is still slow until the last user disconnects and the .laccdb file is closed
Yes, the .laccdb is the lock file. This means the accdb file still live which affects the performance.
as to data aspect, mamy codes in local table which can not be found in remote table also possible influence the performance.

so,VBA loop probably better than link table and query here.

hope Some one test in LAN environment rather than virtual machines ,and make sure whether you have to some with your file server.