This content has been marked as final. Show 6 replies
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.
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.
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) )
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.
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?
Look up sp_fulltext_catalog in Books Online.
Thank you Phil!
I got it all working and the search is SO fast!! The client is happy happy happy!!!