Access speed on new server much slower than old (2 Viewers)

Alc

Registered User.
Local time
Today, 09:49
Joined
Mar 23, 2007
Messages
2,407
I've found various questions about Access speed over a remote connection but can't find one that quite covers this problem.
Apologies if I've missed it.

Until two weeks ago, we had a load of databases on a shared drive (P:\). They were set up with a shared back end and each user having their own front end.
The P drive is used throughout the company, for a lot of things.

With the whole Covid 19 thing, all users have been working remotely.
The connection speed was fairly slow, so I was able to get our IT support to build a new, dedicated server (Q:\) to host just our databases. I moved all Access front and back ends over and remapped where required, but now the connection speeds are slow to the point of being unusable at times if accessing the databases from outside the company.

I'm hoping someone whose gone through a similar move might have experienced something along these lines and have some words of wisdom about what might be causing the drop in speed?

Thus far:
* IT can't find any network speed difference between the two machines. Not sure how they measure this but they did.

* If using a PC from within the building, the speed is much faster than using the old server within the building, which suggests to me that the drop when accessing remotely may be VPN related but doesn't explain the big drop between the old and new servers
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:49
Joined
Sep 21, 2011
Messages
14,041
Once is a company I was working at, we were at a satellite location, but we had our own server and desktops in the same area in the same building, yet the server was slow as hell.?

Turns out all the traffic was going to the main site and then coming back to the satellite site, instead of going 2 or 3 feet to the server? :D

Not saying this is the case, but that surprised the hell out of me. :)
Might be worth IT checking the network structure/links anyway.

Try your own ping test to the servers anyway?
 

Isaac

Lifelong Learner
Local time
Today, 06:49
Joined
Mar 14, 2017
Messages
8,738
I moved all Access front and back ends over and remapped where required
But the front ends are still on the users' machines, right?
 

Alc

Registered User.
Local time
Today, 09:49
Joined
Mar 23, 2007
Messages
2,407
Once is a company I was working at, we were at a satellite location, but we had our own server and desktops in the same area in the same building, yet the server was slow as hell.?

Turns out all the traffic was going to the main site and then coming back to the satellite site, instead of going 2 or 3 feet to the server? :D

Not saying this is the case, but that surprised the hell out of me. :)
Might be worth IT checking the network structure/links anyway.

Try your own ping test to the servers anyway?
Thanks, I'll try it
 

Alc

Registered User.
Local time
Today, 09:49
Joined
Mar 23, 2007
Messages
2,407
But the front ends are still on the users' machines, right?
Some are on the users' own PCs. Some are on the shared drive.
Normally all would be on one or the other but I'm experimenting to see if it makes a difference and the change is negligible.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:49
Joined
Jan 23, 2006
Messages
15,362
I am not a network person, and don't know if (this LAN vs WAN ) does or could apply to your situation, but this comes up from time to time.
Front ends should be on user's PC --though they may copy a FE from a shared location to their machine at start up.
Good luck --please let us know how things develop.
 

Alc

Registered User.
Local time
Today, 09:49
Joined
Mar 23, 2007
Messages
2,407
I am not a network person, and don't know if (this LAN vs WAN ) does or could apply to your situation, but this comes up from time to time.
Front ends should be on user's PC --though they may copy a FE from a shared location to their machine at start up.
Good luck --please let us know how things develop.
Thanks will do. I assume I can't be the only person this will happen to.
 

Isaac

Lifelong Learner
Local time
Today, 06:49
Joined
Mar 14, 2017
Messages
8,738
Some are on the users' own PCs. Some are on the shared drive.
Normally all would be on one or the other but I'm experimenting to see if it makes a difference and the change is negligible.
Ok, thanks for the response.

I would think, if
- server A is where the backend used to be, people accessing it from home, using VPN
- server B is where the new backend is, people also accessing it from home, also over VPN

...and B is "much slower" than A, then surely the answer must lie somewhere in the server guys' setup, settings & administration....Sounds like the answer will depend on working with them to troubleshoot. Best of luck - that sounds frustrating, but hopefully now that they've invested in creating your dedicated server, they'll be motivated to figure out why it's actually worse than the last one.
 

Alc

Registered User.
Local time
Today, 09:49
Joined
Mar 23, 2007
Messages
2,407
Ok, thanks for the response.

I would think, if
- server A is where the backend used to be, people accessing it from home, using VPN
- server B is where the new backend is, people also accessing it from home, also over VPN

...and B is "much slower" than A, then surely the answer must lie somewhere in the server guys' setup, settings & administration....Sounds like the answer will depend on working with them to troubleshoot. Best of luck - that sounds frustrating, but hopefully now that they've invested in creating your dedicated server, they'll be motivated to figure out why it's actually worse than the last one.
Sorry, I explained that poorly.
The two options are:
Front end back end both on new server
Front end local, back end on new server

Yes, I figure now someone has built it he has his name attached to it, so he'll want it to work even for selfish reasons.
 

Isaac

Lifelong Learner
Local time
Today, 06:49
Joined
Mar 14, 2017
Messages
8,738
Oh. I thought that when things were on the P drive, they were not as slow as they are now.
Sorry I misunderstood.

Well ... Despite the principle of having FE's on local machine, which I espouse, I mean ... If they work better having each person's individual FE (that only they use) on the server, along with the back end ... I don't think there's much harm in doing so.

If anyone corrupts their FE, it'll just be a matter of a quick replacement with the shiny new master dev copy, just like if their FE was on local.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:49
Joined
Feb 28, 2001
Messages
26,999
OK, let's take a shot here at physical network configuration scenarios.

1. Access on local system, front-end on local system, back-end on shared server. This is the preferred configuration. Here is where with judicious use of indexing, you can make a system respond pretty well. All interactions between Access and the FE file are local. File locks within the FE are local. Lock collisions will not occur on local locks because you already have the objects locked. The only locks that are NOT local are those taken out for the back-end objects.

2. Access on remote system, front-end on remote system, back-end on remote system, using terminal services to have a remote desktop session. This should be very fast and will have relatively low network traffic. However, it depends on the ability to have a private copy of the front-end in a user's private profile. If FE files are actually shared, this configuration will be highly susceptible to DB corruption. Depending on the degree of sharing, will have lots of lock collisions.

3. Access on local system, FE on remote system, BE on remote system, NOT using terminal services because Access is local. Here, you run into the potential for a LOT of file locks. Further, since Access needs to pull all of the data across the network AND has to pull structural elements too, this will perform badly. If the FE on the remote system is shared, extremly probability of issues in corruption. Depending on the degree of sharing, will have very high occurrence of lock collisions.

Try to correlate between bad performance and one of those scenarios.

If the old and new servers are still available and visible to your users who are having the slowdown, you need to have them do a PING /n 4 from their computer to each server and take a screen-shot. If they do a PING /n 4 you must disregard the first ping as it is involved in route discovery. Once the route is discovered, all subsequent ping attempts should be more or less the same. That option says "Do 4 pings in a row and then stop." If you do that and there is a difference in the timing of the routes, you have something to take back to the IT guys.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:49
Joined
Feb 19, 2013
Messages
16,553
when working over VPN it is important to minimise the amount of network traffic. Terminal services is the most efficient as very little crosses the internet - just screen refreshes one way and keyboard and mouse movements the other.

If you haven't got terminal services to minimise network traffic, then you need to fall back on web page principles of minimum data - so forms should not be based on tables or queries without criteria. This applies to subforms as well and possibly to combos/listboxes if they return a large number of rows (e.g. a search combo listing 10,000 customers). Note also that the criteria function of openform is actually a filter - so all records are returned - it just displays the ones that are filtered.

Say you have 10000 customers, and you want to find one using a combo - that's 10000 records to be pulled through - perhaps twice depending on how your form works, once for the combo and once for the form. If you know before you populate the form and combo record/rowsource that the name of the customer starts with N then you only need to bring through those customers beginning with N so very simplistically with 26 letters in the alphabet only 400 records need to be brought through - a 96% performance improvement.
 

Alc

Registered User.
Local time
Today, 09:49
Joined
Mar 23, 2007
Messages
2,407
Thanks all.
I've managed to get a dedicated resource from the technical team to look at this, this week.
I did some benchmark tests using various combinations of C, P and Q drive and was able to convince them that a problem exists.
Will post whatever we find.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:49
Joined
Feb 28, 2001
Messages
26,999
Good luck, there, Alc. The biggest part of getting this fixed is to convince the right people that something is broken.
 

TonyE

Registered User.
Local time
Today, 06:49
Joined
Apr 11, 2019
Messages
23
Good luck, there, Alc. The biggest part of getting this fixed is to convince the right people that something is broken.
Best thing we ever did for our global Access systems is utilise Citrix and put both backends and frontends together on shared drive.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:49
Joined
Feb 19, 2002
Messages
42,970
Creating a new server for your Access apps was unnecessary and wouldn't do anything anyway. None of the Access activity happens on the server. It all happens on the PC where the FE is open. Jet/ACE are NOT actual database servers like SQL Server. With SQL Server, most of the data retrieval activity is handled by processes actually running in memory on the database server.

There is some other issue causing the slowness that your network people are going to have to track down.
 

kentgorrell

Member
Local time
Today, 13:49
Joined
Dec 5, 2020
Messages
46
Access BE via VPNs are notoriously slow and apt to corrupt the db.

I'm with TonyE on using Citrix or RDP. Only the graphics need to go over the net.

And I'm with Pat - SQL Server (even Express) is going to be a better solution than trying to pull data from an Access BE over a VPN. With SQL Server you must give it its own resources. Do not try and install it on a machine that is doing anything else.
 

Alc

Registered User.
Local time
Today, 09:49
Joined
Mar 23, 2007
Messages
2,407
Thanks all, finally got something working.
As Doc_Man said, the hardest part was proving to those in a position to fix it that there was a problem (and whose responsibility it should be).

After sending the IT team a lot of sample data, where I'd run comparison tests, they finally agreed there was an issue.
Two weeks later, they agreed with what I said initially - that the speed improvement on the new server is notable when you're inside the company's firewall, but is killed over VPN.
After six weeks(!) of meetings, they agreed to some measures. Two are up and running and the situation is now workable.

In order of simplicity
Option 1: They leased some extra desktop PCs for our team. Four of us already had these, meaning we could connect to those remotely and work as if we were in the office. We now have ten. Some people are sharing, but it's still better than it was.

Option 2: Virtual PCs were set up on an existing server. Multiple people can connect at the same time (unlike the desktops) and the speed is, if anything, faster than being in the office.

I understand that things like Citrix and SQL Server might be far better options, but they're just not available. The IT team has been talking for as long as I've been with the company (5+ years) about getting rid of Access databases and moving to some bespoke system. However
  • There's no budget to pay for someone outside to do the work
  • Nobody who's taken a look at how the databases are interconnected wants to take responsibility for replacing them. Most were cobbled together by people with no experience of Access who just needed to get something working.
  • None of the managers on our team wants them replaced, because then we'd lose the ability to alter them at will or create new ones for small projects

Thanks for the help.
 

kentgorrell

Member
Local time
Today, 13:49
Joined
Dec 5, 2020
Messages
46
Your Virtual Desktops and/or "Virtual" Virtual Desktops is a great solution. Where you don't have an actual server the "Virtual" Virtual Desktops providing RDP is often the best solution. Laptops or PCs aren't expensive so to have on sitting in the office doing nothing but providing RDP access can be very cost effective especially when you take licensing of OS and RDS CALs into account.
Using RDP with either method resolves the sending of lots of data through a VPN. A while since I've used Citix but your RDPs should work just as well.

It does sound like you need to put together a plan to rationalise your dbs so you have good structures, relationships etc before you even think about moving to SQL Server.

And by plan, I mean produce a report on where your dbs are now; your vision of where they need to be; and a plan to evolve them. A plan to make gradual changes will be more likely to succeed and less likely to inflict pain than if you try to do one big fixup.

As professional Access developers, most of our work comes from small apps "cobbled together by people with no experience of Access who just needed to get something working." and then grew to become the backbone of an organisation.

The last thing you wan't to do is take away the ability to create new apps for specific purposes. And isn't Accesss great for that!

But one thing you should be doing is getting everyone to agree on standards and methodologies so that you can work toward consistency.
Consistency in everything from a Corporate Color Scheme to Naming Conventions.

Then Evolution NOT Revolution.
 

Users who are viewing this thread

Top Bottom