By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,377 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,377 IT Pros & Developers. It's quick & easy.

Keyword query for reporting using fulltext index

P: 4
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.
Mar 8 '13 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 10K+
P: 12,430
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:
Mar 8 '13 #2

P: 4
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.

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


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
Mar 8 '13 #3

Expert Mod 10K+
P: 12,430
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.
Mar 8 '13 #4

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. ALTER Procedure [dbo].[usp_KeywordOccurance_Lastweek]
  2. AS
  3. BEGIN
  4. begin try
  5. declare @keyword nvarchar(max)
  6. declare @issue int
  7. truncate table keyword_occurance_lastweek
  8. declare @Date nvarchar(50)
  9. set @Date = cast(dateadd(ww,-1,getdate()) as nvarchar(50))
  10. declare db_cursor_LastWeek cursor for
  11. select issueid, keyword
  12. from keyword 
  13. open db_cursor_LastWeek
  14. fetch next from db_cursor_LastWeek into @issue, @keyword
  15. while @@FETCH_STATUS = 0
  16. begin
  17. declare @sql nvarchar(max)
  18. set @sql = 'INSERT INTO [dbo].[keyword_occurance_lastweek]([Issue],[Keyword],[department],[Text])'
  19. 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),'''
  20. if charindex(' ',@keyword) > 0
  21. begin
  22.  set @sql = @sql + 'near(' + replace(@keyword,' ',',') + ')'
  23.  end
  24.  else
  25.  begin
  26.  set @sql = @sql + @keyword
  27.  end
  28.  set @sql = @sql + ''') and log_time > ''' + @Date + ''' and Dept = 1)'
  29. exec (@sql)
  30. --exec sp_executesql @sql,N'@return int output', @return output
  31. fetch next from db_cursor_LastWeek into @issue, @keyword
  32. end
  33. if CURSOR_STATUS('global','db_cursor_LastWeek') >= 0 
  34. begin
  35.   close db_cursor_LastWeek
  36.   deallocate db_cursor_LastWeek
  37. end
  38. end try
  39. begin catch
  40. if CURSOR_STATUS('global','db_cursor_LastWeek') >= 0 
  41. begin
  42.   close db_cursor_LastWeek
  43.   deallocate db_cursor_LastWeek
  44. end
  45. end catch
  46. end
Mar 8 '13 #5

Expert Mod 10K+
P: 12,430
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.
Mar 8 '13 #6

P: 4
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?
Mar 8 '13 #7

Post your reply

Sign in to post your reply or Sign up for a free account.