I have a table of around 10millon records, structure of table isID PKTitle varchar(150),ISBN10 varchar(10),ISBN13 varchar(13),Author1 varchar(150),Author2 varchar(150),Author3 varchar(150),Author4 varchar(150),Author5 varchar(150)there are other 20 fields are more in the table.I want to search on the words in the above fields. for that I used full text search on all the above fields. But its taking hell lot of time in the search and my SP is running too slow.Can anybody help me to find optimized way.Create PROCEDURE [dbo].[Sproc_FullTextSearchNewTable] @FREETEXT_SEARCH VARCHAR(1000), @SEARCH_COLOUMN VARCHAR(100), @SearchID int, @PAGENUMBER INT, @PAGESIZE INT AS BEGIN DECLARE @FREETEXT_SEARCHSORT VARCHAR(1000) , @sql nVarChar(4000),@sqlFilter nVarChar(4000), @PRICE INT,@ShippingTime int,@Binding int,@PubYear int,@Vendor int, @SearchPRICE varchar(50),@SearchShippingTime varchar(50),@SearchBinding varchar(50),@SearchPubYear varchar(50), @SearchVendor varchar(50) ,@SearchStock varchar(50) set @sqlFilter='' if(@SearchID <> 0) Begin Select @SearchPRICE =Price,@SearchShippingTime= ShippingTime,@SearchBinding =[Binding],@SearchPubYear=PublicationYear, @SearchVendor=Source,@SearchStock=Stock from Table_SearchCriteria where ID_Search=@SearchID if(@SearchPRICE <>'0') set @sqlFilter= @sqlFilter + ' and PriceEnum in (' + @SearchPRICE + ')' if( @SearchShippingTime <> '0' ) set @sqlFilter= @sqlFilter + ' and ShippingTimeWeeks in (' + @SearchShippingTime + ')' if( @SearchPubYear <>'0' ) begin set @SearchPubYear = replace(@SearchPubYear,'1000','0') set @sqlFilter= @sqlFilter + ' and PubYear in (' + @SearchPubYear + ')' end if( @SearchBinding <>'0' ) begin set @SearchBinding = replace(@SearchBinding,'3','0') set @sqlFilter= @sqlFilter + ' and Bindingenum in (' + @SearchBinding + ')' end if( @SearchVendor <> '0' ) begin set @SearchVendor = replace(@SearchVendor,'2','0') set @sqlFilter= @sqlFilter + ' and Is_IndianVendor in (' + @SearchVendor + ')' end if( @SearchStock <> '0' ) begin set @SearchStock = replace(@SearchStock,'2','0') set @sqlFilter= @sqlFilter + ' and Is_InStock in (' + @SearchStock + ')' end End SET @FREETEXT_SEARCHSORT = lower(replace(replace(@FREETEXT_SEARCH ,' ',''),'''','''''') ) SET @FREETEXT_SEARCH = REPLACE(REPLACE(REPLACE(REPLACE(@FREETEXT_SEARCH,'-',' '),'"',''),'!',''),'''','''''') -- IF(CHARINDEX(' ',@FREETEXT_SEARCH)>0) -- BEGIN SET @FREETEXT_SEARCH = '"' + @FREETEXT_SEARCH + '"' -- END SET @PAGENUMBER = ((@PAGENUMBER-1) * @PAGESIZE) +1 if(@SEARCH_COLOUMN ='filter' or @SEARCH_COLOUMN='*') begin set @sql=' With Prod (id_product) as ( SELECT min(id_product) FROM TABLE_PRODUCTSEARCHNEWSEARCH WHERE CONTAINS((ISBN10,ISBN13,PRODUCT_TITLE,AuthorList),'''+@FREETEXT_SEARCH+''') ' + @sqlFilter + ' GROUP BY ID_PRODUCTSTYLE ),' end else if(@SEARCH_COLOUMN ='title') begin set @sql=' With Prod (id_product) as ( SELECT min(id_product) FROM TABLE_PRODUCTSEARCHNEWSEARCH WHERE CONTAINS((PRODUCT_TITLE),'''+@FREETEXT_SEARCH+''') ' + @sqlFilter + ' GROUP BY ID_PRODUCTSTYLE ),' end else if(@SEARCH_COLOUMN ='author') begin set @sql=' With Prod (id_product) as ( SELECT min(id_product) FROM TABLE_PRODUCTSEARCHNEWSEARCH WHERE CONTAINS((AuthorList),'''+REPLACE(@FREETEXT_SEARCH,'.','')+''') ' + @sqlFilter + ' GROUP BY ID_PRODUCTSTYLE ),' end set @sql=@sql+' ProdList (ROWNUMBER,id_product) as ( SELECT ROW_NUMBER() OVER (ORDER BY case charindex(''' + @FREETEXT_SEARCHSORT+ ''',lower(replace(Product_Title,'' '',''''))) when 0 then 100000 else charindex('''+@FREETEXT_SEARCHSORT+''',lower(replace(Product_Title,'' '',''''))) end,IS_INDIANVENDOR DESC ,IS_INSTOCK DESC,PUBYEAR DESC) as RowNumber, id_product FROM TABLE_PRODUCTSEARCHNEWSEARCH where id_product in (select id_product from Prod) )' set @sql=@sql + 'SELECT ROWNUMBER,PS.ID_PRODUCT,PS.ID_PRODUCTSTYLE,PRODUCT_TITLE,ISBN13,PRODUCT_TITLEURL,PRODUCT_SALEPRICE, Product_DiscountedPrice, Product_ActualPrice, Product_discount, PRODUCT_DISCOUNT,TOTALQUANTITY, AVAILABILITY,SHIPPINGTIME,[BINDING], IMAGE_LOCATION ,PRODUCT_SHORTDESCRIPTION , AUTHORNAME1,AUTHORNAME2,AUTHORNAME3,AUTHORNAME4, AUTHORNAME5, PUBLICATIONDATE, --CASE WHEN ISNULL(TOTALQUANTITY,0)>0 THEN DBO.GETPRODUCTDEALPRICE(PS.ID_PRODUCT) ELSE 0 END DEAL_PRICE, CAST(0 AS FLOAT) DEAL_PRICE,ID_VENDOR,ISNULL(Is_IndianVendor,1) Is_IndianVendor FROM ProdList PL INNER JOIN TABLE_PRODUCTSEARCHNEWSEARCH PS ON ROWNUMBER BETWEEN ' + cast(@PAGENUMBER as nvarchar) + ' AND '+ cast((@PAGENUMBER+@PAGESIZE) -1 as nvarchar) + ' AND PS.id_product = PL.id_product'-- AND PS.PRODUCT_ACTUALPRICE = PL.PRICE ' print @sql EXEC sp_executesql @sql END