Solved

How to quickly search a NVARCHAR(MAX) FIELD?

Posted on 2011-03-02
7
1,584 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 40

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

717 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