how to optimize our fulltext search

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chemInformatic - 26 Oct 2005 03:34 GMT

configuration
hardware: cpu 4*1.5G RAM 2G
software: windows 2000 server,sql server 2000
data: records >40 million ,spaceused > 14G
fulltext variables: Number of items:>10 million,Number of Keys >1
million ,spaceused of

Catalogue >500MB
Note:the type of the column indexed is ntext. the contents of records
are some special defined

strings, such as "alksdj jklasj ssfgs".

the test results:
    no records:time used less than 1s
    records less than 100: time used about 1s
    records more than 500:time used more than 3s(I used the top 100 terms.
If not ,the time used will be more absolutely.
    if the records hited are more than 10 thousand,it will cost more than
30s or more.

i want to limit the time used within 5s. Can everybody give me some
suggestions. Thanks very much:)

Reply to this Message

John Kane - 26 Oct 2005 05:25 GMT

ChemInformatic,
Since you're using SQL Server 2000 FTS with a FT-enabled table of >10
million rows, I'd highly recommend that you use CONTAINSTABLE or
FREETEXTABLE and limit the results to 2000 via the Top_N_Rank parameter. If
you want to limit the time used within 5s, then you need to limit the
reading of the FT Catalog and the resultset coming back via Top_N_by_Rank.

This parameter limits the results to the Top N (some number) by RANK, and
improve performance by restricting the full read of the FT Catalog. For info
on the Top_N_Rank, see KB Article 240833 (Q240833) "FIX: Full-Text Search
Performance Improved via Support for TOP" at
http://support.microsoft.com//default.aspx?scid=kb;EN-US;240833

I'd also highly recommend that consider upgrading to SQL Server 2005 FTS as
soon as possible (RTM is scheduled for Nov. 2005)

SQL Server 2005 Full-Text Search: Internals and Enhancements
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sq
l2005ftsearch.asp

SQL Server 2005 - 7x faster than SQL 2000 Full-Text Indexing
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!433.entry

Regards,
John

Signature

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

> configuration
> hardware: cpu 4*1.5G RAM 2G
[quoted text clipped - 19 lines]
> i want to limit the time used within 5s. Can everybody give me some
> suggestions. Thanks very much:)

Reply to this Message