Increase search speed

[kjuelfs]

Hello,

I'm trying to improve the speed of a search form used to display all entries in my database which contain a given value. I'd like to know if there's any way of improving the time it takes to return the results (currently about 5 seconds).

The details:
- search is done in one field in one table with about 13,000 entires. The field is text, length 8, and the search value is entered in an unbound field and used with a command button to set the filter string to: Like "*" & STRING & "*" (or whatever the correct syntax is)

Example of the field to search in the table:
12345-00
42648-01
42648-02
56843-00
54868-02
etc...



Example of string to search for: "42648". This should return 42648-01 and 42648-02

- the search field is indexed. This doesn't appear to make a difference
- interestingly, when I search the "title" field, which is also a text field length 255, as opposed to mainly numeric, it is almost instantaneous

Any ideas?

Thanks in advance

[NTC]

Like "*" & STRING & "*"

obligates it to search from every character position

if your post is accurate and you only actually need:
Like STRING & "*"

that will speed things up alot.

rhetorical: is your Title look up comparison also using ?
Like "*" & STRING & "*"

[kjuelfs]

Thanks for the answer.

My exact code at the moment:

search_string = "([FIELD] Like ""*" & SEARCH_VALUE & "*"")"
Me.Filter = search_string
Me.FilterOn = True

Where "FIELD" is the field to be searched, and "SEARCH_VALUE" is the value given.

In fact I do need the first "*". In my example I wouldn't need it, true, but there are times when I need to search the middle of the value, for example "2648".

In the title field I have used the exact code as above, just switching the FIELD variable to the title one, and entering a text search value instead of numeric.

For information: I have also done the search form using a query, where the value to "filter" in the query is update according to the given value. This method takes exactly as long.

[kjuelfs]

I've found a solution. It seems there is no way to make the "LIKE *VALUE*" type of search faster. I've changed it according to the suggestion above, and created a work around so that I can find the other values too. The end-product is much better.