LIKE, CONTAINS and Full Text Indexing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi Michael:
michael said:
I'm interested in searching a column of type text for specific words
Click to expand...
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
Click to expand...
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
Click to expand...

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
 
Thanks Bill. I'll first set up the server to allow FTI and then test to see which works faster: a) a full text search on the server or b) a RowFilter to data already in the DataSet. I'm also going to look for the Full Text Indexing news group

Michael
 
Thanks for Bill's wonderful response!

Hi Michael,

Bill's reply is exact what we think when designing the search in our
applications. If you have any further questions, please feel free to post
them in this newsgroup. I will be glad to help. Here is another newsgroup
for SQL Server full text index:

microsoft.public.sqlserver.fulltext

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Well I'm amazed at the speed of RowFiltering within a DataSet of my application! The RowFilter was applied to an 8300 row table on a nvarchar column of 255 characters. The filtering occurred in the blink of an eye, almost like having a SQL engine living in the DataSet

How did it do that

Michael
 
Hi Michael,

The DataView object is an index of the source DataTable. So when you create
a new DataView from the DataTable or change the RowFilter, the index is
recreated. It is much faster than regenerating data.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top