Re: FTS query performance on SQL 2005

I'm struggling with the same issues as you.

Basically what i do is bank on the fact that it is rare for most people to
look beyond the first page of results, so I write the results of the search
to a table, and return it to a data reader displaying the first 25 results
and a count of all search results. Repeat searches go against the cached
table.

The optimizations are

1) use 64 bit
2) use a high resource_usage keeping in mind this can cause locking
3) reorganize your catalogs frequently
4) set ft crawl bandwidth (max) and ft notify bandwidth (max) to 0
5) max full-text crawl range to the number of cpu's on your system
6) convert your binary data to text


and you get the best bank for your buck by placing the full-text catalog on
its own disk subsystem and controller with the fastest disks available. RAID
5 offers best read performance, but for frequently updated catalogs use raid
10.

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



"Bahama Joe" <someone@xxxxxxxxxxxxx> wrote in message
news:epadrr9%23GHA.4712@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
referring to? I believe I have applied all optimizations that I've been
able
to find in the various posts and online docs.

I know that I can limit the size of the resultset with top_n_by_rank, but
in
this case, I'm trying to get back the full set of results. Another form of
my query is to do a "select count(*)", which has the same response times.
I
believe this is because the full resultset is returned from the FTE back
to
SQL Server, and then the count is taken on that. Is there a way to
structure
the query to tell the FTE that you just want the count of results, so that
it doesn't ship the full results back to SQL Server?

Essentially, I'm trying to create a search engine, where the results will
be
displayed back to the user in paginated form, so it will always display a
subset of the resultset, but I'd like to also display "Showing results
1-20
of 40,000", so I need a way to get the size of the resultset.

With regard to hardware, would you expect that I will get the most bang
for
my buck by a) spreading my data across more disks (via RAID) in a single
server, b) creating a cluster of separate servers, c) adding more memory,
or
d) adding more CPU's?

Thanks.





.

Relevant Pages

  • Re: Strange Recordset/Query Problem
    ... > display in SQL Server. ... table names for each field in the SELECT clause, ON clauses, and WHERE ... Also, SQL Server is not case sensitive, so you can save your eyes by using ... the IF statement executes the first condition as it should, ...
    (microsoft.public.inetserver.asp.general)
  • Re: sqlCeResultSet readPrevious() method doesnt work always after a readAbsolut(currentPosition)
    ... > In my program i use a sqlCeResultSet for accessing my data. ... > When i execute a readPreviousafterwards, the resultset stays at the ... > sql server compact edition 3.1, ... > for me that seems to be a bug in the sql server compact edition. ...
    (microsoft.public.sqlserver.ce)
  • Strange Recordset/Query Problem
    ... Access 2000 database to a SQL Server 2000 database. ... display in SQL Server. ... the IF statement executes the first condition as it should, ...
    (microsoft.public.inetserver.asp.general)
  • Re: When do selects execute for multiple resultsets
    ... SQL Server executes each query and stops when its ... When the first resultset is ... The CommandTimeout is measured from the time you execute the query until the ...
    (microsoft.public.dotnet.framework.adonet)
  • MSHFlexgrid not displaying data from ADO data control
    ... if I put it onto a clients machine none of the ... MSHflexgrids display any data. ... The MSHflexgrids data sources are ADO data controls which are ... The SQL Server uses windows network authentication to allow access and I ...
    (microsoft.public.vb.general.discussion)