CODEWITHSUNDEEP
sql-servert-sqlcursorfull-text-searchtext-search
Wolfgang Sohst
Wolfgang Sohst

Reputation: 1

How to speed up search for many different keywords in a given text

I'm looking for the appearance of a couple of key words, stored in a table named "EmailKeywords", within a search string passed with the variable @Searchtext. I accomplished this by the stored procedure written below, which generally works fine. But with many keywords to look for, this takes quite a lot of time because I've to loop through the search text with every keyword.

Does anybody have an idea how to speed up this search? Any hint is welcome.

ALTER PROCEDURE BodyTextLookForKeywords 

    @Searchtext ntext

AS

DECLARE @Found int = 0
DECLARE @Keyword nvarchar(30)

-- Loop through all the listed key words
DECLARE SearchCursor Cursor For SELECT Searchword from EmailKeywords For Read Only

Open SearchCursor
While (0=0) Begin
    Fetch Next
        From SearchCursor
        Into @Keyword
    If (@@Fetch_Status <> 0) 
        Break
    Else
        Begin
            Set @Found = (SELECT PATINDEX('%' + @Keyword+ '%', @Searchtext))
            If @Found > 0 Break
        End
    End
Close SearchCursor
Deallocate SearchCursor

If @Found=0 select 0 as SResult else select 1 as SResult

Upvotes: 0

Views: 63

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

You should avoid using cursors if you can do same thing without it. It is slow, it is an iterative solution. In your case you don`t need any cursor here, because you can select directly from table

IF EXISTS(SELECT * FROM EmailKeyword
WHERE PATINDEX('%' + Searchword + '%', @Searchtext)) > 1)
SELECT 1 as SResult ELSE SELECT 0 as SResult

Upvotes: 1

dario
dario

Reputation: 5269

You can reduce you Stored Procedure's body to this:

SELECT @Found = COUNT(*)
FROM EmailKeywords
WHERE PATINDEX('%' + Searchword + '%', @Searchtext) > 0;

SELECT CASE WHEN @Found > 0 THEN 1 ELSE 0 END AS Result;

Upvotes: 0

Related Questions