• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1942
  • Last Modified:

How to quickly search a NVARCHAR(MAX) FIELD?

The field CellText is NVARCHAR(MAX).  When I leave it out of the OR clause, this executes super fast, with it in, it's soooo slow!  What can I do?  FullTextSearch is on, but I'm not sure how to use it.

DECLARE @Keyword  nvarchar( 256 ) = 'maria''s'

DECLARE @Kw nvarchar(258) = '%' + @Keyword + '%'

SELECT * FROM tempdb.dbo.mh_stuff
WHERE (
            Title like @Kw
      OR
            Name LIKE @Kw
      OR
            CellText LIKE @Kw)
0
MariaHalt
Asked:
MariaHalt
  • 5
2 Solutions
 
tigin44Commented:
you should create a full text index as the one below...
you should replace some keywords with the correct ones.. like column name, table name, index name...

USE tempdb;
GO
CREATE UNIQUE INDEX ui_someUniqueInd ON dbo.mh_stuff(someColumnName);

CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.mh_stuff(CellText)
   KEY INDEX ui_ ui_someUniqueInd
   WITH STOPLIST = SYSTEM;
GO
0
 
MariaHaltAuthor Commented:
tigin44:  I tried it...it's even slower now, 35 sec to 65 seconds :(  Any ideas?
0
 
MariaHaltAuthor Commented:
Tried it again, it's back to 35 seconds...still no difference.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
lcohanDatabase AnalystCommented:
If you created full text index as suggested above did you used full-text search syntax CONTAINS/CONTAINSTABLE or FREETEXT/FREETEXTTABLE ?

You say << When I leave it out of the OR clause, this executes super fast, with it in, it's soooo slow! >>
Did you tried maybe a UNION is fast enough without having to complicate things with SQL FULL-TEXT?

Something like:

SELECT * FROM tempdb.dbo.mh_stuff WHERE ( Title like @Kw )
UNION
SELECT * FROM tempdb.dbo.mh_stuff WHERE ( Name LIKE @Kw )
UNION
SELECT * FROM tempdb.dbo.mh_stuff WHERE ( CellText LIKE @Kw)
0
 
MariaHaltAuthor Commented:
Icochan, I also said I don't know how to use FullText, sounds like I'm half way there...what's Contains/ContainsTable?  
0
 
MariaHaltAuthor Commented:
Icohan.  Looked it up myself, cool, thanks, much better!  I'll split the points.
0
 
MariaHaltAuthor Commented:
Needed to setup the FullText and then use the ContainsTable.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now