Any ideas to speed up filter of a recordset? 2

[timoteo]

I am using the code below to filter a list of names that appear in a datagrid. As the user types in the letters of the last name, the program filters the recordset to those records that start with the letters typed in. The code works fast as long as there are records in the recordset. However, if the record set is empty because there are no names that match what the user typed in, then program responds very slowly. For example, if the user is looking for "Smithie" and there are people named "Smith" but not "Smithie", then the filter is lighting fast as the user types S-M-I-T-H. However, as I-E are typed the filter becomes very slow.

Does anyone have any ideas why this happens or of another way that might be faster?

Thanks in advance for any help.

Timoteo


Private Sub txtNameFilter_Change()
'Fliters the list in the MainGrid by last name according to userinput

If txtNameFilter = "" Then
rsSubMain.Filter = adFilterNone
Else
rsSubMain.Filter = "LName LIKE '" & txtNameFilter.Text & "*'"
End If
End Sub

 

[BobRodes]

Yeah, you can put temporary indexes on your recordset. Do this after you open the recordset and before you set the filter:

rsSubMain.Fields("LName").Properties("Optimize") = True

I got some huge (10 times faster) performance boosts using this, but it does use up memory.

HTH

Bob

 

[timoteo]

Bob,

Thanks for the excellent tip.

Unfortunately, it did not help in this situation. I think the problem has to due with the recordset being empty because as long as there is data in the recordset the filter works very fast, with or without an index.

I found a partial work around using the code below, but I am not completly happy with it because there still is a lag when the recordset first becomes empty.

Private Sub txtNameFilter_Change()
'Fliters the list in the MainGrid by last name according to user _
'input if blank, then undoes filter
If txtNameFilter = "" Then
rsSubMain.Filter = adFilterNone
Else
rsSubMain.Filter = "LName LIKE '" & txtNameFilter.Text & "*'"
If rsSubMain.RecordCount = 0 Then 'Stops searching if recordset is empty
MsgBox "Last Name Not Found", vbOKOnly, "Not Found"
txtNameFilter = ""
End If
End If
End Sub

Any other suggestions?

Thanks again,

Timoteo

 

[BobRodes]

Oh, ok.

What you're doing is filtering out a large percentage of records in the recordset. When the filter excludes most of the records, the recordset cursor is still evaluating those records.

I've never personally run into that situation in practice, since I don't generally present filtered recordsets to users. I prefer to requery. You might experiment with requerying and see what it does for your performance; you may find that you're reassigning the performance hit to a different user context which works better for you.

Another thing. The change event fires at some interesting times, and you might want to consider using the keypress event if you are wanting to respond only to user input.

HTH

Bob

 

[zemp]

I also prefer the requery method and to let the database engine do the filtering.

zemp

 

[timoteo]

Bob, Zemp,

Thanks for the suggestion on the requery. I must admit that I am unsure how to use the requery method to achieve my goals. My only experience with this method is using it to refresh a recordset based the query that originally defined it.

Could you point me in the right direction for using the requery method in the manner you describe?

Also Bob thanks for the suggestion to use the keypress event rather than the change event.

Timoteo

 

[BobRodes]

I just close my recordset and reopen it.

Bob

 

[zemp]

I'm with Bob.

zemp

 

[BobRodes]

Ah. Timoteo, I read your question more carefully, and I see a great example of the importance of semantics here. When we're saying "requery method", we don't mean "a method called ReQuery", we mean "use requerying as the means to accomplish our goals." It's why the RUP recommends the use of a semantic dictionary that is continually added to throughout the process of application development, wherein the written definition of a word represents the consensus as to its meaning within the entire scope of the application.

Bob

 

[timoteo]

Now I think I understand what you are talking about. On each keypress I should close the recordset then reopen it using a query based on the user's input. And what you are *not* talking about is the Requery Method of the Recordset Object (i.e. rsSubMain.Requery).

I will try that and let you know how it goes.

Thanks again.

 

[zemp]

Be aware of large recordsets (100's or 1000's of records) that can take time to load into a grid. This can frustrate the user and slow down the entire program.

In this case I like to use recordset paging. It loads only the number of records that the user can see, one page at a time. Basicall it splits the wait time up into smaller less noticalble pieces. See the link below.

http://support.microsoft.com/default.aspx?scid=kb;en-us;254117

zemp

 

[BobRodes]

Rather, I would use a judicious combination of requerying and filtering. It has to do with when you assign the performance hit.

Bob

 

[zemp]

I agree, but at times it is not the requery of the data that causes app performance problems but rather the displaying of the results to the user. I have found this especially in grids.

zemp

 

[BobRodes]

Yes, that's very true.

Bob

 

[timoteo]

I recoded the last name search to requery rather than use the filter method. It works very fast; eventhough there can be a maximum of 13,000 records. zemp, that is a very interesting link. I will keep that in mind just in case I notice performance lags as the database grows.

Bob, zemp thanks for the help. I never would have been able to find a solution without your help.

Timoteo