Split Access DB slow speed

I have a split MS Access database, with the back-end on a shared network drive and the front-end on each user's PC (about 15 users). Our network is pretty slow, and when loading records to edit it can take up to 60 seconds to load, which seems to get slower the more users are connected to the back-end.

All that is used are forms which use queries as their data source to limit fields, but the query links 3 tables (all via a shared primary key, which is an auto-number on the main table) so that it's editable. Testing seemed to make this faster and less error-prone than using sub-forms. Also I need to filter the query based on fields from 2 of the 3 tables.

The slow-speed is only when querying the data-set, but since it's constantly being edited, I need to re-query every time when moving between records. This is because the query filters to records that have been unassigned, and every time a record is opened, it is assigned to that user.

My question is are there any suggestions for speeding this up? It may be simply that having so many users is causing the slowdown, or that my query is inefficient. I can't post the code (very security conscious company) but am happy to give more details.

I've tried a few suggestions found online, such as keeping a database connection open, but this didn't make any noticeable difference, and running a query on fewer fields (only those necessary to filter) to find the next record, and then just open that record (using a where statement) using the main query, but this seemed even slower. I've also read that if you use fields in your where clause that are not indexed the whole data-set is passed to the client, which then filters it, so I've thought of indexing every field in the where, but haven't tried this yet.

Apologies if I haven't given enough details.
LVL 2
Andy CownieImplementation SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Indexes will help ... if the tables have relationships with Referential Integrity (RI) enforced as, Access builds a hidden index on the foreign key fields, so creating a visible one is not necessary.

So the first step would be to create relationships with RI

You are already using an AutoNumber in the main table -- good.

Make sure the Foreign Key fields are Long Integer data type.

If you cannot enforce RI on the relationships, check to see if the Default Value for the FK (Foreign Key) is 0 and if it is:
1. delete the Default Value, and
2, delete all the values that are 0

Next, it will really help a lot if search fields are indexed.  For text fields, make sure the allowable size is not 255!, which is the default.

If you do not know how long your text fields need to be, run the free Analyzer for Microsoft Access here:

analyzer.codeplex.com

On the Deep Analysis report (which is like a data dictionary on steroids), right after the data type for text fields will be the size the field is allowed to be and the next number is the maximum characters that have ever been used.  Resize your fields before building your indexes.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Luke ChungPresidentCommented:
Have you opened a connection to the linked database when your app starts? That'll make s big difference. Also make sure you have subdatasheets set to none.

More info in my papers.
http://fmsinc.com/MicrosoftAccess/Performance/LinkedDatabase.html

http://fmsinc.com/MicrosoftAccess/Performance/subdatasheet/index.htm
ste5anSenior DeveloperCommented:
Our network is pretty slow.

What does this mean? When you're working over a 1MBit line, then you cannot do that much, except:

Place all queries in the front-end. Copy at application startup all static lookup tables into your front-end. This minimizes the needed network traffic and is easy to implement. The only thing you need to consider: Users are no longer capable of editing those lookup tables. Thus you need a separate administration process to maintain this kind of data.

Another point to gain performance: Link your database using UNC paths instead of mapped drives.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
also use short paths whenever possible -- so instead of putting a database in a bunch of nested folders, put it as close as you can to the root.
Luke ChungPresidentCommented:
How much data are we talking about?
Dale FyeOwner, Developing Solutions LLCCommented:
I like the concept of:
1.  Getting the PK of the table that indicates the next record that is unassigned.  I would generally do this by opening that table in a recordset where Assigned = False (or some such construct).  Then immediately set the [Assigned] field to whatever value is appropriate, then make sure you move back to that record (rs.bookmark = rs.LastModified), and then read the PK of that record.
2.  Then go back and retrieve the rest of the information you need from the other tables using just this PK value.

Have you considered moving your data to SQL Server or SQL Server Express?  This would allow you to do much of the work on SQL Server and avoid having to pull large volumes of data across your network.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Do your queries use any IIF or function calls? This can cause significant slowdowns, especially as your dataset grows.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
here is code to turn off subdatasheet in all tables.  

Public Sub SetSubDatasheetNone()
  
   'crystal 10-27-06, 130725
   'strive4peace

   'based on code written by Allen Browne
   '      http://allenbrowne.com/bug-09.html
   '
   '  click HERE
   '     press F5 to Run!
   '     (or choose 'Run, Run Sub/Userform' from the menu)
   '
   'set the Subdatasheet property to [None] in all (non-MSys) tables
   '
   '~~~~~~~~~~~~~~~~~~
      'NEEDS reference to Microsoft DAO Library
      'or
      'Microsoft Office ##.0 Access Database Engine Object Library
   '~~~~~~~~~~~~~~~~~~
 
   Dim tdf As DAO.TableDef _
      , prop As DAO.Property
      
   Dim nCountDone As Integer _
      , nCountChecked As Integer _
      , mBoo As Boolean _
      , sStr As String
  
   'cheap but it works <g>
   On Error Resume Next
   
   nCountDone = 0
   nCountChecked = 0
   For Each tdf In CurrentDb.TableDefs
      'skip Microsoft System tables
      If Left(tdf.Name, 4) <> "Msys" Then

         mBoo = False
         nCountChecked = nCountChecked + 1
         Err.Number = 0
         sStr = tdf.Properties("SubdatasheetName")
         If Err.Number > 0 Then

            Set prop = tdf.CreateProperty( _
               "SubdatasheetName", dbText, "[None]")

            tdf.Properties.Append prop
            mBoo = True
         Else
            'thanks, Allen!
            If tdf.Properties("SubdatasheetName") <> "[None]" Then
               tdf.Properties("SubdatasheetName") = "[None]"
               mBoo = True
            End If
         End If
         If mBoo = True Then
            nCountDone = nCountDone + 1
         End If
      End If
   Next tdf
   
   Set prop = Nothing
   Set tdf = Nothing
   
   MsgBox nCountChecked & " tables checked" & vbCrLf & vbCrLf _
      & "Reset SubdatasheetName property to [None] in " _
      & nCountDone & " tables" _
      , , "Reset Subdatasheet to None"
   
End Sub

Open in new window


keep in mind, though, that this won't stick unless AutoCorrect is also off ,,, and turning this off will also help performance.

File, Options, Current Database

1. uncheck Perform Name AutoCorrect (leave Track Name AutoCorrect checked) -- you might need to check it to uncheck Perform
2. Compact/Repair
3. Uncheck Name AutoCorrect
4. Compact/Repair
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
First thing you need to look at is the indexing as Crystal said in her first comment.  And a couple of points there:

1. Certainly make sure all the keys fields are indexed.
2. If your using compound keys (multiple fields make up the key), unless you are searching the keys on the same order, the index will not be used.  So your often better off having individual indexes on each field.  JET SHOWPLAN can tell you what the query is doing (up to a point - it doesn't do sub-queries),, but it might help.  Leavethis go for the moment though.   It's rare that you need to resort to this.

3. Don't index fields with low carnality (uniqueness).  A yes/no field should not be indexed since it only has two possible values, yes and no.
4.  Too many indexes can be as bad as too few.  The more indexes, the more that needs to be maintained and the more you can have concurrency issues.

 This is a good possibility because by default, Access is set to index fields with certain strings (like 'ID').


Next up would be what Scott touched on; the queries themselves.   Besides IIF(), Domain functions are a major no-no as well.   makes your queries totally un-optimizable by the query parser and you will always get poor performance.

 I'd then focus in on your need to requery constantly and a better way to handle it which is what Dale was getting at.

Jim.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> " This is a good possibility because by default, Access is set to index fields with certain strings (like 'ID')."

aah yes, a default I always change!.  Jim makes a good point.

File, Options, AutoIndex on Import/Create --> delete everything in the box! (code, id, num, and I can't remember what else because now they are gone)

It is a good habit to keep the indexes window open when you design tables (lightening bolt icon) -- put it over to the right and make it smaller, but keep it on so you can remove extra indexes in tables.  These only change when records are changed, but it takes more time and makes the database bigger, of course.

Following up with Scott's comment and to help you find where you have used functions, here is a tool to Document Calculated Fields in Queries, write results to Excel and format
http://www.rogersaccesslibrary.com/forum/document-calculated-fields-in-queries_topic619.html
- SQL to show calculated fields in queries, use CopyFromRecordset to put in Excel and then format

and here is a video explaining it:
https://www.youtube.com/watch?v=vS8KfHU6L90
Andy CownieImplementation SpecialistAuthor Commented:
Thanks for all of your comments!
Crystal:
I do have RI set on the relationships, which are mostly 1-1, with one being 1-M
I'll change the text field size on some fields, but most are pretty short (~10-20)
I'm still testing indexing, but so far haven't noticed any improvement, but a user did start getting an error when editing an indexed field, something about creating a duplicate index, though it was set to allow duplicates. Still looking into this
LukeChung:
I had already tried opening a connection to the database and leaving it open while the file is open, but this didn't help at all.
ste5an:
The lookup tables are located in the f/e, as otherwise loading a form took minutes instead of seconds.
Dale Fye:
I like your idea, it will take a bit to test this out, but will update if this helps. Part of my problem is that stupidly I need to lookup 2 linked tables to find the next unassigned record, so I will need to change that
I'd love to move to a mysql or other backend, but the business won't support this at the moment.
Scott and Crystal:
There are no iif's or function calls in the queries linked to these forms, or the tables then lookup.
I've got a few things to test out here and see if any of it helps, so thank you all!
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome

I am surprised that most of your relationships are 1:1 as 1:many is much more common.  What kind of tables are these?  Why so many 1:1 relationships?

When you creates indexes, if you want to allow that field to be Null, you should set that in the Indexes window on the index properties, otherwise a value may be required.  Remember, there is Yes (No Duplicates) and Yes (Duplicates OK).  If the field is a FK of a 1:1 relationship with RI, even if the visible index allows duplicates, the hidden one won't
Luke ChungPresidentCommented:
How was the performance before you split it?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Curious as to what you found to be the culprit. Lots of suggestions, so it'd be nice if you could summarize for anyone else who might have the same issue.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.