performance question/multiple keys

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Mott - 19 Sep 2005 01:38 GMT

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

Reply to this Message

Hilary Cotter - 19 Sep 2005 03:47 GMT

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.

Signature

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

> Hi All,
>
[quoted text clipped - 13 lines]
>
> John

Reply to this Message

John Kane - 19 Sep 2005 05:18 GMT

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

Signature

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

> Hi All,
>
[quoted text clipped - 15 lines]
>
> John

Reply to this Message