Network Split Database Slowness- with a twist! (1 Viewer)

[Clem Fandango]

i guys,

I've got a very specific split database/network problem. I have created a front end/back end solution and when I install the back end onto the company C: drive and the front end anywhere else (eg. other partitions on the server with a different drive letter) everything is fine and snappy.

If I reverse the arrangement, so that the back end is on another partition (and even with the front end on the same partition), the database still works but slows down like crazy. It's like it really wants to see the back end with the drive letter C:, but I can't figure out why. All partitions have the same permissions with full modify etc. (and as I say everything still works, it's just really slow).

If I open the same database unsplit on the other partition (and I've tried a few different ones), it runs fast and snappy. It just seems to be linking from C: back to something else that is the problem. I wonder if anybody has experienced this weirdness?

 

[Gasman]

When you say partition, is that a drive split into separate partitions just because it is one large drive, or just different drives on the server?

A disk only has one set of heads, so if the first option, you have BE on one partition and the FE on another, but it is still the same disk, so the heads are going to be working overtime.

Really they should be on separate disks. The FE is normally on a local machine for the user and the BE on a sever somewhere.?

Every user needs their OWN copy of the FE, not sharing it, so should never be on the server.?

 

[Gasman]

Crossposted spotted after my reply https://www.accessforums.net/showthread.php?t=83511

 

[Clem Fandango]

Sorry about the cross-posting, I'm a newbie and I didn't realise it was a no-no. I'll never do that again! Thanks so much for your response. We have one server disk (Windows Server) with partitions. I do want to have local FEs but this problem is preventing me, because the BE has to be on the C: drive of the server (otherwise the slowness) and obviously that can't be linked from a client, because it would just point to the client's local C: drive. Maybe I'm being stupid?

 

[Gasman]

Not so much a No No, just advise the other sites.
That way people do not waste their time repeating the same advice, and can also see what has already been suggested.

The C: drive of the server and likely the folder will be mapped along the lines of P:\ (for Public) for the network users? It should not be in the root.?
Even if you do not map and use the UNC path, it will just be a folder on that server. That is how I had mine setup in my last place of work.

You really should have local FEs and each user has their own. I cannot stress that enough. You will see that mentioned time and time again here and on other sites.

Even if it was one user and both FE & BE were on the sever, you are increasing the network traffic I believe.

It is a bit like copying files from one server to another using your computer.
The file has to come down to your computer over the network, and then back up again to the other server.
Much better to copy from one server to the other using one of the servers.

 

[Clem Fandango]

Again, thanks so much for your response. It makes sense, and I really do want to have a local client on each computer like I had on the old server. We had it set up on the old server mapping to Z:, which seemed to work fine. I'm not sure where Z:\ actually was, perhaps it worked because it was mapping to the C: drive of the old server. I'll get the IT guys tomorrow to give me a map (P or something else) to the C: drive of the new server and see how that goes- thanks for the suggestion. By the way, we have always had some small number of users using the same front end because they access by RDP while travelling or in our tiny office in Berlin. It hasn't been a problem yet (about 8 years) but I agree it's not ideal and it is only a few people at a time.

 

[Gasman]

I have never shared a FE, so all I can say, is you have been very lucky.
Take note of the actual UNC path that the drive letter is mapped to, as if for any reason a user *might* use P: for a different mapped folder, then the UNC path would be better for linking. I would have thought that all your IT people had to do was map the new folder to Z.
In my last place of work, the new server was backed up to the old server as that was backed up to the cloud.

When a windows update buggered up the new server, I had the users just map to the old server whilst I rebuilt windows on the new server.

I will however add a little warning, however slight the chance it has occurred with you.

I once worked in a satellite location to the main work building. We had our own server on the same desk as my local computer. (I Changed the backup tapes). We also had three other computers for users in the pod.

Access to that networked server was painfully slow, even though I could touch it from my seat.

Turns out after complaining, that the network traffic was going all the way to the main building 20 to 30 miles away and then coming back to the actual server. Once the network guys configured the network correctly, well then it was blindingly fast.

Good luck anyway.

 

[The_Doc_Man]

OK, at least some of your problem is file locking. (I'm not saying "all of it.")

When you open a file under Windows, regardless of the application, you get a file-system lock on the file. This is to prevent someone from yanking that file out from under you by deleting it or erasing its contents while you are working. That's true even for NOTEPAD files.

File locks are managed by the Windows running on the machine where the files are located because that is the instance of Windows that is actually running the flle system there. If you open a file locally, your machine is handling the locks. If you open a file remotely, the remote machine is handling the locks. Locally, file locks take one system operation based on data that is in the same memory array as your program is using. Time to evaluate/set/release/modify local locks is a few thousand nanoseconds (on a 1 GHz machine). Time for that same work remotely? Milliseconds, exact number depending on network speed.

Now start sharing the file in question. Every interaction now has multiple locks - sort of like a skier on a slalom course. Things to go through, things to avoid... it is not a straight path to get to a file. But wait, it gets better (or worse, depending on your viewpoint...)

Access takes out its own locks because the Windows locks are whole-file locks. Access delves into parts of the file so needs to track the parts that are being locked. For security-technical reasons, Windows won't share that kind of information, so Access writes the .LDB (locking) file to track what parts are busy. But if it is a remotely shared filed, the lock is in the same place (including same folder) as the locked file. And it is a file, so you just multiplied all of those interactions by two (because 2 files). AND the complexity of the locking goes up for each user attempting simultaneous file operations. I won't say it is a factorial function but it IS at least a geometric-growth problem.

When you share a front-end, your users are most likely to step on each other. They will want to use your opening form or your switchboard or whatever common control method you use to prevent total chaos. They will want to use the pre-defined forms and reports rather than trying to "roll their own." So that means you not only have lots of users, but they will be using the same thing, which leads to complex lock arbitration.

If your FE and BE are on the same server, even if on different disks of that server, you just doubled the complexity AGAIN. So what can you do?

If you assure that users work from a private copy of the FE file, then there can be no lock collisions for anything in the FE file because each person is touching a different file. No possible conflicts to resolve. No way to cause corruption from lock collisions in the FE file. And if your queries and forms use optimistic locking or no locks at all, you minimize the likelihood of lock collisions in the BE file. If you avoid recordset operations in code (i.e. use SQL whenever possible) then you minimize the window of opportunity for conflicts to sneak in.

Moral of this long-winded story? NEVER EVER allow direct sharing of an FE file.

 

[Pat Hartman]

To be clear, using RDP does NOT change the one user, one copy of the FE "rule". Every RDP user has a personal directory. Have the batch file or whatever distribution method you use, copy the FE from the master folder to the person's personal directory.

Also, NEVER run the Access FE from a server if you can avoid it (RDP is different since both the FE and BE are actually on the same server and so is your session so there is no network interaction using RDP or Citrix). When you open a FE stored on a server drive, it gets loaded into memory on the local computer. It is more efficient to have the FE file local to the PC that is running it.

 

[Cotswold]

Access can give speed problems even with a few users. One must-do to solve this is :

In your BE have a small one record table with one field that is unused in normal operations. When the application starts up,
link the field in that table to an always-open form in your FE. Set the linked field on the form to be locked and hidden to
prevent accidental user access. Retain that linked table to the form until the program is shut down by the user.

Also, apart from the usual settings changed in Options for the database, or by using code set off in your opening Module:
Make sure you switch off both Track Name AutoCorrect and Perform Name Auto-Correct.

One other tip is never to run a backup if anyone is linked into the BE.

 

[gemma-the-husky]

@Cotswold

That's a good reminder with respect to the persistent connection. That would definitely cause a slowdown. I always add a button somewhere to check that the persistent connection is still active.

 

[Clem Fandango]

Hi Everyone,

Thanks so much for all your responses. It’s been a while, but that’s because I’ve been doing a lot of testing as and when I get the chance. I reckon I’ve cracked my problem now, and I thought I’d share the results of my experience in case it might help someone.

Firstly, can I say that I understand and agree with you all! I was myself a little confused about what it was that was actually causing my specific issue, but I think I can explain now. As background, I probably need to say that this is not a massive database in terms of data (although some tables might have 70k + records but there are not so many of them). But it IS quite a complex application in terms of the forms. There are some pretty complex 3rd party controls (such as editable Gantt charts) that take data from lots of tables and crunches it a lot before displaying it in a fancy interface. I use a particularly heavy one of these charts as my measure of “how fast is the connection”. I go by what it “feels” like to decide whether or not it is loading in an acceptable amount of time for the user experience. For this heavy chart, when it loads a big project, it “feels” to me like it is OK when it brings it all up within 3-5 seconds. When it gets to be about 7-8 seconds it feels like internet banking in the 90s, and I don’t think it’s acceptable to the user.

So to clarify my original problem (now that I’ve done all the testing and feel I understand it a bit better), I was experiencing horrible slowness on this “canary” chart after I migrated the application to a new Windows Server. It seemed to me like it was setup the same as on the old server, but I couldn’t properly know because I am not the network guy, we use an external company to handle all that. I’m just the “data and systems” guy internal in the company. My problem seemed to be that installing the server on the C: drive of the server was fine, but on any other drive created this “slowness”.

Actually, I can now report what works for us on our network. So it seems to me that it has nothing to do with where the front end client is, and it all depends on the internal table-linking path within the .accdb (i.e. the path you see when you use the “linked table manager”) of the front end. And it is actually different (believe it or not) whether we are accessing by RDP or across the network internally in the office. So specifically:

For accessing by RDP:

• if the path is to a drive that is a “local disk”, then it is fast and snappy
• if the path is to a drive that is a “network share”, then slowness.

This suggests you should make sure the back end is on the C: drive of the server (or proper partition that is a "local disk"), and the front end can go anywhere you like so long as this internal table-linking is to the local drive AND NOT a network share of the local drive.


For accessing across the network in the office (no VPN or RDP)

• the path cannot (by definition) be to a drive that is a “local disk”
• if the path is to a drive that is a “network share”, then it is fast and snappy!

This suggests you can have the front end with the linking path to a drive that is a “network share”, seemingly contradicting the findings for RDP.


MY SOLUTION:

We need both RDP access (for remote users) and local network access (because it is always better). I didn’t want to write 2 different front ends (not because I am lazy but I am trying to avoid a maintenance pain), so I got the external IT guys to give me a full partition (called L:\) on the new server that was just for this application. Then I got them to map the L:\ partition as a network share, (crucially also as “L:\”) on all the local computers. They resisted this at first because apparently it is not “Microsoft best practice” (which is apparently to create a folder on the partition on the server and map that out, for security). But when I explained everything they relented on the basis that this would be the only thing on the partition.

Now I know it’s weird and idiosyncratic, but this is what works on my system. Everything is fast and snappy setup this way. I write this on the chance that somebody else might come across similar issues to me and it might help them. Thanks again for all your suggestions!

 

[Gasman]

They resisted this at first because apparently it is not “Microsoft best practice” (which is apparently to create a folder on the partition on the server and map that out, for security).

Really!
I have always thought the opposite and in the places I have worked, folders have been allocated to different departments?, so my P drive was not the same P drive as another department.?

 

[Pat Hartman]

Does it make a difference if you link using UNC rather than a mapped drive letter?
I agree with Gasman, I've always had faster response using RDP and Citrix than via LAN but then I've always had the BE on the RDP or Citrix server.

Don't forget, you still do NOT want users sharing the FE when using RDP. They still need a copy. Use whatever technique you are using for the LAN, just put the FE in the user's personal dirctory.

 

[Clem Fandango]

Really!
I have always thought the opposite and in the places I have worked, folders have been allocated to different departments?, so my P drive was not the same P drive as another department.?

Hi Gasman, thanks for the reply- actually, I just meant that they told me they preferred not to share the full drive, but rather a folder within the drive. Since my application requires all users to have some level of access to it then I understood them to be saying that it's not good practice to share the whole drive because then the whole drive is accessible (hence they were comforted by my app being the only thing on the partition). They didn't really comment about whether it was shared as the same thing to everyone (although it is, since we are a small company of about 30 people).

 

[Clem Fandango]

Does it make a difference if you link using UNC rather than a mapped drive letter?
I agree with Gasman, I've always had faster response using RDP and Citrix than via LAN but then I've always had the BE on the RDP or Citrix server.

Don't forget, you still do NOT want users sharing the FE when using RDP. They still need a copy. Use whatever technique you are using for the LAN, just put the FE in the user's personal dirctory.

Hi Pat, thanks for the reply. It doesn't seem to make a difference. The UNC path behaves the same as the "network drive" path via RDP on our system i.e. unacceptably slow.

And yes, regarding FE on the server via RDP, I have an exe which first reads the user's windows username and then opens the FE in the folder with the same name as that user. This way I can use a single published app to the exe and so it looks nice and clean wherever they open from (and some are on macs). All I have to do is make sure there is an FE folder for each user, but it doesn't really matter if there isn't because it will revert to the main one if it can't find one for the user.