Access Database very very slow

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

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.

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.

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

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

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.

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.

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

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

Select allOpen in new window

Many thanks, I will report back.

Thank you all for your help. Turns out I had a hardware problem but Jim led me in the direction of the solution.

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.

No one could have known the server had been changed.