Link to home
Start Free TrialLog in
Avatar of MariaHalt
MariaHaltFlag for United States of America

asked on

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)
ASKER CERTIFIED SOLUTION
Avatar of tigin44
tigin44
Flag of Türkiye image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MariaHalt

ASKER

tigin44:  I tried it...it's even slower now, 35 sec to 65 seconds :(  Any ideas?
Tried it again, it's back to 35 seconds...still no difference.
SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Icochan, I also said I don't know how to use FullText, sounds like I'm half way there...what's Contains/ContainsTable?  
Icohan.  Looked it up myself, cool, thanks, much better!  I'll split the points.
Needed to setup the FullText and then use the ContainsTable.