Solved

How to quickly search a NVARCHAR(MAX) FIELD?

Posted on 2011-03-02
7
1,445 Views
Last Modified: 2012-05-11
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
Comment
Question by:MariaHalt
  • 5
7 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 250 total points
ID: 35021578
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
 

Author Comment

by:MariaHalt
ID: 35027105
tigin44:  I tried it...it's even slower now, 35 sec to 65 seconds :(  Any ideas?
0
 

Author Comment

by:MariaHalt
ID: 35027185
Tried it again, it's back to 35 seconds...still no difference.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 35029268
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
 

Author Comment

by:MariaHalt
ID: 35030020
Icochan, I also said I don't know how to use FullText, sounds like I'm half way there...what's Contains/ContainsTable?  
0
 

Author Comment

by:MariaHalt
ID: 35030267
Icohan.  Looked it up myself, cool, thanks, much better!  I'll split the points.
0
 

Author Closing Comment

by:MariaHalt
ID: 35030275
Needed to setup the FullText and then use the ContainsTable.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Service Statictic 11 30
SqlAdvisor 2016 3 28
SQL Exceptions 3 39
ORA-00923: FROM keyword not found where expected 3 34
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now