Use indexes on the fields in the criteria section when using the query design screen. In other words, index the fields on the right hand side of a where clause.
Use an initial query screen as input to a second query. This limits the number of records required to respond to a new query request. Try splitting a complex query into two or more queries in order to give the user a rapid response.
Use a make table query when it isn't possible to split up a complex query. This approach won't speed up the initial query but can be useful when similar queries are done repeatedly. A make table query saves intermediate results in a new table for future use, improving the efficiency of later queries.
Use stored queries only. A form that has a select statement as the record source either will not be optimized at all or will be optimized every time it runs.
Use recordset operations with the BeginTrans and CommitTrans methods in order to speed up query processing. This approach defines a transaction state which should include a relatively small number of statements.
Use recordset operations especially for an update or append query in order to increase the speed of your query from minutes to a few seconds. This approach has the greatest effect on complex rather than simple select queries since recordset operations are written to memory in a temporary transaction buffer rather than directly to the hard disk.
The following is a snippet of some code we use to analyze our website hits. The key parts of the Transaction processing are bolded.
Dim wksp As DAO.Workspace
Dim special As String
Dim visitor As String
Dim visit_date As Date
Dim Engine As String
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
special = Chr(34) & "-" & Chr(34)
‘making Microsoft access databases run faster with better design
Set wksp = DBEngine.Workspaces(0) ' set up a transaction buffer
wksp.BeginTrans ' all record set changes are buffered after this
On Error GoTo roll0
Set rst2 = db.OpenRecordset("M_Sitestats")
Set rst = db.OpenRecordset("select field1 from Sitestats")
rst.MoveFirst
Do While Not rst.EOF
visitor = Left(rst!Field1, InStr(rst!Field1, " ") - 1)
visit_date = CDate(Mid(rst!Field1, InStr(rst!Field1, "[") + 1, 11))
If InStr(rst!Field1, "www.google") <> 0 Then
Engine = "Google"
GoTo write_it
End If
If InStr(rst!Field1, "yahoo.com") <> 0 Then
Engine = "Yahoo"
GoTo write_it
End If
write_it:
With rst2
.AddNew
!visitor = visitor
!visit_date = visit_date
!Engine = Engine
.Update
End With
check_next:
rst.MoveNext
Loop
wksp.CommitTrans ' here we write the changes to disk all at once
GoTo finish_it
roll0:
If Err.Number = 3022 Then
On Error GoTo roll0
Resume check_next
End If
MsgBox Err.Number & " " & Err.Description
wksp.Rollback ' cancel everything if unexpected error
finish_it:
rst.Close
rst2.Close
Set rst = Nothing
Set rst2 = Nothing
End Sub
This method can speed up queries by allowing the transactions to be cached rather than written to disk after each transaction.
If you haven't time to implement these tips or if the development resources available to you are unable to make progress, please contact us.
Report and Form Tips >