how to optimize search process (1 Viewer)

eugzl

Member
Local time
Today, 12:37
Joined
Oct 26, 2021
Messages
125
Hi All.
I created search filter for continuous form. That is search/filter code:
Code:
Private Sub txtSearch_Change()
    On Error GoTo errHandler

    Dim filterText As String
    
    'Apply or update filter based on user input.
    If Len(txtSearch.Text) > 0 Then
       filterText = txtSearch.Text
        If InStr(Format(filterText, "\#mm\/dd\/yyyy\#"), "#") > 0 Then
            Me.Form.Filter = "[qSearchContinuousForm].[RequestDate] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[FirstName] & ' ' & [qSearchContinuousForm].[FirstName] LIKE '*" & filterText & "*' " _
                            & "OR [qSearchContinuousForm].[TicketNo] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Department] LIKE '*" & filterText & "*' " _
                            & "OR [qSearchContinuousForm].[Device] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Model] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Location] LIKE '*" & filterText & "*'"
        Else
            Me.Form.Filter = "[qSearchContinuousForm].[Employee Name] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[FirstName] & ' ' & [qSearchContinuousForm].[FirstName] LIKE '*" & filterText & "*' " _
                            & "OR [qSearchContinuousForm].[TicketNo] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Department] LIKE '*" & filterText & "*' " _
                            & "OR [qSearchContinuousForm].[Device] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Model] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Location] LIKE '*" & filterText & "*'"
        End If

       Me.FilterOn = True
      'Retain filter text in search box after refresh.
       txtSearch.Text = filterText
       txtSearch.SelStart = Len(txtSearch.Text)
    Else
       ' Remove filter.
       Me.Filter = ""
       Me.FilterOn = False
       txtSearch.SetFocus
    End If

    Exit Sub

errHandler:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
End Sub
The qSearchContinuousForm query joins 3 tables and 2 queries. My problem is when I run query by itself it works enough quickly but when I'm filtering continuous form by the code so the whole process occupy approximately 2-3 sec just for 20 records. Does exists way to optimize code to get best time filter execution? If yes. Can someone show how it to do?
Thanks.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:37
Joined
Feb 19, 2013
Messages
16,553
1. using the initial * (...LIKE '*" & filterText &...) will preclude the use of indexing, so review as to whether you really need it - train users to use the initial * when required.
2. As Colin says, ensure all fields used in your query joins and searched are indexed if they are generally populated with a variety of values - see this link

not sure what you are doing here
OR [qSearchContinuousForm].[FirstName] & ' ' & [qSearchContinuousForm].[FirstName] LIKE

or here
If InStr(Format(filterText, "\#mm\/dd\/yyyy\#"), "#") > 0 Then
since a) you don't seem to be searching for a date and b) both assignments look identical to me

Edit: couldn't see for looking, but you need to format the searched for date, not the search string

....format([RequestDate],"mm/dd/yyyy") LIKE '*" & filterText & "*' ....

wont affect performance, but you don't need [qSearchContinuousForm]. in your filter string
 
Last edited:

eugzl

Member
Local time
Today, 12:37
Joined
Oct 26, 2021
Messages
125
Have you indexed the search fields?
Hi isladogs. Thanks for reply.
That is my first Access project. What need to do to index the search? If do you mean indexed table, so yes all my tables is indexed.
Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:37
Joined
Feb 19, 2002
Messages
42,971
The code is probably in the wrong event. Are you intending to run the search for each character you type? Or did you want to run it once after all the characters were typed?

No indexing helps when you are using wildcards this way. It is always a full table scan.
 

eugzl

Member
Local time
Today, 12:37
Joined
Oct 26, 2021
Messages
125
The code is probably in the wrong event. Are you intending to run the search for each character you type? Or did you want to run it once after all the characters were typed?

No indexing helps when you are using wildcards this way. It is always a full table scan.
Hi Pat Hartman. Thanks for reply.
Now the search works for each entered character. What event will be correct?
Thanks
 
Last edited:

eugzl

Member
Local time
Today, 12:37
Joined
Oct 26, 2021
Messages
125
in which case take note of my first comment post#3
Hi CJ_London. I remove IF statement leave only
Code:
Me.Form.Filter = "[qSearchContinuousForm].[RequestDate] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Employee Name] LIKE '*" & filterText & "*' " _
                            & "OR [qSearchContinuousForm].[TicketNo] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Department] LIKE '*" & filterText & "*' " _
                            & "OR [qSearchContinuousForm].[Device] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Model] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Location] LIKE '*" & filterText & "*'"
It still works slowly.
Do you mean indexed each field which specified in the search code?
Thanks
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:37
Joined
Jan 23, 2006
Messages
15,364
You have told/shown us HOW you have done the search code, but we don't know the application. None of the who,what why, how many etc. You have gotten responses and you say it is still slow.
Can you describe what you are searching; number of simultaneous users etc --there may be other options?
 

SHANEMAC51

Active member
Local time
Today, 19:37
Joined
Jan 28, 2022
Messages
310
Hi All.
I created search filter for continuous form. That is search/filter code:
Code:
Private Sub txtSearch_Change()
[/QUOTE]
You have one search field and 6 data fields
, it's probably more convenient to have 7 search fields (2 fields for the date, for setting the interval)
this would allow you to choose
- john of texas
- contacts with Texas in 2021-2022
...
 

Attachments

  • msa_0131.png
    msa_0131.png
    35.1 KB · Views: 232

CJ_London

Super Moderator
Staff member
Local time
Today, 16:37
Joined
Feb 19, 2013
Messages
16,553
@eugzl - you have not acknowledged the comment made in post#3, post#5 or post#6 namely

using the initial * (...LIKE '*" & filterText &...) will preclude the use of indexing,

And you don't appear to have read the link provide in post #3 which would answer your question about indexing

Also you have removed the if statement but not changed your query as advised in post #3
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:37
Joined
Feb 19, 2002
Messages
42,971
Now the search works for each entered character. What event will be correct?
Is that what you want to happen?
user types a, run the filter code, user types the next character, run the filter code, user types the third character, run the filter code

If you don't want the code to run except for once when the user finishes typing the characters he wants, then the event you need to use would be the BeforeUpdate event AND instead of the .text property (which you only use in the on Change event, you would use the .value property which is the default so we normally omit it. Me.controlname is the same as Me.controlname.value.

There are three buffers.
.OldValue - null for a new record or the original value from the table for an existing record.
.Text - the value of the buffer that collects keystrokes as they are happening.
.Value - the value of the buffer after typing is complete.

Between the form's BeforeUpdate event and the form's AfterUpdate event, the .Value property will replace the .OldValue property once the new/changed record has completed being saved.
 

eugzl

Member
Local time
Today, 12:37
Joined
Oct 26, 2021
Messages
125
1. using the initial * (...LIKE '*" & filterText &...) will preclude the use of indexing, so review as to whether you really need it - train users to use the initial * when required.
2. As Colin says, ensure all fields used in your query joins and searched are indexed if they are generally populated with a variety of values - see this link

not sure what you are doing here


or here

since a) you don't seem to be searching for a date and b) both assignments look identical to me

Edit: couldn't see for looking, but you need to format the searched for date, not the search string

....format([RequestDate],"mm/dd/yyyy") LIKE '*" & filterText & "*' ....

wont affect performance, but you don't need [qSearchContinuousForm]. in your filter string
I modified code and substitute event to AfterUpdate(). Now it works much better without flickering
Code:
Private Sub txtSearch_AfterUpdate()
    On Error GoTo errHandler

    Dim filterText As String
    
    'Apply or update filter based on user input.
    If Len(txtSearch) > 0 Then
       filterText = txtSearch
            Me.Form.Filter = "format([RequestDate],'mm/dd/yyyy') LIKE '*" & filterText & "*' OR [Employee Name] LIKE '*" & filterText & "*' " _
                            & "OR [TicketNo] LIKE '*" & filterText & "*' OR [Department] LIKE '*" & filterText & "*' " _
                            & "OR [Device] LIKE '*" & filterText & "*' OR [Model] LIKE '*" & filterText & "*' OR [Location] LIKE '*" & filterText & "*'"

       Me.FilterOn = True
      'Retain filter text in search box after refresh.
       txtSearch = filterText
       txtSearch.SelStart = Len(txtSearch)
    Else
       ' Remove filter.
       Me.Filter = ""
       Me.FilterOn = False
       txtSearch.SetFocus
    End If

    Exit Sub

errHandler:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
End Sub
Thanks for your analyze and advice very helpful.
 

eugzl

Member
Local time
Today, 12:37
Joined
Oct 26, 2021
Messages
125
Is that what you want to happen?
user types a, run the filter code, user types the next character, run the filter code, user types the third character, run the filter code

If you don't want the code to run except for once when the user finishes typing the characters he wants, then the event you need to use would be the BeforeUpdate event AND instead of the .text property (which you only use in the on Change event, you would use the .value property which is the default so we normally omit it. Me.controlname is the same as Me.controlname.value.

There are three buffers.
.OldValue - null for a new record or the original value from the table for an existing record.
.Text - the value of the buffer that collects keystrokes as they are happening.
.Value - the value of the buffer after typing is complete.

Between the form's BeforeUpdate event and the form's AfterUpdate event, the .Value property will replace the .OldValue property once the new/changed record has completed being saved.
I used your suggestion but BeforeUpdate() event gave me error message. Then I tried to use AfterUpdate() event and got search result much better then was before. You can see in the post #13 how I used your suggestions.
Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:37
Joined
Feb 19, 2002
Messages
42,971
Great but when you are referencing a control, use "Me." to disambiguate the reverence AND get intellisense. It makes it much easier for people reading the code when they can easily see whether you are reverencing variables or form controls in addition to being more efficient. When you use "Me.", the interpreter KNOWS that the variable is defined in the form's class module and so it doesn't have to search the current module for it first.
 

Users who are viewing this thread

Top Bottom