Click here to Skip to main content
15,903,388 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am using sqlserver 2005 . I create a search proc using freetext, every thing is working fine but after every 15 min the proc take more than 45 sec to execute .Please help me to solve
this problem.


ALTER PROCEDURE [dbo].[sp_ftsearchOnResumetbl]
(
	@StructuredXMLResume_ExecutiveSummary							NVARCHAR(100),
	@StructuredXMLResume_Qualifications_QualificationSummary		NVARCHAR(100)
)
AS
BEGIN
DECLARE @searchWord NVARCHAR(200)
	SET @searchWord= @StructuredXMLResume_ExecutiveSummary 
               +' '+@StructuredXMLResume_Qualifications_QualificationSummary
	BEGIN TRY
		SELECT ResumeId,StructuredXMLResume_ExecutiveSummary as 
                Exesummary,StructuredXMLResume_Qualifications_QualificationSummary as Qualifications FROM Resume
		INNER JOIN FREETEXTTABLE(Resume, 
             StructuredXMLResume_ExecutiveSummary,StructuredXMLResume_Qualifications_QualificationSummary),@searchWord) 
              AS KEY_TBL
		ON ResumeId=KEY_TBL.[KEY]
	END TRY
	BEGIN CATCH
		RETURN 1
	END CATCH
END

Thanks,
Partha
Posted
Updated 15-Apr-11 21:34pm
v3
Comments
OriginalGriff 16-Apr-11 3:27am    
Perhaps if you shared the proc with us, and how you call it, we might be able to be more help? At the moment we are working in the dark...
ParthaDinda 16-Apr-11 3:36am    
please check the question i updated it.

1 solution

This is a difficult question to answer without looking at the queries / stored procedures.

1) Make sure you have the right indexes on your tables.
2) Make sure your tables are properly normalized

From a query perspective, you might want to take a look at some articles on SQL query tuning and performance.
If you do a search on the internet, you will surely find some links for this.
 
Share this answer
 
Comments
ParthaDinda 16-Apr-11 3:42am    
my table has only four columns and primary key (one columns identity) is the index and one foreign key.this proc execute instant but after every 15 min it take 48 sec.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900