Help! Search Optimization For Large Table in SQL 2000

[RealQuiet]

I have an application that lets the user search an account table by name. The search can be performed with "Begins With" or "Containing" Clauses. The Top 100 search results are returned to the form and are narrowed down as the user types additional characters. I am using "Select * From Accounts Where Compressed_Name Like 'SearchText%'" for the Begins With search and "Select * From Accounts Where Compressed_Name Like '%SearchText%'" for the containing search. The account table has roughly 2 million records. I have indexed the table, and the searches are pretty sporty after 3 or 4 characters or more are in the search, but the searches are taking far too long when the searches have fewer than 3 characters when looking for "Containing" text. I would appreciate any tips to optimize this search process.

 

[foxdev]

I don't see how you can do much more to optimize "contained in" searches - indexes won't help here.

You might try full-text indexing on that column as a test; I don't know if it will help, since you searching for pieces of words rather than whole words. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org