Microsoft Access database get dramatically slower as day progresses

How exactly the users access the FE+BE ?

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.

Then you "load" the system with ms Access instances....each logon just add a bit more and a bit more...

Is the BE also Access, or is it on SQL Server?

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.

BE is Access

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.

it is not SQL

Sorry I misread the question.

NP

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

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

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?

I will make note of the sizes.

How do I check for orphan processes?

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.

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

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?

So you are saying I need more indexes?

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.

got it.

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

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.

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.

It may be worth testing with the BE locally just to eliminate the Network as a potential source of problem.

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

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.

Are there temp tables and temp queries created during the active user period?

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.

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

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

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.

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.

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.

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.

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

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

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.

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.

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

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

ok

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)

Select allOpen 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

Select allOpen in new window

not that I am aware of.  How can I check?

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

Select allOpen 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

Select allOpen in new window

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.

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

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

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.

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

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.

Thanks, John

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

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.

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

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.

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.