Solved Best practices for making a general purpose search field

[Saphirah]

A client of mine requested to make him a general search field, that can be used to search for more than one field at the time.
For example when searching for a customer in the system, you have one input box, where you can search for

customer name, street, place, Tax-ID, email, telefone

You can seperate values by spaces, so if you write "Apple California Cupertino" the system should check if there is a customer with the earlier mentioned fields that match to the words "Apple", "California", "Cupertino".

So i came up with 2 approaches to solve the problem. The first solution was, i combined all the mentioned fields into a single field and then do a like search on it.
In "Apple"'s case this would be

SearchStr: "AppleInc.1 Apple Park WayCupertinoCaliforniaTAXIDHEREcontactus.de@euro.apple.com1 408 996–1010"

Now when we write a filter we get

"SearchStr Like '*Apple*' AND SearchStr Like '*California*' AND SearchStr Like '*Cupertino*'"

You can probably already see where the problem is lying. Because we are doing a Wildcard search on a generated field SQL needs to perform a full text search through all records. This is fine when you have <10000 entries, but everything above is getting pretty bad performance wise.

That is why i came up with my second approach, which is the most obvious you can do.
I indexed all of the fields that are used for the search. I then generate a filter string using vba:

"(CustomerName Like 'Apple*' OR Street Like 'Apple*' OR Place Like 'Apple*' OR TaxID Like 'Apple*' OR Email Like 'Apple*' OR Telefone Like 'Apple*') AND (CustomerName Like 'California*' OR Street Like 'California*' OR Place Like 'California*' OR TaxID Like 'California*' OR Email Like 'California*' OR Telefone Like 'California*') AND (CustomerName Like 'Cupertino*' OR Street Like 'Cupertino*' OR Place Like 'Cupertino*' OR TaxID Like 'Cupertino*' OR Email Like 'Cupertino*' OR Telefone Like 'Cupertino*')"

This method has the advantage of using indexes, and it performs much better than the previous method, but is still not scalable with acceptable performance.

That is why i would be really thankfull for a pointer in the right direction. What is the best method to create a general search field? Is there a way to optimize the code, filters or anything? Maybe a different method would be better?

Thank you very much for your help.

 

[Minty]

I think generally your end-users should know that Apple is either a place or a company name.
They wouldn't search for a zip code in the phone number field for instance. So let them choose what to search, but restrict the where.

So only search one field at a time, if there are many many possible returns, dump them into a list box and then select another field to further refine the search. By displaying the results your end-user will normally identify what they are after very quickly.

The only other option would be a full text index in SQL server or something similar, but that takes some setting up and obviously won't work in an Access back end.

 

[jdraw]

You may get some ideas from or find this material can be used.

 

[MajP]

Agree with Minty. I would provide them the ability to specify one or more fields to search. That example is a Find as you type and works fine for something under 50k records. 10k would be no problem. As you start getting much bigger then you have to sacrifice speed for flexibility. If hey provide an exact thing to search vs a Like then it will be much much faster.