• Status: Open
  • Priority: High
  • Security: Public
  • Views: 45
  • Last Modified:

Access Database very very slow

MS Access 2016 database, split into front end and back end, backend on a LAN, 140k records in the largest table, other tables very small,

Database ran well for several weeks, drives replaced on server, database ran for a couple of days OK but now queries that used to take 10 seconds take 5 minutes or stop.

I have compressed front and back end several times.

what can be wrong?

Thank you very much for your help.

Bob
0
Robert Wardlow
Asked:
Robert Wardlow
18 Comments
 
Fabrice LambertFabrice LambertCommented:
Many reasons can be involved, main ones beeing network traffic and server performances (wich you have no control on).
You can also take a look at your queries and see if they arn't too heavy.

As a sample, a query aggregating muliple sub queries themselves aggregating data arn't good performance wise, better filter as much as you can early and aggregate at the end.

Did you try executing your application with both FE and BE on your local computer ?
0
 
Robert WardlowPresidentAuthor Commented:
Hello Fabrice,

Thank you for your suggestions.
Yes, I have executed on my local PC and it is fine.
I don't have many sub queries, maybe 1

What about indexes? Could something have changed with an index?

Thank you
Bob
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
As Fabrice said, the reasons can be many.

I think my first stop would be a review of the indexes on the tables making sure they are appropriate.

While 140,000K records is not too large, it is at the point where performance can start becoming an issue if good development practices were not followed.

For example, use of domain functions in a query, extensive use of Dlookup() to get data, etc.

Jim.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
By the way, on this:

used to take 10 seconds take 5 minutes or stop.

 That can be ACE just choosing a poor query execution plan, which may have changed as a result of tables growing in size and then doing a compact and repair (all costing plans are dumped when you do a compact and repair along with updating table statistics).

Jim.
0
 
Robert WardlowPresidentAuthor Commented:
Hi Jim,
Thank you for your suggestions
I will review the indexes.
I am not using DLookup
What domain functions? I am not familiar.

Thank you
Bob
0
 
Robert WardlowPresidentAuthor Commented:
Hi Jim,
The tables in this database have not grown, the big table started at 141,000 and it is static.

I did not know compact and repair can cause problems. I thought it was good practice to compact and repair each time the database is closed.

I can stop that but how do I fix the damage if damage was created?

Thank you
Bob
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Last thing that you can try quick, put the following line of code in the app at startup.

  dbEngine.SetOption dbMaxBufferSize, 65535

 That will increase the cache size.  If may be flushing a lot.

 Also some other things:

1. make sure the DB's are not being virus scanned.
2. Turn off name Auto Correct and tracking.
3. Turn off subdatasheets on all tables (I have code for that).

 That's all easy stuff.  Start with that.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Bob,

<<What domain functions? I am not familiar.>>

 Dlookup() is one.  DSum() is another.    Those functions in Access carry out SQL operations in place where SQL is normally not allowed, say in a control.

 For example, Dlookup is nothing more than:

  SELECT <some field> FROM <some table> WHERE <some condition>

  When writing a query you are using SQL already, so there is no need to use them (a Dlookup would be replaced by adding a table and doing a join).

  And worse, when you do use them, the query processor cannot optimize the query properly.   This is a mistake that many people make and gives poor performance.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I did not know compact and repair can cause problems. I thought it was good practice to compact and repair each time the database is closed.

  It is a good practice and it's rare that the query optimizer will choose a bad plan, but it can happen, especially if there is not a lot of indexing available.

I can stop that but how do I fix the damage if damage was created?

 You can't really.   My only suggestion here would be to take a backup and test it with the situation you have now and see if it is faster.  Then perform a compact and repair.    If it slows down after that, then you know where the problem lies.

Jim.
0
 
Robert WardlowPresidentAuthor Commented:
Hi Jim,
Thank you again for your suggestions.
I am not using domain functions, thanks for clarifying.

Where do I place this statement: dbEngine.SetOption dbMaxBufferSize, 65535
I have a main menu form, can it be there? Maybe OnOpen

I will try your other suggestions.
If the code is handy for number 3 please share with me

Thank you very much
Bob
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Where do I place this statement: dbEngine.SetOption dbMaxBufferSize, 65535
I have a main menu form, can it be there? Maybe OnOpen

  On Open event of that would be fine.

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

Open in new window

0
 
Robert WardlowPresidentAuthor Commented:
Many thanks, I will report back.
0
 
PatHartmanCommented:
Does each user have his own copy of the FE?  Is it running locally from their C: drive?  You mentioned that the app was split but didn't say anything about how the FE was accessed.

Some people use an installer that runs only when a new version of the FE is deployed but I use a batch file that copies a fresh copy of the FE from the server to a local directory every time the app is opened.  This ensures that the user always has an updated version and eliminates the need to compact the FE.
0
 
EirmanChief Operations ManagerCommented:
There could be some untracable corruption. Try this 10-15 minute experiment .

Create a 2 new blank databases (e.g. testFE + testBE) in a test folder.
Import all Tables, Queries, Forms, Reports, Macros, VBA etc. from your existing databases
(For safety, import from copies).

Use the Linked Table Manager and test locally.
Copy  testBE to a network location and use the Linked Table Manager and test.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Well can you give us some tech info like OS on FE/OS on BE...possibly Antivirus or other staff running in the Background...ping status...if something has changed recently...
Also does this "slow" happens on all the tables...
0
 
Robert WardlowPresidentAuthor Commented:
Thank you all for your help. Turns out I had a hardware problem but Jim led me in the direction of the solution.
0
 
Robert WardlowPresidentAuthor Commented:
It turns out that this problem was hardware based and not Access based. Without my knowledge the group managing the server moved the database to an alternate location on a very underpowered "server". I apologize for wasting the time of everyone who generously helped me. The server staff has been taken out back and shot.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I apologize for wasting the time of everyone who generously helped me.

 No need to apologize.   Sometimes that's the way it works out.

 Just nice to know you have solved your problem as that is what counts in the end.

Jim.
0

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now