return like query faster

Question

Post reply

return like query faster

lleemon13

Ten Centuries

Points: 1063
More actions
December 10, 2010 at 3:50 pm

#147896

I have a table that only has a few fields but one of them is a varchar(max) and most records have a lot of data in this field. I will call it fieldTEXT. The total number of records are 1978639.
We want to do a text search but it just takes forever.
Example
select ID
from table
where fieldTEXT like '% medical doctor %'
So I thought if I just limit it to certain rows it would be faster.
Example
select ID
from table
where fieldTEXT like '% medical doctor %'
and (ID >= 0 AND ID < 150000)
This did actually return results in about 5 seconds. So I created a sp that would loop by 150000 but it still takes forever.
There has to be a faster way to do a LIKE query where it returns in seconds instead of minutes.
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Take a look into full text indexing. This is probably your best bet for speed here, though it does require some upkeep.

- Craig Farrell

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

lleemon13 Ten Centuries Points: 1063 More actions · Answer 2

Thanks.

Here is my db version:

I also did EXEC sp_dbcmptlevel 'dbname'

RESULT: The current compatibility level is 80

When I do a select fulltextserviceproperty('isfulltextinstalled') it returns 1 but when I right click on the table 'Full-Text index' is grayed out.

Any ideas why?