Solved

How to quickly search a NVARCHAR(MAX) FIELD?

Posted on 2011-03-02
7
1,431 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

11 Experts available now in Live!

Get 1:1 Help Now