I've came across various methods to search on a table field. I was just wondering which one would be the best (fast, efficient and robust) option considering that I have two varchar/text fields (say ProductName and ProductDescription) and the table may have 3000+ records with these two fields to search on. Should I use:
1. LIKE based search
2. Full-text search (using MS Indexing Service)
3. Any other solution?!?!
Please suggest your views and thoughts on the matter. Thanks!
Thanks for the reply. I just mentioned the number of records as it may (or may not) matter when a keyword based search will be performed on both the varchar/text fields. At max this table can go upto around 10000 records. Maybe I can just use the LIKE-based query.
BTW, I didn't quite get your concern about the "search in loop" issue.
Originally posted by BorgKing
I didn't quite get your concern about the "search in loop" issue.
I was concerned about the frequency of your query. A "once-in-a-while" query does not fear 3.000 or even 10.000 records. However, if you are using your query within an application, which fires the query frequently, you should be aware that searching in text fields using fuzzy criteria like LIKE (char, varchar) or CONTAINS (text) is the most expensive manner of searching, even in text-indexed tables. If there is any way to minimize the frequency of your query, take that opportunity.
Make everything as simple as possible, but not simpler! - A. Einstein
DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool