Results 1 to 4 of 4

Thread: Search Field

  1. #1
    Join Date
    Feb 2003
    Location
    USA
    Posts
    5

    Question Unanswered: Search Field

    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!

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Search Field

    3000+ records? We already past the dBase II age. Any search will be fast enough.

    Do you have to perform your search often within a loop? Consider to restruct your application to keep the text search in the outer loop.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Feb 2003
    Location
    USA
    Posts
    5
    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.

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •