Keyword query for reporting using fulltext index

[rcc69]

I have a table with 40 million rows containing a column defined as varchar(max) with a full text index on this column, other columns are department, date, and key. We have a list of issues and their associated keywords that we need to determine how many times and in which records these issues/keywords appear within a given timeframe for a specific department. An issue can have multiple sets of keywords, since the issue can be described in several ways. Anyone know of a way to do this without a cursor? Using SQL Server 2012.
Thanks.

[Rabbit]

Probably. We would need to know what the data looks like. Some mock data and results would help.

You can also read this documentation by Microsoft on how to query a full text index: http://msdn.microsoft.com/en-us/library/ms142583.aspx

[rcc69]

I can query using full text, and am getting the results I need, but with a cursor, and previous iterations of the loop are keeping locks, so one time it will be fast next it will never return.

DATA
text table
key department date text
1 1 3/1/2013 customer tripped and fell
2 1 3/2/2013 satan visited
3 1 3/3/2013 susan fell

Issue table
key issue
1 customer falls

Keyword table
id keywords IssueID
1 fell 1
2 tripped 1

REPORT

issue keyword date dept text
customer falls fell 3/1/2013 1 Customer tripped and..
3/3/2013 1 Susan fell
total 2
tripped 3/1/2013 1 Customer tripped and..
total 1
total 2

[Rabbit]

You can use the CONTAINS predicate from that article I linked above to return the matching rows. Then you take the results of that and aggregate it to get your final report.

[rcc69]

You can use the CONTAINS predicate from that article I linked above to return the matching rows. Then you take the results of that and aggregate it to get your final report.

This process works for one record of the keyword table, but we have a hundred.

I am currently looping through each record in keyword table and inserting results into a report table, from there I can query for the report. But, I guess, cursor concurrency seems to be an issue and response time can range from 2 minutes to 2 hours on approximately the same dataset. During non production times with no other processes on these tables.

ALTER Procedure [dbo].[usp_KeywordOccurance_Lastweek]
AS
BEGIN
begin try
declare @keyword nvarchar(max)
declare @issue int
truncate table keyword_occurance_lastweek
declare @Date nvarchar(50)
set @Date = cast(dateadd(ww,-1,getdate()) as nvarchar(50))
declare db_cursor_LastWeek cursor for
select issueid, keyword
from keyword 
open db_cursor_LastWeek
fetch next from db_cursor_LastWeek into @issue, @keyword
while @@FETCH_STATUS = 0
begin
declare @sql nvarchar(max)
set @sql = 'INSERT INTO [dbo].[keyword_occurance_lastweek]([Issue],[Keyword],[department],[Text])'
set @sql = @sql + '(select distinct ''' + cast(@issue as nvarchar(50)) + ''' as issue, ''' + @keyword + ''' as keyword, t.department,t.text from text t where contains((text),'''
if charindex(' ',@keyword) > 0
begin
 set @sql = @sql + 'near(' + replace(@keyword,' ',',') + ')'
 end
 else
 begin
 set @sql = @sql + @keyword
 end
 set @sql = @sql + ''') and log_time > ''' + @Date + ''' and Dept = 1)'
exec (@sql)
--exec sp_executesql @sql,N'@return int output', @return output
fetch next from db_cursor_LastWeek into @issue, @keyword
end
if CURSOR_STATUS('global','db_cursor_LastWeek') >= 0 
begin
  close db_cursor_LastWeek
  deallocate db_cursor_LastWeek
end
end try
begin catch
if CURSOR_STATUS('global','db_cursor_LastWeek') >= 0 
begin
  close db_cursor_LastWeek
  deallocate db_cursor_LastWeek
end
end catch
end

[Rabbit]

I know you have a keyword table with multiple rows. There's nothing stopping you from using the CONTAINS predicate on all the rows instead of one by one.

[rcc69]

I've tried for a month now, hence the question. Any ideas? I can or everything in keywords for each issue and reduce loops but not eliminate them. What am I missing?
Thanks