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

Microsoft Access database get dramatically slower as day progresses

Our database runs quickly first thing in the morning.  As the day progresses it slows dramatically.  

We run it on Citrix.   Only have 15-25 users accessing it per day and those users are evenly divided between 2 Citrix servers.  Each user has their own frontend

I completely realize as the users log on more and more processes, bandwidth etc., are taking up resources.   However, the servers never go above 20-30% CPU or Memory usage.

Servers are one Win 2016 physical machine and one 2016 Virtual

Miriam Bizup has been helping to see if there is any way to speed it up, but I am looking for some other thoughts on what might be the cause.

Let me know if you need any additional info.
CitrixDatabasesMicrosoft Access

Avatar of undefined
Last Comment
J.R. Sitman

8/22/2022 - Mon
John Tsioumpris

How exactly the users access the FE+BE ?
J.R. Sitman

ASKER
The database is located in LA and we have 2 locations that the users connect via an MPLS 20 meg connection via Citrix Receiver.
The FE & BE are located on the same server.
John Tsioumpris

Then you "load" the system with ms Access instances....each logon just add a bit more and a bit more...
Your help has saved me hundreds of hours of internet surfing.
fblack61
Dale Fye

Is the BE also Access, or is it on SQL Server?
J.R. Sitman

ASKER
correct.   However, the majority of the users are logged in by 10 am and the speed is still fine.  As the day progresses it slows dramatically.
J.R. Sitman

ASKER
BE is Access
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Olivier Marchetta

You can run the "sp_whoisactive" query on your SQL server to see how many queries are running or stuck on a dependency.
You could kill some queries to free up some resources and speed.
J.R. Sitman

ASKER
it is not SQL
Olivier Marchetta

Sorry I misread the question.
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
NP
Daniel Pineault

Is the database bloating throughout the day and being compacted at the end (on close or using another mechanism)?

Is there a performance difference for other apps throughout the day?  Say, opening a heavy Excel file, does it take the same time in the morning as at the end of the day (on the CITRIX machine of course)?
J.R. Sitman

ASKER
not sure how to tell if it is bloating.   It is not compacted at night, however, the users get a fresh copy every day.

I will ask about other programs performance
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Daniel Pineault

Bloating can be determined by accessing the size of the files (back-end and front-end) in the morning vs at the en d of the day.  minor bloating is normal and inconsequential, but large file size changes are indicative of design issues and can impact performance.

Have you checked for orphan processes?  Perhaps the db isn't closing automated process or itself properly?
J.R. Sitman

ASKER
I will make note of the sizes.

How do I check for orphan processes?
Daniel Pineault

You're going to have to check the task manager for a user who is seeing such slow downs to see if there are any extra excel.exe, msaccess.exe, ... running.
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

It would be strange if we have orphaned processes ...unless there are application issue and it crashes...and the users just restart leaving the previous instance..
Daniel Pineault

I've seen it before in CITRIX environments.  Some of it can be caused by poor code, and some by user behavior.  It's definitely one of many possibilities, at least worth illuminating.

Also, is the database kept open and progressively slows down, or are your users exiting and reentering the database throughout the day.  If they have it open all day, have you checked if closing the database and exiting the CITRIX session and staring it up again impacts performance at all?
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
J.R. Sitman

ASKER
So you are saying I need more indexes?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

I'm saying that something is causing things to slow down as users input data.  When tuning a database, I first look at inefficient queries doing full table scans when an index would help.

You need to be aware that over-indexing can cause more harm than good.  That is where it becomes more of an art than a science.
J.R. Sitman

ASKER
got it.
PatHartman

Just because you are using Citrix does not alter the way an Access app is configured.  EVERY user needs his own personal copy of the FE.  They should NEVER, EVER share the same FE.  Frequently Citrix admins don't understand this concept.  To them the FE is an application to be shared but we all know that it is a container type file and Access is continually updating the FE depending on what the user does.  So, you end up with 25 people making changes to a SINGLE file and Access needs to track all those changes and make sure that every change gets saved correctly.

Here is the batch file I use with my Citrix installations.  It gives the user a fresh copy of the FE in his own personal directory every time he runs the shortcut.

md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" %USERPROFILE%\DwgLog
%USERPROFILE%\DwgLog\DrawingLog.accdb
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Daniel Pineault

So you are saying I need more indexes?
I'm sure Mariam will check indexing, but a very general rule of thumb is an index on any field used to filter (WHERE, HAVING).   Then you fine tune from there.  It is a balancing act between query performance and overall performance at times.  Too many indexes can slow down inserts/updates, ...

Does you FE create a persistent connection with the BE at the startup?
Everything FE/BE are running locally to the CITRIX server, ideally the BE is directly on the CITRIX server thus eliminating any network aspect between the FE and BE.
J.R. Sitman

ASKER
I will have her check it but she is really busy with work.
Yes the FE has a persistent connection
No, the database is on a dedicated file server not on the Citrix server.  However, it has always been that way.
Daniel Pineault

It may be worth testing with the BE locally just to eliminate the Network as a potential source of problem.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
J.R. Sitman

ASKER
I will consider it after we do some more testing. That's not a simple thing to do
John Tsioumpris

Essentially you have a trade-off situation..
You get the best performance because in the end everything is local (FE/BE physically are on the same machine) but on the other hand you get issues because you load too many instances of Ms Access on the same machine.
On the other hand you would have the normal setup ..a FE that is copied locally on each user's workstation and a BE server that serves all the users ...getting the Networking penalty which depending of the design of the application both on FE/BE , network, tuning,tweaking ,workflow can be significant.
aikimark

Are there temp tables and temp queries created during the active user period?
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
slightwv (䄆 Netminder)

I don't understand all the nuances of Citrix and I agree with the FE should be local but given the information stated in the question:  Things start out fine and slow down as the day progresses.

If it was a FE being "shared", I would expect it to start out slow and continue slow.  Since it slows down over time, I'm leaning towards SQL/data not configuration.
PatHartman

@JR,
I asked earlier if the FE was shared or if each user had his own personal copy.  Please respond.
J.R. Sitman

ASKER
@Pat, sorry I missed it.  Yes, every user has their own frontend.   We have two Citrix servers.  Friday I put the Virtual one into Maintenance mode.  Yesterday and today the speed of the database was fine.  However, on the weekends there are approximately 5-8 fewer people using it.

Your thoughts on could it be a Citrix server problem.  It has 66 meg of memory, 4 processors and 63 gigs of free HD space.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PatHartman

Do you really need two servers for 25 people?  If not, try putting them on on the same server with the BE.  That eliminates all network latency.
J.R. Sitman

ASKER
we don't.  It just gives me immediate failover if one goes down.  And one has.

I know it's been suggested to put the database on the Citrix server, but I'm not sure I think that is the issue.  It has been on a separate server for the past 18 years.
Jim Dettman (EE MVE)

Late to the party, but:

1. I would second another pass through on the indexing just to double check more than anything.   The "gets progressively slower as the day goes on" is a usual tip off, BUT if you come in the following morning not having done a Compact and Repair, then this is probably off the table.   The searches would be just as poor from the previous day.

 There is an outside chance though that your user load/activity increases as the day progresses, so it's not out of the question and worth a double check.

2. I would monitor the servers closely throughout the day.   CPU utilization, free memory, disk queue length, and network bandwidth.

  Something may be getting overworked as the day goes on.

 With situations like these, I like to ask "What's changed?"    You've commented a couple times that you've had this setup for a while without issue if I am understanding correctly.   So is it more users, recent server swap/os upgrade, etc?   Any changes in the app?

Jim.
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
I will continue to monitor the servers.

One change is the virtual Citrix server is new.  Also, I increased its processors from 3 to 4.   I have seen a few opinions that 4 is sometimes not a good idea.
The database has had changes over the years and I know that some have slowed it down by a few seconds.  What's happening now is it has slowed by 1 minute to move through fields, according to the users.  
I do not do a compact and repair nightly but the users do get a new FE every day.

I do not have the knowledge to check the indexes and Miriam Bizup is traveling this week.  Is it possible for someone to check the FE?   I am not sure how I could provide the BE without some type of signed agreement to protect the customer data.   We do not store anything except names and addresses.
Jim Dettman (EE MVE)

<<One change is the virtual Citrix server is new.  Also, I increased its processors from 3 to 4. >>
<<I do not do a compact and repair nightly but the users do get a new FE every day.>>

  Um....problem is a lot of things at play here.   Really requires some investigation or you could be jumping through a lot of hoops for nothing.

<<I do not have the knowledge to check the indexes and Miriam Bizup is traveling this week. >>

 Something you could live with for the week?   I'd drop her a line and see even if traveling, she might be able to spend a little bit of time on it.   Would probably be better to have someone familiar with the app to work with it rather than someone brand new.

<< I am not sure how I could provide the BE without some type of signed agreement to protect the customer data.   We do not store anything except names and addresses.>>

  Certainly would be willing to sign a NDA here as I'm sure some of the other would be willing to do so as well.

Jim.
Jim Dettman (EE MVE)

<<One change is the virtual Citrix server is new.  Also, I increased its processors from 3 to 4. >>

and on this, I think I would just spot check with task manager every half hour or so and see if there is a vast difference between early and late in the day.

  It's the chicken and the egg thing though; is the app (and possible changes) driving the problem for the server, or is a problem with the server driving the app performance problem?

Jim.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jim Dettman (EE MVE)

and BTW, on this:

<<I do not do a compact and repair nightly but the users do get a new FE every day.>>

 can you make that happen later in the day and see if the problem clears up as a test?

Jim.
J.R. Sitman

ASKER
Thanks, Jim.   You are correct, it can wait a week.  

If I need someone else to look at it I will let you know.  

The speed this weekend was good after I put the Virtual Citrix server in maintenance mode.  Still testing to see if that actually help or just a coincidence.

Monday and Tuesday there are fewer users so results on those days won't tell me much.
J.R. Sitman

ASKER
do you mean to give them a new FE midday?   If so yes I can.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jim Dettman (EE MVE)

<<do you mean to give them a new FE midday?   If so yes I can>>

  Yes.  Wait until performance is an issue, then update the FE's without compacting the BE.

Jim.
J.R. Sitman

ASKER
ok
Lambert Heenan

At the risk of raising something that might be obvious, and so overlooked, does your application open a database object for each back end .accdb/.mdb file involved?  See here for instance.

Typically I do this in the Open event of a hidden form, launched at start up.

      
Dim DbCon as DAO.Database
Set DbCon = OpenDatabase(FullPathToDbFile)

Open in new window


And then in close event of the form, which fires when the application closes down, the database object is freed up.

      
DbCon.Close
Set DbCon = Nothing

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
J.R. Sitman

ASKER
not that I am aware of.  How can I check?
Lambert Heenan

Well I would look to see what the AutoExec macro is doing, if it exists. That's one place to load a hidden form. Or check the opening code for any default form that may be set (Options/Current Database/Display Form)

If you do not see anything like it (written by a previous developer?)  then it's very simple to add. Then see if performance picks up.

Create an Autoexec macro, or add to the existing one, such that the first step is an OpenForm action, and specify the form name, and the window mode as 'Hidden'

In the aforementioned form, define code for the Open event and the Close event.
Private Sub Form_Load()
    InitializeApp
End Sub

Private Sub Form_Close()
  EndApp
End Sub

Open in new window


And those two subs being called would be in a module and look something like this...

Private theLinks() As DAO.Database ' a module scope variable

Sub InitializeApp()
' opens connections to all the linked access files to improve performance
' we can find all the linked Access tables in the table MSysObjects
    Const ACCESS_LINKS = "SELECT DISTINCT MSysObjects.Database FROM MSysObjects WHERE (((MSysObjects.Database) Is Not Null));"

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim linkDb As DAO.Database
    Dim nLinks As Long

    Set db = CurrentDb
    Set rs = db.OpenRecordset(ACCESS_LINKS, dbOpenDynaset)

    While Not rs.EOF
        Set linkDb = OpenDatabase(rs!Database)
        ReDim Preserve theLinks(nLinks)
        Set theLinks(nLinks) = linkDb
        nLinks = nLinks + 1
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Sub endApp()
' close all the previously opened Access databse connections
    Dim db As DAO.Database
    Dim n As Long
    For n = LBound(theLinks) To UBound(theLinks)
        Set db = theLinks(n)
        db.Close
        Set db = Nothing
    Next n
End Sub

Open in new window

J.R. Sitman

ASKER
today will be the 7th day that we have been running only on the physical Citrix server.  So far the input I have gotten from the users is the speed is normal, no significant slow down.

Those of you that use Citrix, can I get your feedback?   Could it be the Virtual server is the problem.   Also I am waiting for Miriam to have time to review this post.
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
PatHartman

That's a fair assumption.  Perhaps there is something wrong with how the virtual server is configured.
J.R. Sitman

ASKER
Thank you all for a lot of good advice.

Miriam believes the indexes need some cleaning up.   She also pointed out that the main form has a lot of fields that do calculations every time a user accesses a record.

Thanks
Pavel Celba

Hi all folks,

I am just monitoring this question because my experience with the Citrix is close to a nightmare. We are just not using Access.

I don't think the problem is caused by indexes because the application was slow at evenings and fast on mornings w/o any maintenance.

Also in some of previous posts is written: "… 7th day that we have been running only on the physical Citrix server …  the speed is normal". There is no index optimization done in the app.

So it must have been Citrix in conjunction with virtual server. Thus the answer selected as the solution is not the real solution in fact and it should be reconsidered. OTOH, I have nothing against indexes optimization.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
J.R. Sitman

ASKER
@pcelba    Today it has slowed down again.   Not dramatically but slow.
John Tsioumpris

Based on the accepted solution i must say that Access is a different philosophy than SQL ( a  quick search in the Internet will show more info)..in SQL index optimization can mean the world in performance tweaking but in Access pretty much is internal and the only optimization is done via C+R so unless you have a maintenance routine to do it in intervals there isn't much to do...and unless the database is heavily hammered it won't make that big difference..
There is too little control over the index...in the concept that automatically is set by Access ...you might add one or two but in the end it boils of how well (normalization) your table design is .
It would be more interesting to check on the separate machine FE/BE scenario  to see if it really affects performance and if it gets degraded during the day.
J.R. Sitman

ASKER
Thanks, John
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
Pavel Celba

Yes, we may add one or two indexes after app queries analysis and compact the database each night which is the standard Access database maintenance process and which is not done most likely.

The slow speed at evenings and normal morning speed is not explained at all. Neither proper indexes existence nor their absence explains this behavior. And that's the main reason why I am saying the accepted solution is selected incorrectly.

If the Citrix environment is slowing down during the day then you have to monitor the whole environment. What is the CPU load, what is the RAM consumption. What is the hardware resources overload when running in VM environment? Etc. etc.

IMHO, Jim Dettman provided the most valuable answers and I am still waiting you'll respond to his very important requirement:
"Wait until performance is an issue, then update the FE's without compacting the BE."
John Tsioumpris

C+R on the FE will only affect performance if there are a lot of temp tables that store info and thus causing the FE to behave slower...if there aren't too many temp tables and the size remain typically the same then the issue is only related to the BE.
slightwv (䄆 Netminder)

>>The slow speed at evenings and normal morning speed is not explained at all.

I based my post on this information from J.R.

but the users do get a new FE every day.

If they get a new FE DB and it performs well and slows over the course of the day, I assumed it was with the FE slowing things down.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PatHartman

today will be the 7th day that we have been running only on the physical Citrix server.  So far the input I have gotten from the users is the speed is normal, no significant slow down.

Those of you that use Citrix, can I get your feedback?   Could it be the Virtual server is the problem.   Also I am waiting for Miriam to have time to review this post.
Based on this comment, I don't think the correct solution was chosen.
J.R. Sitman

ASKER
Let me apologize to all that participated.  After taking the Virtual server out of maintenance for one day it was fine today it was a nightmare for the users.  Extremely slow.  

I spent many hours monitoring the server performance and on several occasions, one single instance of Access was using 25% of the servers CPU.  Sometimes 2 instances.

However, the advice given was still very helpful in looking for any database issues.

I hope this corrects my premature selection of the correct solution.  Sorry.