?
Solved

How to quickly search a NVARCHAR(MAX) FIELD?

Posted on 2011-03-02
7
Medium Priority
?
1,838 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 1000 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
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.

 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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