Microsoft Access Database is slow locally and fast via Citrix. Why?

J.R. Sitman
J.R. Sitman used Ask the Experts™
on
We have an Access database on a Win 2012 R2 server locally.  The backend and frontend are separate and each user has there own frontend.  

When they use it from the office where it is stored the performance is very poor.

When they access it via Citrix, it is extremely fast.  

This makes no sense to me.  Why is it faster over an MPLS connection?

How can I improve local performance?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Is the Win12 server located in the same building  and on the same LAN as the local workstations? or is it over a WAN?

Access generally, does not operate well over a WAN, unless you have a very high speed WAN.  Generally, when you use Citrix, you are logging into a server on which the Access FE and BE are both installed, or at least which are co-located, so the round trip from the FE to the BE is significantly quicker.
J.R. SitmanIT Director

Author

Commented:
Local.

The FE and BE are located on a separate server, not the Citrix server
John TsioumprisSoftware & Systems Engineer

Commented:
Can you give a bit more clear info on how you are connected to BE server...wired/wireless ?...if its wireless just forget it Access doesn't play nicely with Wifi ...also if you are operating via e.g 100Mbps network and the Citrix <-> BE server is connected via 1000Mbps its again natural the Citrix to be much faster ...we are talking 10x speed difference...(minus other factors like latency, hops..etc)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I've seen the same. Not that the off-Citrix scenario was slow but, run inside Citrix, the application was extremely fast.

I couldn't get a definitive answer why, but Citrix environments are often built from hi-spec computer and network hardware scaled to perform at peak workload while, most of the time, the workload is much lower, thus the single user experiences very good performance.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
This makes no sense to me.  Why is it faster over an MPLS connection?

 To add a bit to what gustav said, when in a Citrix situation, only KVM (Keyboard, Video, and Mouse) data runs over the connection to your PC.  The server does the actual processing of the DB data.    The other piece is that Citrix servers have a lot of optimizations built-in for the desktop experience, because they don't always run over fast connections.

 But when your on the office LAN the application is run on the PC and the actual data from the DB file is being read/written and processed by the PC.  There is not the same optimizations either.  I.e. you still running at highest resolution, highest color settings, etc.  As far as Windows is concerned, the PC is running fine.

Jim.
J.R. SitmanIT Director

Author

Commented:
All computers a wired to the network.

We have used Citrix for many years and it has always been faster.


Is it possible to have the local users access the database via Citrix?   I have never tried this.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<Is it possible to have the local users access the database via Citrix?   I have never tried this.>>

 Sure.   Just setup a RDP connection to the server.   If you have a later OS (i.e. 2008 and up) you can do it as a published app and just have an icon on their desktop.

Jim.
John TsioumprisSoftware & Systems Engineer

Commented:
If you have a few users it rather clear that you will have benefits ...but as the number of users increase the performance will decline....although its not clear enough it seems the Citrix server is more powerful than your average workstations and given the fact various optimizations it performs much faster especially if accessed by few users but personally i would invest more time on optimizing the design of the application than relying on a powerful server....eg. a correct index ...or a proper datatype as key for a table could bring a great deal of performance boost..probably if you have data intensive operations its a good idea to start planning for SQL server as your BE
J.R. SitmanIT Director

Author

Commented:
How do I find out on the local database server how many people are using the database?
J.R. SitmanIT Director

Author

Commented:
ignore my last post
John TsioumprisSoftware & Systems Engineer

Commented:
Maybe extreme but maybe just maybe there is a way to identify what citrix does and makes the application run faster ...but it would quite tedious...you require two VMs with identical configuration...one loaded with WIndows Server and one with Windows Server + Citrix...
Granted that actually the application runs faster on Citrix ..(keep everything vanilla ...just WIndows+Access Updates /+Citrix Updates) you could load Process monitor and get the behinds the scenes view...just make a capture doing the exact same procedure...on both machines and compare
J.R. SitmanIT Director

Author

Commented:
Thanks, but yes way to much work.
J.R. SitmanIT Director

Author

Commented:
one thing I noticed today is in the bottom left corner of one of the forms, the "calculating"  runs for quite a while.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
That could be caused by numerous things.

How fast the station is, what else it's doing at the time, etc.  Would take a lot digging unless it's something specific and repeatable.

Jim.
J.R. SitmanIT Director

Author

Commented:
Just did some testing by using the database directly from the server and it was very fast.  When using it from the local computer, not on Citrix, the performance is poor.

So what can be done on the computer to increase performance?  computer data is attached

spcala298.png
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
first thing I would suggest is to make sure that the network switch is operating properly.

I've had situations where the speed on the switch was inadvertently switched to the lowest bandwith possible.  I would also have your IT folks check the network card in the PC, as it could have gone bad as well.
J.R. SitmanIT Director

Author

Commented:
I am the IT Director.  The switches are the unmanaged type so I do not know how to verify the speed.

It is not one computer.   I am doing the testing on 3 of them.  All results are the same.
John TsioumprisSoftware & Systems Engineer

Commented:
For quick and dirty testing :
100 Mpbs LAN you got to have 10-11 Mb/s when you copy a file from one machine to server and vice versa
1000 Mpbs (1 Gbps)  LAN provided you have good HDDs you should see speeds at least 80 Mb/s +
The ping should be always steady below 1<ms AT ALL the time without strange spikes..
Keep in mind that Local database performance is ALWAYS far superior to Network performance...no matter what
J.R. SitmanIT Director

Author

Commented:
speed was between 50-70 MB/s
J.R. SitmanIT Director

Author

Commented:
Also, the Database server is a Hyper-V.  I have it set with 2 processors.
John TsioumprisSoftware & Systems Engineer

Commented:
This is not very good speed.although it will have very small impact in the performance of your application..do remember that for such kind of testing both the server and the workstation should be idle without any background operations ..
John TsioumprisSoftware & Systems Engineer

Commented:
Is the citrix server hosted on the same host as the database server ?
J.R. SitmanIT Director

Author

Commented:
One Citrix server is physical and the other Virtual one is hosted on the same server as the database.

The speed I posted was server to server.   The speed from server to computer is 11 MB/S
J.R. SitmanIT Director

Author

Commented:
in addition, all of the local computers are connected to VOIP phones that are connected to the network.  e.g. phone to wall RJ45 and computer to phone
John TsioumprisSoftware & Systems Engineer

Commented:
So its crystal clear as i wrote in my very first post...your workstations are cabled via 100 Mbps lines and the Citrix/database are hosted on the same host (probably you have them linked internally via 10 Gb virtual switch )...so just for kicks go out and buy a 1 gbit switch to connect workstation/s to the server...i am pretty sure you will get "interesting" speed performance increases..
J.R. SitmanIT Director

Author

Commented:
I will do some testing when I am in the office on Tuesday.
J.R. SitmanIT Director

Author

Commented:
Would you know what the speed of the NIC card is in a Dell Optiplex 3050?
IT Director
Commented:
I set up each workstation to run the FE directly from their local drive.   That solved the problem.   Thanks to all for helping.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial