MS SQL search on ntext

[sampsas23]

I am having a hard time with trying to find a good solution to searching on a ntext field.
It is for resumes and the max 8000 on varchar wasn't large enough, so I had to switch to a text field. I just want to search on the field, but it takes forever and times out most of the time. Even with a "RequestTimeout"

The query looks like this:
SELECT DISTINCT u.UserID, u.Fname,u.Lname,u.Email,u.DateRegistered,u.Telephone, u.user_type
FROM Users u, E_Resume er
WHERE er.UserID = u.UserID
AND u.RC = #VARIABLES.RC#
AND er.resume LIKE ('%#LCase(FORM.keyword)#%')

I was going to try to use Full-Text searching, but so far the ISP hasn't been able to let me create the "catalog" because of permission issues.

I tried creating a view, but it doesn't make it faster.

I haven't found a way to index, because it is a "text" datatype.

Does anyone have any ideas on how I can fix this?

[pete_freitag]

If you could enable full text searching, that does create a text index of the field, and would solve your problem. I'd ask your ISP again if they can create this for you.

The only thing I can recommend is that you use the cfqueryparam tag, this is both for added security, and also improves performance.

[MikerRoo]

You need to get that text searching enabled or use verity.

In text search use CONTAINS() or FREETEXT(), not LIKE ('%#LCase(FORM.keyword)#%').

When you start a like string with a wildcard, any index you have is ignored. SQL is forced to do a table scan.

The text search functions usually perform better in such cases.

In extreme cases you can "digest" each text column into a relation table and a table of words. This adds a little to the storage and update overhead but it eliminates the need for wildcards or text searches.
The "digest" technique, especially coupled with hash indexes can yield quantum leaps in search performance.

[drforbin1970]

Full-text indexing is the way to go for text fields, push for it. If that is not an option, perhaps you could narrow your search a bit.

Run part of your query first to find matching records (without text field search) and load into a table variable:

declare @index_table table (UserID varchar(100) )
INSERT @index_table
SELECT DISTINCT u.UserID
FROM Users u, E_Resume er
WHERE er.UserID = u.UserID
AND u.RC = #VARIABLES.RC#

Then join the @index_table to your resume table and run your text search:
SELECT DISTINCT u.UserID, u.Fname,u.Lname,u.Email,u.DateRegistered,u.Telephone, u.user_type
FROM @index_table u INNER JOIN E_Resume er
ON er.UserID = u.UserID
WHERE er.resume LIKE ('%#LTrim(RTrim(LCase(FORM.keyword)))#%')

This way, you should only be searching resume records for matching UserID, instead of ALL resume records.
There are other ways to do it without using a table variable, but I added it here so you can view the output, if needed.

[sampsas23]

Well I talked to the ISP. They say it is possible.

Now I tried to create it through Transact-SQL, but haven't had any luck. Has anyone created an full text index with transact SQL?

I first created and index on ResumeID, but the code below won't work
CREATE FULLTEXT CATALOG EResume AS DEFAULT
CREATE FULLTEXT INDEX ON ExpressResume(Resume) KEY INDEX ui_Resume

Does anyone know why or have any code that does work?

[philh · Accepted solution]

Look up sp_fulltext_catalog in Books Online.

[sampsas23]

Thank you Phil!

I got it all working and the search is SO fast!! The client is happy happy happy!!!