performance question/multiple keys

Hi All,
The table I'm using has full-text columns and also a date column thats
indexed. If I do a query on a date range and the full-text column does SQL
Server return the full-text rows first, then subset by date, or subset by
date and pass that set of rows to MSSEARCH?
I'm wondering about performance issues on tables with many rows (3 millions
or so). I'm wondering if i should break the data up into tables by day so
that i'm not doing full-text searches if I know that I'll be getting a small
subset according to a date range.
Any insight on this issue?
thanks,
John
Rows are first returned from MSSearch and then trimmed.
Partitioning is a good idea. However, how large are your results sets? If
they are small (i.e. under 500 rows) this should not be a problem.
Hilary Cotter
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
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:eBaJxILvFHA.3080@.tk2msftngp13.phx.gbl...
> Hi All,
> The table I'm using has full-text columns and also a date column thats
> indexed. If I do a query on a date range and the full-text column does SQL
> Server return the full-text rows first, then subset by date, or subset by
> date and pass that set of rows to MSSEARCH?
> I'm wondering about performance issues on tables with many rows (3
millions
> or so). I'm wondering if i should break the data up into tables by day so
> that i'm not doing full-text searches if I know that I'll be getting a
small
> subset according to a date range.
> Any insight on this issue?
> thanks,
> John
>
|||John,
First of all, it is always a good idea to get the SQL Server & OS platform
version info. Could you post the full output of SELECT @.@.version ?
Q. If I do a query on a date range and the full-text column does SQL Server
return the full-text rows first, then subset by date, or subset by date and
pass that set of rows to MSSEARCH?
A. SQL Server first queries the MSSearch service for all rows that match the
FTS query, then applies the WHERE clause filter after ALL results are
returned from the FT Catalog.
Yes, there can be performance issues with SQL Server 2000, but on which side
of the equation (FT Indexing &/or FT Search) - running a Full Population vs
running CONTAINS query are you concerned with? If the former, see the below
blog entry detailed resources.Also, review SQL Server 2000 BOL Title
"Full-text Search Recommendations". If the latter, you should review KB
article 240833 (Q240833) "FIX: Full-Text Search Performance Improved via
Support for TOP" and consider using the Top_N_by_Rank with either
CONTAINSTABLE or FREETEXTTABLE. If possible, partitioning the table into
smaller table can be helpful.
SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:eBaJxILvFHA.3080@.tk2msftngp13.phx.gbl...
> Hi All,
> The table I'm using has full-text columns and also a date column thats
> indexed. If I do a query on a date range and the full-text column does SQL
> Server return the full-text rows first, then subset by date, or subset by
> date and pass that set of rows to MSSEARCH?
> I'm wondering about performance issues on tables with many rows (3
> millions
> or so). I'm wondering if i should break the data up into tables by day so
> that i'm not doing full-text searches if I know that I'll be getting a
> small
> subset according to a date range.
> Any insight on this issue?
> thanks,
> John
>