6 Replies Latest reply on Jun 14, 2006 12:22 PM by sampsas23

    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?

        • 1. Re: MS SQL search on ntext
          pete_freitag Adobe Community Professional
          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.
          • 2. Re: MS SQL search on ntext
            MikerRoo Level 1
            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.

            • 3. Re: MS SQL search on ntext
              drforbin1970 Level 1
              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.




              • 4. Re: MS SQL search on ntext
                sampsas23 Level 1
                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?
                • 5. Re: MS SQL search on ntext
                  philh Level 1
                  Look up sp_fulltext_catalog in Books Online.
                  • 6. Re: MS SQL search on ntext
                    sampsas23 Level 1
                    Thank you Phil!

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