9

I have a database table with several columns; most of them are VARCHAR(x) type columns, and some of these columns have an index on them so that I can search quickly for data inside it.

However, one of the columns is a TEXT column, because it contains a very large amount of data (23 kb of plain ascii text etc). I want to be able to search in that column (... WHERE col1 LIKE '%search string%'... ), but currently it's taking forever to perform the query. I know that the query is slow because of this column search because when I remove that criteria from the WHERE clause the query completes (what I would consider), instantaneously.

I can't add an index on this column because that option is grayed out for that column in the index builder / wizard in SQL Server Management Studio.

What are my options here, to speed up the query search in that column?

Thanks for your time...

Update
Ok, so I looked into the full text search and did all that stuff, and now I would like to run queries. However, when using "contains", it only accepts one word; what if I need an exact phrase? ... WHERE CONTAINS (col1, 'search phrase') ... throws an error.

Sorry, I'm new to SQL Server

Update 2 sorry, just figured it out; use multiple "contains" clauses instead of one clause with multiple words. Actually, this still doesn't get what I want (the exact phrase) it only makes sure that all words in the phrase are present.

Improve this question

5 Answers 5

Reset to default
11

Searching TEXT fields is always pretty slow. Give Full Text Search a try and see if that works better for you.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

The link is not working anymore. Please fix if possible!
The link is now fixed
6

If your queries are like LIKE '%string%' (i. e. you search for a string inside a TEXT field), then you'll need a FULLTEXT index.

If you search for a substring in the beginning of the field (LIKE 'string%') and use SQL Server 2005 or higher, then you can convert your TEXT into a VARCHAR(MAX), create a computed column and index this column.

See this article in my blog for performance details:

Improve this answer

Comments

5

You should be looking at using Full Text Indexing on the column.

Improve this answer

Comments

0

You can do complex boolean querying in FTS; like

contains(yourcol,'"My first sting" or "my second string" and "my third string"')

Depending on your query ContainsTable or freetexttable might give better results.

If you are connecting through .Net you might want to look at A google full text search

Improve this answer

2 Comments

I just tried your variation, and it doesn't work; firstly, it requires the use of ' and not ", and second adding second or multiple words does not work if I use either ' or " in the query
My mistake you need a single ' at the end the syntax is Contains (* or colname,' " your data " and "some other data" ') msdn.microsoft.com/en-us/library/ms187787.aspx
0

And since nobody has already said it (maybe because it's obvious) querying LIKE '%string%' bypasses your existing indexes - so it'll run slow. Hence - why you need to use full text indexing. (which is what Quassnoi said).

Correction - I'm sure I learnt this, and always believed it - but after some investigating it (using wildcard at the start) seems OK? My old regex queries run better with likes!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.