Full-Text Indexing

Question

Post reply

Full-Text Indexing

Jim Kitterman

SSC Veteran

Points: 201
More actions
August 15, 2005 at 11:40 am

#87103

SQL Server 2000 on W2k server.
Database has 4 million records, Fulltext on an image column that contains html. I have a stored procedure that uses CONTAINSTABLE, when using additional criteria it doesn't return the proper results. ex.
SELECT * from
dbo.Documents bt
INNER JOIN
CONTAINSTABLE(Documents, Document, 'isabout("white" weight(.1))
  AND isabout("male" weight(.1))', 250) ct
ON (bt.DocID=ct.)
WHERE (bt.AgencyID = 5)
     AND (bt.dbid = 1)
returns 0 results, if the where clause is remove i receive 250 rows and documents that belong o agency 5 appear at the top.
I only want to retrieve 250 rows so i am using the ,250 for the containstable. If i remove this it will retrieve the correct results, but it takes forever.
Any help or hints would be greatly appreciated. I just can't seem to figure out this behavior. Thanks
JimK

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jim Kitterman SSC Veteran Points: 201 More actions · Answer 1

SQL INFO

John Kane SSCrazy Points: 2810 More actions · Answer 2

Jim,

First of all, thank you for providing the SQL INFO (@@version) as this always most helpful in troubleshooting SQL FTS issues!

Secondly, the behavior you are seeing is correct and by design, as the value '250' represents the TOP N(250) by RANK. Rank is the internal scoring or ranking method the MSSearch service has determined for this query. The following KB article describes this behavior in more detail:

240833 (Q240833) "FIX: Full-Text Search Performance Improved via Support for TOP" - http://support.microsoft.com//default.aspx?scid=kb;EN-US;240833

Most likely there are no results for "(bt.AgencyID = 5) AND (bt.dbid = 1)" within the top 250 by RANK results. What you may want to do is to set the Top_N_Rank value to 2000 (the MSSearch service is optimized for returning 2000 results) and then use TOP 250 in the SELECT list, for example:

SELECT TOP 250 * from

dbo.Documents bt

INNER JOIN

CONTAINSTABLE(Documents, Document, 'isabout("white" weight(.1))

AND isabout("male" weight(.1))', 2000) ct

ON (bt.DocID=ct.)

WHERE (bt.AgencyID = 5)

AND (bt.dbid = 1)

The Top_N_Rank parameter restricts the MSSearch service from "reading" the entire FT Catalog of your 4 million row table, and therefore performs faster than when you remove the Top_N_Rank parameter!

Hope that helps,

John

SQL Full Text Search Blog

http://spaces.msn.com/members/jtkane/

John T. Kane

Jim Kitterman SSC Veteran Points: 201 More actions · Answer 3

Thanks John, this is the solution that i came up with too, but the speed is slow. Are there any tricks or tips you have that might improve performance? Now I understand more fully how the Top_N_Rank really works. I appreciate the time you spend on this forum. Thanks a bunch.

I provided the sql information for you hoping you would respond to this one. Thank you again.

JimK