Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help in creating SP

Author  Topic 

dipeshneema
Starting Member

6 Posts

Posted - 2010-11-29 : 05:15:03

I have a table of around 10millon records, structure of table is

ID PK
Title 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
   

- Advertisement -