MS SQL search on ntext

Question

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?

philh · Accepted Answer

Look up sp_fulltext_catalog in Books Online.

drforbin1970 · Answer

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.