sql server 2005 stored procedure unexpected behaviour

Question

i have written a simple stored procedure (run as job) that checks user subscribe keyword alerts. when article posted the stored procedure sends email to those users if the subscribed keyword matched with article title.

One section of my stored procedure is:

OPEN @getInputBuffer

                FETCH NEXT
                FROM @getInputBuffer INTO @String

                WHILE @@FETCH_STATUS = 0

                BEGIN
                --PRINT @String

                INSERT INTO #Temp(ArticleID,UserID)             
                SELECT  A.ID,@UserID
                FROM    CONTAINSTABLE(Question,(Text),@String)  QQ 
                JOIN    Article A WITH (NOLOCK)  ON A.ID = QQ.[Key]
                WHERE   A.ID > @ArticleID


                FETCH NEXT
                FROM @getInputBuffer INTO @String

                END

                CLOSE @getInputBuffer
                DEALLOCATE @getInputBuffer

This job run every 5 minute and it checks last 50 articles.

It was working fine for last 3 months but a week before it behaved unexpectedly.

The problem is that it sends irrelevant results.

The @String contains user alert keyword and it matches to the latest articles using Full text search. The normal execution time is 3 minutes but its execution time is 3 days (in problem).

Now the current status is its working fine but we are unable to find any reason why it sent irrelevant results.

Note: I have already removing noise words from user alert keyword. I am using SQL Server 2005 Enterprise Edition.

Answer 1

Does the CONTAINSTABLE(Question,(Text),@String) work in an ad hoc query window? If not it may be that your Full Text search indexes are corrupt and need rebuilding

• Rebuild a Full-Text Catalog
• Full-Text Search How-to Topics

Also check any normal indexes on Article table, they might just need rebuilding for statistical purposes or could be corrupt too

• UPDATE STATISTICS (Transact-SQL)

Answer 2

I don't have the answer, but have you asked all the questions?

Does the long execution time always happen for all queries? (Yes--> corruption? disk problems?)

Or is it only for one @String? (Yes--> anything unusual about the term? Is there a "hidden" character in the string that doesn't show up in your editor?)

Does it work fine for that @String against other sets of records, maybe from a week ago? (Yes--> any unusual strings in the data rows?)

Can you reproduce it at will? (From your question, it seems that the problem is gone and you can't reproduce it.) Was it only for one person, at one time?

Hope this helps a bit!

Answer 3

I'd go along with Glen Little's line of thinking here.

If a user has registered a subscribed keyword which coincidentally (or deliberately) contains some of the CONTAINSTABLE search predicates e.g. NEAR, then the query may take longer than usual. Not perhaps "minutes become days" longer, but longer.

Check for subscribed keywords containing *, ", NEAR, & and so on.

The CONTAINSTABLE function allows for a very complex set of criteria. Consider the FREETEXTTABLE function which has a lighter matching algorithm.

Answer 4

1) How do you know it sends irrelevant results?

If it is because user reported problem: Are you sure she didnt change her keywords between mail and report?

Can you add some automatic check at end of procedure to check if it gathered bad results? Perhaps then you can trap the cases when problems occur

2) "This job run every 5 minute and it checks last 50 articles." Are you sure it's not related to timing? If job takes more than 5 minutes one time, what happens? A second job is starting...

You do not show your cursor declaraion, is it local or could there be some kind of interference if several processes run simultaneously? Perhaps try to add some kind of locking mechanism.

Answer 5

Since the cursors are nested you will want to try the following. It's my understanding that testing for zero can get you into trouble when the cursors are nested. We recently changed all of our cursors to something like this.

WHILE (@@FETCH_STATUS <> -1) BEGIN
    IF (@@FETCH_STATUS <> -2) BEGIN

        INSERT INTO #Temp(ArticleID,UserID)             
            SELECT  A.ID,@UserID
            FROM    CONTAINSTABLE(Question,(Text),@String)  QQ 
            JOIN    Article A WITH (NOLOCK)  ON A.ID = QQ.[Key]
            WHERE   A.ID > @ArticleID

    END

    FETCH NEXT FROM @getInputBuffer INTO @String
END