Question Built-In Access Find/Replace Function taking way too long.

[MSAccessRookie]

I have an Access 2003 project that uses an MS Server 2005 Back End, and most of the combo box searches are taking significantly longer than they were when the database was Access Only.

I searched the forum and I was unable to find very many posts related to this subject. I did notice that I made a similar request for assistance a few months ago, and never really got any feedback. I also noticed that a number of users with similar problems also received little or no response, so I assume this must be a difficult issue to deal with.

If anyone has experienced similar symptoms, I would appreciate any insight as to where to look for a resolution to this. It is the ONLY remaining issue left on my conversion approval list.

 

[The_Doc_Man]

From the Help files associated with Ac2k3

It takes too long to find and replace data.

For the fastest searches using the Find and Replace dialog box, click Whole Field in the Match list, and then enter search strings that you think match an entire field. If you search an indexed table, click Start of Field in the Match list.

If you search the same nonindexed field repeatedly, you can speed the search process by creating an index for the field. An index is an internal table with two columns. One column contains the values in the fields that you're indexing, and the other contains the physical location of each field that contains the values. Access uses indexes in the same way that a reader uses an index in a book. It looks up an entry and goes to the location or locations that contain that entry, making the search process faster.

This is related to using the Find/Replace dialog box to muddle about in tables. Anything that would make a query run slow would make find/replace run equally slow. Stop thinking about the problem as a find/replace issue and make it into a query speed issue. See if that helps your analysis.

 

[MSAccessRookie]

From the Help files associated with Ac2k3



This is related to using the Find/Replace dialog box to muddle about in tables. Anything that would make a query run slow would make find/replace run equally slow. Stop thinking about the problem as a find/replace issue and make it into a query speed issue. See if that helps your analysis.

The_Doc_Man,

Thank you very much for your reply. I ran a test on two of the queries that are having problem, and the results were as follows:

1. qry_Institutions: Loads Appx. 3000 records instantly. Takes up to 2 seconds to find a record for a specific institution. This used to take no time at all.
2. qryPeopleAlphabetic: Loads over 9000 records almost instantly. Takes up to 3 seconds to find a record for a specific person. This used to take no time at all.
3. qryWorkOrders: Loads Appx. 40000 records instantly. Takes up to 45 seconds to find a record for a specific institution or a specific person. This used to take less than 5 seconds.

I am sure that I can find others, but I believe these to be representative of the problem that I am having. I am willing to provide as much detail as I am able. Please let me know what else I can do.


NOTE: The appropriate tables on the SQL Server have Indexes on all 5 of the searchable columns that are represented here.

1. InstitutionID for Query1
2. PersonID for Query2
3. ResearcherID, ShipToID, and InstitutionID for Query3

 

[The_Doc_Man]

The comment: "Used to take no time at all" intrigues me. So ... what happened? I'm guessing something grew in size.

Not that this is a simple problem, but some simple questions might be in order.

First, can you rebuild the indexes? If so, does it make a difference?

Second, can you change the size of an index record on your BE database? If so, can you try to make the index wide and shallow vs. narrow and deep? The former makes for faster searches but costs more memory. The latter makes for smaller buffer sizes but takes longer to do a search.

I also have to consider what kind of query the find/replace is building to do this operation. If I were to implement this, I would have the F/R build an Update query once you click the Replace button or the Replace All button. So think about this as a query.

Which leads me to the next issue, a comparison of methods.

Suppose that you are searching table Rabble for all cases where field Rouser contain the word Distims and you want to change that to Graustark. (Don't ask...)

So

UPDATE SET Rabble.Rouser = "Graustark" WHERE Rabble.Rouser = "Distims" ;

Right? This is Find/Replace as a query. (And pardon my sloppy syntax!)

This should run fast.

But what about this one?

UPDATE SET Rabble.Rouser = "Graustark" WHERE Rabble.Rouser LIKE "*Distims*" ;

That one will take LOTS longer because it is a search for any field that CONTAINS the targeted string. So... how many wildcard F/R will you do? That is a case where you might partly or completely negate the effectiveness of the index.

Always, for F/R, think about what the underlying query has to do and test by comparing an equivalent SQL statement.

Now... here is where you also are doing yourself dirt. Where is the F/R executed? Inside of Access, I'll bet. Whereas the query, if it is a passthru query, is executed solely on the server and all you get back are the hits, none of the misses. The F/R on the client gets it all, winners and losers alike. So your speed comparisons might be apples/oranges.

 

[MSAccessRookie]

The comment: "Used to take no time at all" intrigues me. So ... what happened? I'm guessing something grew in size.

I have two different versions of the program and two different databases that I use for testing.

1. I have a copy of the unmodified application Access 2003 FE and its Access 2003 BE.
2. I have the modified Access 2003 FE and the SQL Server 2005 BE.

Both of the Database BEs are updated as often as necessary, and kept in synch, that is at any given time, the same version of the production data is in both of them. The updated FE is actually smaller because 1/4 of the queries have been moved to SQL Server Views.

If I run the original version, the Find/Replace is significantly faster than the updated version (I have already given rough statistics).

First, can you rebuild the indexes? If so, does it make a difference?

The SQL Server Admin has already set up and implemented a nightly (M-F) procedure to backup and rebuild the database in preparation for moving to production.

Second, can you change the size of an index record on your BE database? If so, can you try to make the index wide and shallow vs. narrow and deep? The former makes for faster searches but costs more memory. The latter makes for smaller buffer sizes but takes longer to do a search.

Interesting idea, but I am not sure how to do this.

I also have to consider what kind of query the find/replace is building to do this operation. If I were to implement this, I would have the F/R build an Update query once you click the Replace button or the Replace All button. So think about this as a query.

I have no idea, but I suspect that it may be looking over the entire form as opposed to the requested column.

Which leads me to the next issue, a comparison of methods.

Suppose that you are searching table Rabble for all cases where field Rouser contain the word Distims and you want to change that to Graustark. (Don't ask...)

George Barr McCutcheon fan?

UPDATE SET Rabble.Rouser = "Graustark" WHERE Rabble.Rouser = "Distims" ;

Right? This is Find/Replace as a query. (And pardon my sloppy syntax!)

This should run fast.

But what about this one?

UPDATE SET Rabble.Rouser = "Graustark" WHERE Rabble.Rouser LIKE "*Distims*" ;

That one will take LOTS longer because it is a search for any field that CONTAINS the targeted string. So... how many wildcard F/R will you do? That is a case where you might partly or completely negate the effectiveness of the index.

Always, for F/R, think about what the underlying query has to do and test by comparing an equivalent SQL statement.

I am not sure how to respond to this. You make a great point, and I do understand, but I am not in control of the method, MS Access is.

Now... here is where you also are doing yourself dirt. Where is the F/R executed? Inside of Access, I'll bet. Whereas the query, if it is a passthru query, is executed solely on the server and all you get back are the hits, none of the misses. The F/R on the client gets it all, winners and losers alike. So your speed comparisons might be apples/oranges.

I would think that the Find/Receive is in Access, since the command is executed via Control/F. As for Apples and Oranges, I do not think that my users care which kind of fruit comes with lunch, they just want the search speed back.

 

[MSAccessRookie]

I have been looking all day for any online references to a problem like mine and I have noticed two things.

1. Very few people have asked for help in forums like this one.
2. None of those who have have received any suggestions that were able to resolve their issue.

What I found leads me to the following conclusion:

1. Very few people have this problem OR
2. There is no known resolution to the problem.

I hope that #2 is not the case.

I think that I need to restart the process with this post.

------------------------------------------------------------------------------------------------

My problem is as follows:

I have several forms that each use the Built-In Find/Replace functions via Last/Next/Prev buttons (see the attached example SampleWO). The user will select a Contact name and click on the Last button to get the last record with that contact name. Subsequent records can be located via the Next and Prev buttons.

The Last/Next/Prev buttons each have a macro that is executed in the OnClick Event (See the code below).

[FONT=Courier New][SIZE=3]LAST:  [/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Find What:            [FindPerson][/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Match:                Whole Field[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Match Case:           No[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Search:               Down[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Search As Formatted:  Yes[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Only Current Field:   Yes[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Find First:           Yes[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=3]NEXT:  [/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Find What:            [FindPerson][/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Match:                Whole Field[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Match Case:           No[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Search:               Down[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Search As Formatted:  Yes[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Only Current Field:   Yes[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Find First:           No[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=3]PREV:  [/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Find What:            [FindPerson][/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Match:                Whole Field[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Match Case:           No[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Search:               Up[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Search As Formatted:  Yes[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Only Current Field:   Yes[/SIZE][/FONT]
[FONT=Courier New][SIZE=3] Find First:           No[/SIZE][/FONT]

 

[MSAccessRookie]

I was reading a post in another forum where someone claimed that the "Search As Formatted" attribute slowed down the Find/Replace function, so I decided to try to see what happened if I turned it off. The result was hit and miss. Either the search speed was improved from over a minute to under 15 seconds, or the search no longer functioned at all.

I changed and tested each of the macros that performed searches, and was able to improve less than 1/2 of them. The remaining ones are still the way that they were.

This afternoon's adventure leads me to two questions:

1. What does the "Search As Formatted" attribute do?
2. Why is it required for some searches and not for others?

Any suggestions as to how to proceed would be appreciated.

 

[Banana]

So, we're using Access's built-in find/replace functionality?

I wonder if because it was designed with JET (e.g. a file server) in mind, it may not be the best solution.

Why didn't you roll out a custom form with combobox/textbox to search for a record and utilize queries or stored procedures from MS SQL so it does the searching, not Access?

 

[MSAccessRookie]

So, we're using Access's built-in find/replace functionality?

I wonder if because it was designed with JET (e.g. a file server) in mind, it may not be the best solution.

Why didn't you roll out a custom form with combobox/textbox to search for a record and utilize queries or stored procedures from MS SQL so it does the searching, not Access?

I would love to replace the built-in feauture, but I am by no means either familiar enough or comfortable enough with VB yet.

Any suggestions would be greatly appreciated.

 

[Banana]

1) There is in fact, a built-in wizard to help you create a combobox for searching a record. In form design view, make sure you have wizard (magic wand) button lit up and select combobox, then drop it in the form, then a dialog should pop up; with search a record being the option (the 3rd option, IIRC)

2) This question has been asked several times. I'm pretty sure KenHiggs (or other?) has posted a sample database demostrating how to create a search form. Have a look in sample database forum and see if you can find it.

 

[MSAccessRookie]

1) There is in fact, a built-in wizard to help you create a combobox for searching a record. In form design view, make sure you have wizard (magic wand) button lit up and select combobox, then drop it in the form, then a dialog should pop up; with search a record being the option (the 3rd option, IIRC)

2) This question has been asked several times. I'm pretty sure KenHiggs (or other?) has posted a sample database demostrating how to create a search form. Have a look in sample database forum and see if you can find it.

I am going to look into this, but I think I am going to have to do it to a blank database and evaluate the results, since the combo am not sure don't see how creating new ones would help.

 

[Banana]

Normally, it's used for editing records and would be placed at header or footer of a form for user to choose if they needed to find a specific record. It's also usually unbound (I don't know if a bound combobox would even work... but haven't tested to be sure)

With new record, it's just a button away on the record selectors (or a custom button if you don't want to use that)

 

[The_Doc_Man]

Seeing the details of your F/R operation doesn't help that much. But I do see some things that make me wonder just how much damage you are doing to yourself with the "Match Case" = "NO" option. You cannot do a fast compare in that case because Access has to convert every string to UPCASE (or lowercase, don't care which) before doing the comparison. I.e. there is a "translate" overhead for every comparison.

I still think that the F/R must be done on the Access client side, whereas you would get far better speed if you could make that query somehow become a passthrough so that it would be executed solely on the BE and all you would get were the answers.

 

[MSAccessRookie]

Seeing the details of your F/R operation doesn't help that much. But I do see some things that make me wonder just how much damage you are doing to yourself with the "Match Case" = "NO" option. You cannot do a fast compare in that case because Access has to convert every string to UPCASE (or lowercase, don't care which) before doing the comparison. I.e. there is a "translate" overhead for every comparison.

Good point. I will have to add this change to the ones I have already and see how it fares.

Followup: Changing the "Match Case" to "NO" has no effect on the problem, although I will leave it that way for now since the arguments to do so are sound. ALSO, for some reason, the "Search As Formatted" option does not work at all any more. I am not sure how I recorded success yesterday, but I have changed all of them back to Yes, and they work, but are very slow.

I still think that the F/R must be done on the Access client side, whereas you would get far better speed if you could make that query somehow become a passthrough so that it would be executed solely on the BE and all you would get were the answers.

I am not sure how I could do this in my scenario. I remain open to any ideas.

 

[dfenton]

The main issue, I'd think, would be whether or not the fields you're searching are indexed. A full table scan will be necessary if they aren't. In Access that can take a long time with big tables, but compared to pulling the entire table across the wire from a SQL Server, it could seem instantaneous, relatively speaking.

Another option would be to try filter by form. In other words, right click on the field you're searching in and choose FILTER and put in the value you want to filter on. This will limit the form's records to the matching values.

I also deduce from what you've said that you have bound your form to an entire table (whether using the table directly, or using a SQL statement to retrieve the table). This is a really bad idea with a Jet back end (even though it's the default suggested by the easy-to-use point-and-click tools that Access provides). But it's disastrous for a server back end. You really want to retrieve only the records you need. This can be very easily accomplished with a FIND field whose AfterUpdate event changes the recordsource of the form so that it is limited by a WHERE clause that retrieves only the matching records.

If you need to search on more than one field, you could supply some method of allowing the user to select from a list of the fields (in an unbuond combo box) and then write your WHERE clause to use the value in the FIND textbox as the filter for the field chosen in the combo box.

All of these would be superior methods of accomplishing the task.

And all would be somethign that many, many people in this forum could provide help on implementing.