Solved

Microsoft Access performance is slow

Posted on 2016-10-01
10
100 Views
Last Modified: 2016-10-06
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
Comment
Question by:J.R. Sitman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41825191
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
 

Author Comment

by:J.R. Sitman
ID: 41825411
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41825435
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41825436
<<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
 
LVL 20

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41825654
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
 

Author Closing Comment

by:J.R. Sitman
ID: 41832272
After getting your suggestions, I did reduce some of the indexing.   Also I found that turning off the automatic spell checking helped.

Thanks
0
 
LVL 57
ID: 41832277
<< 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
 
LVL 75
ID: 41832372
+1 on turning off Subdatasheets !
0
 

Author Comment

by:J.R. Sitman
ID: 41832376
thanks
0
 
LVL 20
ID: 41832934
you're welcome ~ happy to help
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question