Fastest Way to Search for Record in a Form

[JoeM]

I have inherited a very inefficient database that we are making some changes to. The first changes were to split it, and put the back-end in SQL, which we have done successfully. Now we are having some performance issues because some of our users are at a remote location, and the connection tunnel is limited, so it runs very slowly for them. Though that is the main cause of their performance issues, I am looking to see where I might be able to gain some efficencies and make things a little better for them.

One of their slowest performing objects is a particular form they use to search and edit data. The Record Source of this Form is a query that basically takes a subset of data from the main data table. The query currently returns about 23500 records (of a total of about 44500 records found in the main table).

The slow action they have is a Search button, in which they search for a record based on a value they input. The field that they are searching on is the Primary Key field, so there is really no efficiency to be gained there, since it is already indexed. I was wondering how efficient their method of searching is. Currently, they are clicking on a Command Button which open the Find/Replace dialog box, where they enter the value they are searching for. The code looks like this:

Private Sub Command50_Click()
On Error GoTo Err_Command50_Click

    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command50_Click:
    Exit Sub

Err_Command50_Click:
    MsgBox Err.Description
    Resume Exit_Command50_Click
    
End Sub

In some old databases I created many years ago, I used this type of functionality to search for records, where a user enter the value they want to look for in a Combo Box, and the AfterUpdate events triggers it to jump to that record:

Private Sub cboClient_AfterUpdate()

'   Go to selected record
    DoCmd.ShowAllRecords
    Me!strClientCode.SetFocus
    DoCmd.FindRecord Me!cboClient
    
'   Blank out value in combo box
    Me!cboClient.Value = ""
    
End Sub

So, my question is this. Is the Find/Replace dialog box a fast and efficient method of searching for records, or is there a better, more efficient way of going about it (like the other method I posted)? I would try it myself, but unfortunately (or fortunately) I am not in the environment that is experiencing the slowness. So I would need to coordinate the testing with one of the users out there. I may still do that, but just wanted to get some opinions on what might be some best practices for searching first.

Thanks

[ItsMe]

...The Record Source of this Form is a query...

Would it be possible to rewrite the query as a pass-through query?

[Perceptus]

How about Cloning the recordset object for the form and searching the clone. Return the bookmark from the search if found and set it appropriately to your current view.

[CJ_London]

So, my question is this. Is the Find/Replace dialog box a fast and efficient method of searching for records

No - it is the slowest

I seem to recall that 'find' does a sequential search but even if it isn't if they are searching on the basis of 'contains search string' then indexes cannot be used so the search is still sequential. if they are searching on the basis of 'starts with search string' then indexing will be used (assuming the field is indexed in the first place) - indexes can only be used from the beginning of the field value

also, rather than finding the record, have you tried using a filter instead?

Private Sub Command50_Click()
On Error GoTo Err_Command50_Click

    me.filter="[" & screen.previouscontrol.controlsource & "]=[" & screen.previouscontrol & "]"
    me.filteron=true


Exit_Command50_Click:
    Exit Sub

Err_Command50_Click:
    MsgBox Err.Description
    Resume Exit_Command50_Click
    
End Sub

if you want to try it out before you change the code, go to the form recordsource to view all the records, then right click on a column that is indexed and do a search from there.

[CJ_London]

one other thing to try if it is right for you is to change the recordsource for your form to have a criteria which will return 0 records e.g.

SELECT * FROM SqlView WHERE 1=0

then once the user has entered their find info change the recordsource to include the criteria

me.recordsource="SELECT * FROM sqlView WHERE [" & screen.previouscontrol.controlsource & "]=[" & screen.previouscontrol & "]"

this will really minimise the amount of data that needs to be transmitted across the network

Also, forgot to mention the examples I provided assume a numeric value - if it is text you need to add single quotes and dates the #

[JoeM]

Would it be possible to rewrite the query as a pass-through query?

No, as I said in the question, they use this form to edit data, and pass-through queries are not editable.

How about Cloning the recordset object for the form and searching the clone. Return the bookmark from the search if found and set it appropriately to your current view.

As long as that is editable, that is a possibility. Would that be more efficient than the second method I posted in my original question?
Do you have any links to code that does that?

Regarding "Filters", I thought about that. But I don't think they want it limited down to just a single record. They want all the records, but want the one that they are searching for the "selected" record. Normally, if they just wanted that one record, I would create a Form where they enter their criteria and I build the SQL code via VBA on-the-fly (I am quite proficient at that).

[CJ_London]

But I don't think they want it limited down to just a single record.

if speed is an issue then you may need to rethink what you are doing and ask your users - do they want speed? or the ability to scroll through thousands of records?.

[JoeM]

Its kind of a "fine line". It is 2 users out of 50 that have this issue (because they are the only two at this remote location). So if I take something away from 48 to fix something for 2, I will probably have many other complaints on my hand (such is the life of a programmer).

Question: Is it easy to toggle back to "unfiltered" once you are in a "filtered" record? I must admit, since I usually do "query on-the-fly" solutions, I don't use filters that much, so I am not versed on all the nuances of filters. I really don't want to go with a "query on-the-fly" solution here, because I would to keep it looking the same as much as possible. These users aren't that technical, and big changes are often not well received.

Thanks

[CJ_London]

So if I take something away from 48 to fix something for 2, I will probably have many other complaints on my hand (such is the life of a programmer).

I sympathise! - but the 48 would also see an improvement. And it does depend on how your users are actually using the form. I still think you need to talk to your users about how they actually use the form - having found a record, why are they interested in the records just before and after it? or perhaps it is a search which will find more than one record so find a record, make a change, find the next one - a filter saves them having to find the next one - it will be on the next row.

If your users are uncomfortable with seeing a blank screen before they enter a search you could populate the screen before they start searching with say the first 100 records

SELECT TOP 100 * FROM SqlView

then use the process I outlined in post #5

And the other suggestion is to modify the code with an if or case statement

if user is remote then use filter method otherwise use find method

[JoeM]

Update: So I tried changing the code to run like the second block of code in my original post, and asked the users to test it out. They said it works great now! Much faster. The only caveat is that the original method let them search any field, while the new one is for a specific field, but since the run 90% of their searches one this one field, it is a huge improvement. I left the "old" button out there too, for those other searches.

So I think we go with this for now.

Thanks everyone.