January 25, 2007 at 12:55 pm
I have enabled full-text search on my db, and have created a full-text index on a table called knowledgebase.
I have a proc that uses this index as defined below. The issue I'm having is that the proc is periodically and sporadically timing out. I can pass a search string to the proc and it will time-out, then I immediately re-execute and it comes back immediately. Once it comes back, then it will return immediately every time. If I wait an hour or so, and there is no one running the proc, then I execute it again, it will timeout the first time and act as described above. It may be a coincidence that the time I wait has anything to do with it.
Has anyone had any experience with this? The proc is pretty straight forward and uses the freetexttable feature of full text search.
BTW. This occurrs in both queries in the proc. (ie. if categoryid = 0 or categoryid > 0)
Any help is truly appreciated.
Proc Definition:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE
Procedure [dbo].[SearchKnowledgeBase] @CategoryID INT=0,
@SearchPhrase
VARCHAR(1000)=''
AS
SET NOCOUNT ON
IF @CategoryID = 0
BEGIN
SELECT knowledgebaseid,
question
,
answer
,
keywords
FROM (SELECT kb.knowledgebaseid,
kb
.question,
kb
.answer,
kb
.keywords,
ct
.rank
FROM knowledgebase kb (nolock) JOIN FREETEXTTABLE(KnowledgeBase, keywords, @SearchPhrase) ct on kb.knowledgebaseid = ct.) a
ORDER BY rank DESC
END
ELSE
BEGIN
IF @CategoryID > 0
BEGIN
SELECT knowledgebaseid,
question
,
answer
,
keywords
FROM (SELECT kb.knowledgebaseid,
kb
.question,
kb
.answer,
kb
.keywords,
ct
.rank
FROM knowledgebase kb (nolock) join FREETEXTTABLE(KnowledgeBase, keywords, @SearchPhrase) ct on kb.knowledgebaseid = ct.
WHERE kb.CategoryID = @CategoryID) a
ORDER BY rank DESC
END
END
January 26, 2007 at 8:28 am
Keith,
Can I assume that the above sproc is used in a SQL Server 2005 FTS enabled database? How many rows in your Knowledgebase database? If so, then the following KB article may apply:
You may experience a 45-second delay when you run a full-text query in an instance of SQL Server 2005 that is running on a server without Internet access - http://support.microsoft.com/kb/915850
Also, how many rows are returned when the sproc times out? Substantialy more than 2000 rows? If so, you may want to restrict the number of rows via either TOP or Top_N_by_RANK parameter. If the above KB article does not resolve the time out issue.
If this is SQL Server 2000, then the above Top_N_by_RANK parameter is the only solution and limit resutls to 2000 or less. See the following KB article on Top_N_RANK:
240833 FIX: Full-Text Search Performance Improved via Support for TOP -
http://support.microsoft.com/kb/240833
Regards,
John
SQL Full Text Search - http://www.SQLFTS.com (under construction now)
SQL Full Text Search Blog - http://jtkane.spaces.live.com/
John T. Kane
July 25, 2007 at 10:17 am
Don't know if it helped the original poster, but that link you posted sorted out our FTS as we had exactly the same issue. Many thanks.
Seems odd though that MS would require the word breaker to have an internet connection and that the workaround is to make your server just a little bit less safe from attack but hey ho.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply