FTS capabilities + questions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tmueller@teewebco.com - 09 Nov 2006 18:47 GMT

Hello,

I have some questions about Full Text Search and how to handle a
scenario that I have. The database that I am going to hit up against
has 80 tables and over 250 fields that will be searched for FTS. This
database has 600 + tables in it so 80 is needed to make the search
meaningfull for the user.

What is the best method to use FTS against so many tables?

Could I build a massive query that dumps the results into a single
table for searching?
Can I use the 80 tables and use some kind of dynamic query to do my
joins so that it scales?

I know this problem isnt unique to our company but just wondering how
some of the gurus have solved this problem?

Thanks in advance.

Reply to this Message

Hilary Cotter - 09 Nov 2006 19:37 GMT

That really depends on the queries you have. Ideally you will have a single
table which you full text index. The queries themselves against the
full-text indexes are processed separately from the queries against the
tables, so it is somewhat asynchronous.

Adding this asynchronous nature to 80 tables means that you do not have a
scalable solution.

HTH

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hello,
>
[quoted text clipped - 15 lines]
>
> Thanks in advance.

Reply to this Message

teewebco - 09 Nov 2006 19:41 GMT

Thanks for the fast response.

How about doing the queries up front and stuffing the results into a
single field that is searchable?

Is this the preferred method when encountering many tables like me?

> That really depends on the queries you have. Ideally you will have a single
> table which you full text index. The queries themselves against the
[quoted text clipped - 39 lines]
> >
> > Thanks in advance.

Reply to this Message

Hilary Cotter - 09 Nov 2006 20:13 GMT

Sure, this is an option, but then you would have to full-text index this on
the fly. One other option comes to mind, and that is if you are using SQL
2005, you might be able to full-text index an index view which represents
your underlying tables.

Signature

> Thanks for the fast response.
>
[quoted text clipped - 47 lines]
>> >
>> > Thanks in advance.

Reply to this Message

teewebco - 09 Nov 2006 21:31 GMT

Ill take a look at that. Thanks!

Reply to this Message