issues with full text search (especially views and pick lists)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

baraka - 26 Jul 2005 02:36 GMT

Hi, I am trying to implement a global full text search on our SQL
Server. Our app has several entities that are stored in the DB. I would
like to be able to search for 'John Doe' and get results in all types
of entities. Problem is:

-    FT Search does not crawl views. Unfortunately, each entity type in
our system is not stored in full in one table. This is because we are
using pick lists and look ups. For instance: Industry type is stored as
a code that represents an entry in an industries table. However, all
these values are joined in a view to create all the required fields for
the entity a full text search query and index are created per table.
What would be an effective way to work around this problem? (I don't
see any solution to this problem in Yukon either.)
-    Ranking: both 2000 and Yukon do not allow for merging of rankings
from several tables/queries. However, it is important to us to be able
to display results from several tables and sorted in a logical way. Any
suggestions/work arounds?
-    Performance and scalability: How many rows and/or how many GB can I
have in my DB and still get good FT search query performance (less than
5 seconds)? (I need data for both 2000 and Yukon)
-    Same with regards to indexing: ideally we would like to keep the
index up to date in real time. We would like to use the track changes
feature. However, our app allows many users to be logged in and
edit/delete/add entries in the DB. What is the maximum amount of DB
transactions per minute (second?) that will still allow us to keep the
index updated in real time? (I need data for both 2000 and Yukon)
-

I would greatly appreciate any suggestions/tips/info/workaround.

Thanks!

Reply to this Message

John Kane - 26 Jul 2005 05:22 GMT

baraka,
Unfortunately, SQL Server 2000 FTS does not directly support the FT Indexing
of views. However, you can include FTS queries *in* views, but not create FT
Indexes *on* views in SQL Server 2000.

Since you're testing Yukon (SQL Server 2005), have you downloaded and
installed the June CTP version? If not, I'd highly recommend that you do so
and then create one of your views as an "indexed view" as FT Indexing an
indexed view is supported in SQL Server 2005.

Relative to merging ranking for multiple tables (in SQL 2000 or SQL 2005),
try the following example to dump them into a
temp table, the below option as a few advantages, especially as it relates
to "summing" via the MAX function the RANK value as well as using a group
by:

CREATE TABLE #FTSQueryResult (PK_ID int, Rank int)
INSERT #FTSQueryResult
SELECT PK, FTSTable.Rank FROM . . . (and the rest of your 1st query)
INSERT #FTSQueryResult
SELECT PK, FTSTable.Rank FROM . . . (and the rest of your 2nd query)
Select PK_ID, Max(Rank) as Rank From #FTSQueryResults
Group by PK_ID

In regards to performance and scalability, for SQL 2000 its less an issue
with amount of text, and more a factor of the number of rows and the
language of the text in the FT-enable columns as well as your server's
configuration - number & speed of procs, amount of RAM and speed of your
disk drives. Review SQL 2000 BOL title "Full-text Search Recommendations"
for more info! As for SQL 2005, I've worked with a customer (now in Yukon
TAP) who FT-indexed a table with 180 Million rows in 8 hours, while using
SQL 2000 on the same server and same database and FT-enable table took over
6 weeks to complete! Checkout the case study of an internal MS application
"SQL Server 2005 - 7x faster than SQL 2000 Full-Text Indexing" at
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!433.entry

I'd highly recommend that you review all of the links at "SQL Server 2000
Full-Text Search Resources and Links"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry

Regards,
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Hi, I am trying to implement a global full text search on our SQL
> Server. Our app has several entities that are stored in the DB. I would
[quoted text clipped - 27 lines]
>
> Thanks!

Reply to this Message