SQL Server Performance

When is the full-text search really really needed?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by matt3.5, Aug 15, 2008.

  1. matt3.5 New Member

    In a database I am designing for a website, one of the main tables is a products table. Users will need to be able to search using a search box on the UI against the ProductName column, varchar(120) (a fairly standard scenario). Now, I have read up and developed a fully working sample that has a full-text catalog etc and I have done some queries using 'contains' predicates etc. But I'm wondering if full-text search should actually be used in this scenario in place of the usual LIKE..% % type query on the where clause. The table of products will only ever have about 200 rows, 300 absolute max. Now I know full-text search can be used against huge tables to gain high performance reads, but in this scenario, will I see a significant increase in performance or will the additional overhead of Full-text search outweigh any gains? Is this overkill? Will the usual LIKE query be too slow even for this fairly small table (baring in mind this is for the web)?
    Any full-text vs like usage wisdom appreciated!
  2. preethi Member

    Knowing that you have only a few hundred rows, you dont have to go through full test searches. you need an addiitonal process to read the data and to maintain full test index. I can safely say full test is an overkill.
    However, having '%' in front of the string will lead to table scan even if you have an index on the product name. If many thousands of uses are going to access your server, you need to think again.
    If you data is almost static, you should think of caching the data in the application layer.
  3. matt3.5 New Member

    Thank you for your reply. After more research though I have decided to still go with the full-text catalog - mainly to avoid table scan and also benefit from inflectional results using * etc. This will be on a dedicated machine so the thread issue should not be a big problem.

Share This Page