Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thisman
Starting Member
3 Posts |
 Posted - 2004-08-19 : 08:32:10
|
Hello!I'm having a problem with a query  The thing is, I have a text field (up to 1000 words or so) in one table and keywords in another table. I want to select a specific text field and check if one (or more) of the keywords exists in it (which can be 100000-1000000).One textfield -> n keywords.Now I'm using something like this: SELECT * FROM keyword k, where 'Block of text to search in bla bla' like ('%' + k.word + '%')This is of course terrible and takes forever to compute. If any of you have better query and/or index suggestions please speak up  Thanks in advance,thisman |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
thisman
Starting Member
3 Posts |
Posted - 2004-08-19 : 10:55:33
|
Ok - thanks for the reply!I used a tally table and by splitting the text into seperate words and comparing them to the keywords, the total time went from 2 minutes to 30 seconds. This is still too slow  Any suggestions?Thanks in advance,thisman |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-19 : 11:17:33
|
| well how many records do you have?Go with the flow & have fun! Else fight the flow :) |
|
|
|
thisman
Starting Member
3 Posts |
Posted - 2004-08-20 : 05:14:05
|
| The tally (text block split into seperate words) is about 300 - 500 fields and the keywords table is 100,000 - 1,000,000 fields.Then I use something like this:SELECT * FROM keywords k, tally t WHERE t.word LIKE k.word + '%'thisman |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-20 : 06:53:43
|
| the time acctaly seems normal to me for that size and comparison type... i really don't see how it could go faster...maybe if you split the whole thing into alphabetic ranks...Go with the flow & have fun! Else fight the flow :) |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-08-20 : 14:48:44
|
| Why not extract the single words from your text field into another table and search against it? How many rows are there in your text table, or are you only searching 1 row at a time?--Ken"Knowledge is a process of piling up facts; wisdom lies in their simplification." |
|
|
|
|
|
|
Comparing text to list