Re: how to optimize our fulltext search

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/sql2005ftsearch.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
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/


"chemInformatic" <cdfuwu@xxxxxxxxxxx> wrote in message
news:1130294052.731749.135960@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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:)
>


.

Relevant Pages

  • Re: Advice for 1-Tier architecture
    ... questions like this I HIGHLY recommend you get a good performance consultant ... in to help with review, architecture, capacity planning, configuration, ... computer as SQL Server 2005 express? ...
    (microsoft.public.sqlserver.programming)
  • Re: Database security (non-existent?)
    ... I run sqlcmd without specifying any username or pwd, ... *HUGE* security flaw - unless, ... BTW, based on this and your other post, I would highly recommend you pick ... done correctly SQL Server 2005 is pretty much as secure as ...
    (comp.databases.ms-sqlserver)
  • Re: What provider / namespace?
    ... You can only use SqlClient against Sql Server. ... You can use OleDb and Odbc ... against any OleDb or ODBC Compliant database but I'd HIGHLY recommend ... I'd highly recommend Sahil Malik's ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL Server and Windows SBS
    ... I would highly recommend AGAINST the idea of allowing unauthenticated ... I recommend calling the licensing folks. ... Q. I want to use the SQL Server component of SBS 2003 R2 Premium ... As long as the Internet users do not authenticate on the SBS ...
    (microsoft.public.sqlserver.setup)
  • Re: FTS Performance in SQL 2005
    ... we had no end of problems with SQL FTS. ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
Quantcast