SQL Server Full-Text Search - how to optimize our fulltext search

Author

how to optimize our fulltext search

chemInformatic

2005-10-27, 7:56 am

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:)

John Kane

2005-10-27, 7:56 am

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//defau...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/d...005ftsearch.asp

SQL Server 2005 - 7x faster than SQL 2000 Full-Text Indexing
http://spaces.msn.com/members/jtkan...members/jtkane/

"chemInformatic" <cdfuwu@hotmail.com> wrote in message
news:1130294052.731749.135960@g47g2000cwa.googlegroups.com...
> 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:)
>

Sponsored Links