Full Text Search using CONTAINSTABLE clause

 

hi,

 

i having problem in full text search. there table named products contains 30 million products of hundreds of merchants. while searching online users can select the specific merchants products want searched. lets suppose user selects 10 merchants , wants these merchants products matching search criteria should searched. problem when make query for full text search provided criteria have include merchants ids in containstable clause using or keyword ( "holiday" , "creations" , merchantid66 or merchantid77 or merchantid88 or merchantid89 or merchantid99 or .......) , when query execute under full text search first searches 30 million records , filter out results given merchant ids.

 

this makes on head on database because instead of searching in selected merchant's products searching first through products , filtering out.

 

i need urgent , efficient solution. please me asap.

 

thanks , regards,

 

zeeshan malik

 

* moved database engine forum

 

it looks me indexing merchantids. perhaps better solution similar following merchantid in own column.

 

code snippet

create table merchants (merchantid int)

create table products (productid int not null primary key, merchantid int, description varchar(max))

 

select *

from products

inner join containstable (products, description, '"holiday" or "creations"') as ft

on products.productid = ft.[key]

where products.merchantid in (66,77,88)

 

 

as fulltext engine not integrated database engine i'm afraid you'll find performance drain running 2 queries fulltext query being "remote" query , returning results database query optimiser results dealt with.

 

here article found may address issues suggest:

 

http://www.sql-server-performance.com/article_print.aspx?id=22&type=art

 

hth!

SQL Server  >  SQL Server Database Engine