Re: LIKE, CONTAINS and Full Text Indexing

From: William Ryan eMVP (bill_at_NoSp4m.devbuzz.com)
Date: 03/07/04 

Date: Sun, 7 Mar 2004 12:05:36 -0500

Hi Michael:
"michael" <michael@hockstein.org> wrote in message
news:F60367A8-3ABC-47B4-BD66-8586BC57E0D9@microsoft.com...
> I'm interested in searching a column of type text for specific words
withing the field. I need the search to be rapid. Currently, I use a LIKE
predicate in the RowFilter of a DataView.
>
> For best results, should I explore Full Text Indexing and using a CONTAINS
predicate? Would it be best to execute this on the SQL Server rather than
from within my application's DataSet? Do I need Full Text Indexing to use
CONTAINS?
>
> Michael

That's really hard to tell without knowing more about the app. however, as
a general guideline I'd mention this. Full Text Indexing is not a trivial
feature of SQL Server. It's extremely powerful but it also needs to consume
some resources to accomplish this. As such, comparing something that runs
on a server, takes a fair amount of space to implement in most cases etc to
a method of a relatively small class running client side isn't exactly
apples to apples.

If what you need can be easily accomplished with a Like Statement, then full
text indexing probably isn't necessary. For instance, you wouldn't need it
to find all of the people who's last name begins with "O'" in a LastName
column. If on the other hand your app is a legal app that searches bills
and proposed bills in Congress and you need to find everything document
dealing with Derivatives, then Like and a DataView probably aren't the way
to go.

In general, you want to pull over as little data as possible with ADo.NET
disconnected objects. And ideally you don't want to have to constantly fire
select statements. SQl Server's optimizer is a lot better sutied to big
heavy queries than a rowfilter. conversely, if you have 25 records and you
need to find a small subset making a trip back to the server is silly and
wasteful when a Rowfilter or datatable.select can do it for you.

In a nutshell, it really depends on the total number of records, the record
size and how much precision you need in your search.

Also, the Rowfilter can't use Contains so if you need that syntax, it won't
get you there.

HTH,

Bill

Relevant Pages

  • Re: New indexing algorithm??
    ... I took a look at a couple of the CopperEye whitepapers, ... SQL Server clustered index is optimized for range retrievals at ... SQL Server's indexing flexibility, once index keys are decided, is ...
    (microsoft.public.sqlserver.server)
  • Re: accent insensitive search (any definitve solution?)
    ... Text Search with SQL Server 2000 and the indexing services. ... This is the installation program we will use to ... You will then have to create a schedule for the indexation process. ...
    (microsoft.public.inetserver.indexserver)
  • Re: Full Text Indexing Option Disabled
    ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ... feature because of the bug in SQL Server Enterprise Manager that disables ... It implies that because the account is ...
    (microsoft.public.sqlserver.fulltext)
  • Re: New indexing algorithm??
    ... SQL Server 2005 doesn't have technology like this built in. ... The article states, "CopperEye indexing as ... > if SQL Server will be coming up with a better indexing algorithm than ...
    (microsoft.public.sqlserver.server)
  • Re: SQL database challenge
    ... Indexing but I haven't researched it yes. ... >> I am very new to using the SQL language and SQL Server ... >> great if the user could click on a name and pull up that ... >> digital sender which saves a Tiff file, ...
    (microsoft.public.sqlserver.datamining)