August 15, 2005 at 11:40 am
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
August 15, 2005 at 12:30 pm
SQL INFO
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
August 16, 2005 at 12:30 am
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
August 16, 2005 at 9:23 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply