SQLTeam.com | Weblogs | Forums

Help with SQL1014 Freetext Search Optimization

sql2014

#1

I am trying provide a ranked search on my website, here is the stored procedure - I was wondering if anyone could give me suggestions on if this needs improving?

<USE Database
GO
/****** Object: StoredProcedure [dbo].[name_procedure] Script Date: 3/17/2016 3:55:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description: Site Search
-- =============================================
CREATE PROCEDURE [dbo].[name_procedure]

@language varchar(200) = '',
@body varchar(200) = '',

AS

DECLARE @topRank int

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

set @topRank=(SELECT TOP 200 MAX(RANK) FROM
FREETEXTTABLE(content, body, @body, 200))

SELECT TOP 200
ftt.RANK,
(CAST(ftt.RANK as DECIMAL)/@topRank) as matchpercent,
content.idCR,
content.title,
content.nameElement,
content.body,
content.source,
content.language,
content.flag
FROM content
INNER JOIN
FREETEXTTABLE(content, body, @body)
as ftt
ON
ftt.[KEY]=content.idCR
WHERE (
( publish LIKE @publish1 OR publish LIKE @publish2 OR publish LIKE @publish3)
AND language LIKE @language
)
ORDER BY ftt.rank DESC, title ASC

END/>