Avatar of J.R. Sitman
J.R. Sitman
Flag for United States of America asked on

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

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?
Microsoft OfficeCitrixDatabasesMicrosoft Access

Avatar of undefined
Last Comment
J.R. Sitman

8/22/2022 - Mon
Dale Fye

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. Sitman

ASKER
Local.

The FE and BE are located on a separate server, not the Citrix server
John Tsioumpris

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)
Your help has saved me hundreds of hours of internet surfing.
fblack61
Gustav Brock

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)

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. Sitman

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gustav Brock

Jim Dettman (EE MVE)

<<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 Tsioumpris

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
J.R. Sitman

ASKER
How do I find out on the local database server how many people are using the database?
J.R. Sitman

ASKER
ignore my last post
John Tsioumpris

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
J.R. Sitman

ASKER
Thanks, but yes way to much work.
J.R. Sitman

ASKER
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)

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
J.R. Sitman

ASKER
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 Fye

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. Sitman

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
John Tsioumpris

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. Sitman

ASKER
speed was between 50-70 MB/s
J.R. Sitman

ASKER
Also, the Database server is a Hyper-V.  I have it set with 2 processors.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
John Tsioumpris

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 Tsioumpris

Is the citrix server hosted on the same host as the database server ?
J.R. Sitman

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
J.R. Sitman

ASKER
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 Tsioumpris

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. Sitman

ASKER
I will do some testing when I am in the office on Tuesday.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
J.R. Sitman

ASKER
Would you know what the speed of the NIC card is in a Dell Optiplex 3050?
ASKER CERTIFIED SOLUTION
J.R. Sitman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question