We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Microsoft Access database get dramatically slower as day progresses

High Priority
198 Views
Last Modified: 2019-05-05
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.
Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
How exactly the users access the FE+BE ?
J.R. SitmanIT Director

Author

Commented:
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 TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Then you "load" the system with ms Access instances....each logon just add a bit more and a bit more...
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Is the BE also Access, or is it on SQL Server?
J.R. SitmanIT Director

Author

Commented:
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. SitmanIT Director

Author

Commented:
BE is Access
Olivier MARCHETTACitrix Support and Infrastructure Engineer
CERTIFIED EXPERT

Commented:
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. SitmanIT Director

Author

Commented:
it is not SQL
Olivier MARCHETTACitrix Support and Infrastructure Engineer
CERTIFIED EXPERT

Commented:
Sorry I misread the question.
J.R. SitmanIT Director

Author

Commented:
NP
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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. SitmanIT Director

Author

Commented:
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
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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. SitmanIT Director

Author

Commented:
I will make note of the sizes.

How do I check for orphan processes?
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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 PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
If they get a new copy every day and it slows as data is accumulated:  Indexes.

Look at your queries and how the database is used and created indexes where appropriate.  Sorry there really isn't a science to them.  Look for columns used in the WHERE clause and how tables are joined.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
J.R. SitmanIT Director

Author

Commented:
So you are saying I need more indexes?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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. SitmanIT Director

Author

Commented:
got it.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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. SitmanIT Director

Author

Commented:
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 PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
It may be worth testing with the BE locally just to eliminate the Network as a potential source of problem.
J.R. SitmanIT Director

Author

Commented:
I will consider it after we do some more testing. That's not a simple thing to do
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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.
CERTIFIED EXPERT
Top Expert 2014

Commented:
Are there temp tables and temp queries created during the active user period?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.
CERTIFIED EXPERT
Distinguished Expert 2017

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

Author

Commented:
@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.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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. SitmanIT Director

Author

Commented:
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)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.
J.R. SitmanIT Director

Author

Commented:
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)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<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)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<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.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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. SitmanIT Director

Author

Commented:
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. SitmanIT Director

Author

Commented:
do you mean to give them a new FE midday?   If so yes I can.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<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. SitmanIT Director

Author

Commented:
ok
CERTIFIED EXPERT

Commented:
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

J.R. SitmanIT Director

Author

Commented:
not that I am aware of.  How can I check?
CERTIFIED EXPERT

Commented:
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. SitmanIT Director

Author

Commented:
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.
CERTIFIED EXPERT
Distinguished Expert 2017

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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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.
J.R. SitmanIT Director

Author

Commented:
@pcelba    Today it has slowed down again.   Not dramatically but slow.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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. SitmanIT Director

Author

Commented:
Thanks, John
CERTIFIED EXPERT

Commented:
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 TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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. SitmanIT Director

Author

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.