• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

Microsoft Access performance is slow

We use Access 2013 on Citrix and the Citrix servers are load balanced so Access is evenly distributed.  Citrix is run over a dedicated MPLS of 3 gps.

Users average 6-8 per server.  Over 5 the performance is degraded.   The memory on both servers never goes over 30% usage.  On small occasion the CPU will jump to 85%, but it's brief.

I'm looking for any suggestions that might help with the performance
0
J.R. Sitman
Asked:
J.R. Sitman
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
Gustav BrockCIOCommented:
You don't tell how you run the application and what database you share.

A proven method is to use SQL Server for the backend database and (silently) distribute the frontend to the user's local environment. You can study the article here for ideas:

Deploy and update a Microsoft Access application in a Citrix environment

/gustav
0
 
J.R. SitmanAuthor Commented:
Not sure what you mean "how we run the application".
We aren't using SQL.
Each user has a frontend on the remote server.
0
 
Gustav BrockCIOCommented:
The sad situation is that you may not be able to do anything at all. Citrix admins usually have little understanding not to say passion for Access. Thus, adjusting something related to file sharing - which probably is what your issue is about - in a Citrix environment is normally a no-go.

/gustav
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I'm looking for any suggestions that might help with the performance>>

  There are a multitude of things that can be done.  But for a start:

1. Make sure you keep a connection to the BE from the FE for the life of the app.

2. Check your indexing (whole subject onto itself).   Over indexing can be just as bad as under indexing.   Columns with low cardnality (ie. a yes/no column) should not be indexed.

3. Look at the app.  Most Access performance issues are related to development.   Things like using Domain functions (DLookup()) inside of a query.   Or using multiple Dlookups() instead of opening a recordset and fetching a record once, or not modifying a forms recordset to load related table data.

  Locking is another one.   Use Transactions set to no on some queries can boost performance considerably.

4. Make sure the DB's are not being virus scanned.

5. On Citrix, watch out for references in VBA and pathing in general.   Pointing to something like a bar code lib can end up with every user pointing to the the DB file (i.e. C:\Program Files\abarcode.MDA).

 You also say your load balancing servers.....where are the FE's stored?   If their being hauled over the network, that's not good.

6. Make sure your compacting the BE.

Jim.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
it is also best to use short fields for linking. Autonumber/Long Integer gives good performance. Text fields that are used shouldn't be allowed to be 255 characters -- resize them to be just what you need. And while I can't say for sure, my guess is you need to delete the Index/RelationshipWithRI (since that creates hidden index), compact/repair, then recreate the Index(es)/Relationships after resizing if you don't notice a gain in performance.
0
 
J.R. SitmanAuthor Commented:
After getting your suggestions, I did reduce some of the indexing.   Also I found that turning off the automatic spell checking helped.

Thanks
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<< Also I found that turning off the automatic spell checking helped.>>

 That is a biggie and I was remiss in not mentioning.

 Along the same lines, subdatasheets should be turned off as well if you don't use them.

Jim.


Sub TurnOffSubDataSheets()

    Dim MyDB As DAO.Database
    Dim MyProperty As DAO.Property
    Dim propName As String, propVal As String, rplpropValue As String
    Dim propType As Integer, i As Integer
    Dim intCount As Integer

10  On Error GoTo tagError

20  Set MyDB = CurrentDb
30  propName = "SubDataSheetName"
40  propType = 10
50  propVal = "[None]"
60  rplpropValue = "[Auto]"
70  intCount = 0

80  For i = 0 To MyDB.TableDefs.Count - 1
90      If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
100         If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
110             MyDB.TableDefs(i).Properties(propName).Value = propVal
120             intCount = intCount + 1
130         End If
140     End If
tagFromErrorHandling:
150 Next i

160 MyDB.Close

170 If intCount > 0 Then
180     MsgBox "The " & propName & " value for " & intCount & " non-system tables has been updated to " & propVal & "."
190 End If

200 Exit Sub

tagError:
210 If Err.Number = 3270 Then
220     Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
230     MyProperty.Type = propType
240     MyProperty.Value = propVal
250     MyDB.TableDefs(i).Properties.Append MyProperty
260     intCount = intCount + 1
270     Resume tagFromErrorHandling
280 Else
290     MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets RoutineName."
300 End If
End Sub
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
+1 on turning off Subdatasheets !
0
 
J.R. SitmanAuthor Commented:
thanks
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now