May 18, 2011 at 10:13 am
Ok, someone tell me there's a faster way to do this. I have a table with 37k string fields (open ended question responses) and I'm trying to extract words from it that match certain keywords which it turn map to categories so I can categorize the responses. I have a table with keywords (obviously) and it maps to categories.
There are currently about 2000 keywords and 200 categories...and this query takes 3.56 minutes to run.
select top 100 A.QuestionName, A.AnswerText, K.Description as MatchingWord, S.Description as Category
from TABLE_OBFUSCATED A
LEFT JOIN Keywords K
on CHARINDEX(' ' + K.Description + ' ',A.AnswerText)>0
INNER JOIN Subcategory S
on S.ID = K.SubcategoryID
where QuestionType = 'Open Ended'
group by A.QuestionName, A.AnswerText, K.Description, S.Description
order by A.AnswerText
May 18, 2011 at 10:45 am
Full text search might be an option (not an expert).
Otherwise please attach the actual execution plan.
May 18, 2011 at 11:20 am
Donalith (5/18/2011)
Ok, someone tell me there's a faster way to do this. I have a table with 37k string fields (open ended question responses) and I'm trying to extract words from it that match certain keywords which it turn map to categories so I can categorize the responses. I have a table with keywords (obviously) and it maps to categories.There are currently about 2000 keywords and 200 categories...and this query takes 3.56 minutes to run.
And for what you just described with them there joinage, that's probably not too bad. Ninja's right, you want Full Text Indexing with a full catalog to speed this up. This is basically a data mining type of issue.
Some google keywords you may not have thought to associate with this issue: Document searching, Find and replace. Basically you're going to keep going round to the same answer though, Full Text Index. Either that or you're going to split these into a massive table so you have some chance at indexing... which I'd personally avoid like the plague.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 18, 2011 at 11:29 am
Yup.. I've never instituted full-text searching before so I'm looking into how to set it up on SQL 2008 and how to implement it. It seems to be more what I'm looking for, though I have no idea how to set up a french thesaurus file for this.
Thanks for the input folks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply