|
|
|
date: Tue, 15 Jul 2008 09:52:39 +0100,
group: microsoft.public.sqlserver.fulltext
back
Incorrect RANK results returned
Morning all,
I'm having problems with my FTS rankings on our SQL2005 database and I'm a
bit stumped as where to start looking at what the the problem might be.
The simplified SQL is as follows:
---------------------------------------------------------------------------------------------
DECLARE @SEARCH nvarchar(2000)
SET @search = '"touchpoints"'
SELECT TOP 250 [KEY], SUM(Rank) AS Rank, content_live.title
FROM
(
SELECT Rank, [KEY] from CONTAINSTABLE(content_live,(title), @search)
) AS ftt
INNER JOIN content_live ON content_live.id = [KEY]
GROUP BY
[KEY], content_live.title
ORDER BY Rank DESC
---------------------------------------------------------------------------------------------
This returns me the following example results:
---------------------------------------------------------------------------------------------
KEY RANK TITLE
3304 192 TouchPoints Site: Using TouchPoints
3324 96 TouchPoints Site: Contact Us
3267 64 TouchPoints Site: TouchPoints Home
2137 48 Vision vs. practice seminar - agencies, media owners and
clients to discuss their views on TouchPoints
3273 32 TouchPoints Site: How we do it
---------------------------------------------------------------------------------------------
My problem is that the 3rd result with a Rank of 64 should have a rank of
192 based upon the occurence of the word "TouchPoints".
If I rebuild the index then I get the correct results back, but with the
knowledge that it'll start going wrong again.
The table in question is set to Track Changes : Automatic.
Title field is VARCHAR(1000).
Version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007
16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition
on Windows NT 5.2 (Build 3790: Service Pack 2)
Any suggestions of what might be causing this problem?
Are there some settings that I need to change to ensure that the Rankings
get updated correctly?
Do I need to go through the crawl logs to see if there is a problem that
stops population (even though I'm sure that the record in question hasn't
been changed).
Anyone else seen this issue?
Many thanks,
Larry.
date: Tue, 15 Jul 2008 09:52:39 +0100
author: Larry Neylon
Re: Incorrect RANK results returned
You may need to reorganize your catalog/index from time to time. You will
occasionally get results like this if you are using change tracking.
"Larry Neylon" wrote in message
news:%23zSFkgl5IHA.4776@TK2MSFTNGP05.phx.gbl...
> Morning all,
>
> I'm having problems with my FTS rankings on our SQL2005 database and I'm a
> bit stumped as where to start looking at what the the problem might be.
>
> The simplified SQL is as follows:
> ---------------------------------------------------------------------------------------------
>
> DECLARE @SEARCH nvarchar(2000)
>
> SET @search = '"touchpoints"'
>
> SELECT TOP 250 [KEY], SUM(Rank) AS Rank, content_live.title
>
> FROM
>
> (
>
> SELECT Rank, [KEY] from CONTAINSTABLE(content_live,(title), @search)
>
> ) AS ftt
>
> INNER JOIN content_live ON content_live.id = [KEY]
>
> GROUP BY
>
> [KEY], content_live.title
>
> ORDER BY Rank DESC
>
> ---------------------------------------------------------------------------------------------
>
> This returns me the following example results:
>
> ---------------------------------------------------------------------------------------------
>
> KEY RANK TITLE
> 3304 192 TouchPoints Site: Using TouchPoints
> 3324 96 TouchPoints Site: Contact Us
> 3267 64 TouchPoints Site: TouchPoints Home
> 2137 48 Vision vs. practice seminar - agencies, media owners and
> clients to discuss their views on TouchPoints
> 3273 32 TouchPoints Site: How we do it
>
> ---------------------------------------------------------------------------------------------
>
>
> My problem is that the 3rd result with a Rank of 64 should have a rank of
> 192 based upon the occurence of the word "TouchPoints".
>
> If I rebuild the index then I get the correct results back, but with the
> knowledge that it'll start going wrong again.
>
> The table in question is set to Track Changes : Automatic.
>
> Title field is VARCHAR(1000).
>
> Version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23
> 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard
> Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
>
> Any suggestions of what might be causing this problem?
>
> Are there some settings that I need to change to ensure that the Rankings
> get updated correctly?
>
> Do I need to go through the crawl logs to see if there is a problem that
> stops population (even though I'm sure that the record in question hasn't
> been changed).
>
> Anyone else seen this issue?
>
> Many thanks,
>
> Larry.
>
>
date: Tue, 15 Jul 2008 05:54:41 -0400
author: Hilary Cotter
Re: Incorrect RANK results returned
Thanks for the reply Hilary,
The "occasionally get results like this" seems very woolly. Has MS got any
guidelines around this area to give an indication of how often I should be
reorganizing?
Are there any alternatives to Automatic Change Tracking? There will only be
100 or so changes in a day, but the user would want to see the results
reasonably quickly.
We're talking 10,000s of records rather than millions.
Thanks again,
Larry.
"Hilary Cotter" wrote in message
news:0CA6B758-B820-4834-A1CF-C8BC0D5ABB76@microsoft.com...
> You may need to reorganize your catalog/index from time to time. You will
> occasionally get results like this if you are using change tracking.
>
>
> "Larry Neylon" wrote in message
> news:%23zSFkgl5IHA.4776@TK2MSFTNGP05.phx.gbl...
>> Morning all,
>>
>> I'm having problems with my FTS rankings on our SQL2005 database and I'm
>> a bit stumped as where to start looking at what the the problem might be.
>>
>> The simplified SQL is as follows:
>> ---------------------------------------------------------------------------------------------
>>
>> DECLARE @SEARCH nvarchar(2000)
>>
>> SET @search = '"touchpoints"'
>>
>> SELECT TOP 250 [KEY], SUM(Rank) AS Rank, content_live.title
>>
>> FROM
>>
>> (
>>
>> SELECT Rank, [KEY] from CONTAINSTABLE(content_live,(title), @search)
>>
>> ) AS ftt
>>
>> INNER JOIN content_live ON content_live.id = [KEY]
>>
>> GROUP BY
>>
>> [KEY], content_live.title
>>
>> ORDER BY Rank DESC
>>
>> ---------------------------------------------------------------------------------------------
>>
>> This returns me the following example results:
>>
>> ---------------------------------------------------------------------------------------------
>>
>> KEY RANK TITLE
>> 3304 192 TouchPoints Site: Using TouchPoints
>> 3324 96 TouchPoints Site: Contact Us
>> 3267 64 TouchPoints Site: TouchPoints Home
>> 2137 48 Vision vs. practice seminar - agencies, media owners and
>> clients to discuss their views on TouchPoints
>> 3273 32 TouchPoints Site: How we do it
>>
>> ---------------------------------------------------------------------------------------------
>>
>>
>> My problem is that the 3rd result with a Rank of 64 should have a rank of
>> 192 based upon the occurence of the word "TouchPoints".
>>
>> If I rebuild the index then I get the correct results back, but with the
>> knowledge that it'll start going wrong again.
>>
>> The table in question is set to Track Changes : Automatic.
>>
>> Title field is VARCHAR(1000).
>>
>> Version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23
>> 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard
>> Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
>>
>> Any suggestions of what might be causing this problem?
>>
>> Are there some settings that I need to change to ensure that the Rankings
>> get updated correctly?
>>
>> Do I need to go through the crawl logs to see if there is a problem that
>> stops population (even though I'm sure that the record in question hasn't
>> been changed).
>>
>> Anyone else seen this issue?
>>
>> Many thanks,
>>
>> Larry.
>>
>>
>
date: Tue, 15 Jul 2008 11:20:18 +0100
author: Larry Neylon
Re: Incorrect RANK results returned
run full or incremental populations then:)
"Larry Neylon" wrote in message
news:uLHipRm5IHA.3952@TK2MSFTNGP06.phx.gbl...
> Thanks for the reply Hilary,
>
> The "occasionally get results like this" seems very woolly. Has MS got
> any guidelines around this area to give an indication of how often I
> should be reorganizing?
>
> Are there any alternatives to Automatic Change Tracking? There will only
> be 100 or so changes in a day, but the user would want to see the results
> reasonably quickly.
>
> We're talking 10,000s of records rather than millions.
>
> Thanks again,
> Larry.
>
>
> "Hilary Cotter" wrote in message
> news:0CA6B758-B820-4834-A1CF-C8BC0D5ABB76@microsoft.com...
>> You may need to reorganize your catalog/index from time to time. You will
>> occasionally get results like this if you are using change tracking.
>>
>>
>> "Larry Neylon" wrote in message
>> news:%23zSFkgl5IHA.4776@TK2MSFTNGP05.phx.gbl...
>>> Morning all,
>>>
>>> I'm having problems with my FTS rankings on our SQL2005 database and I'm
>>> a bit stumped as where to start looking at what the the problem might
>>> be.
>>>
>>> The simplified SQL is as follows:
>>> ---------------------------------------------------------------------------------------------
>>>
>>> DECLARE @SEARCH nvarchar(2000)
>>>
>>> SET @search = '"touchpoints"'
>>>
>>> SELECT TOP 250 [KEY], SUM(Rank) AS Rank, content_live.title
>>>
>>> FROM
>>>
>>> (
>>>
>>> SELECT Rank, [KEY] from CONTAINSTABLE(content_live,(title), @search)
>>>
>>> ) AS ftt
>>>
>>> INNER JOIN content_live ON content_live.id = [KEY]
>>>
>>> GROUP BY
>>>
>>> [KEY], content_live.title
>>>
>>> ORDER BY Rank DESC
>>>
>>> ---------------------------------------------------------------------------------------------
>>>
>>> This returns me the following example results:
>>>
>>> ---------------------------------------------------------------------------------------------
>>>
>>> KEY RANK TITLE
>>> 3304 192 TouchPoints Site: Using TouchPoints
>>> 3324 96 TouchPoints Site: Contact Us
>>> 3267 64 TouchPoints Site: TouchPoints Home
>>> 2137 48 Vision vs. practice seminar - agencies, media owners and
>>> clients to discuss their views on TouchPoints
>>> 3273 32 TouchPoints Site: How we do it
>>>
>>> ---------------------------------------------------------------------------------------------
>>>
>>>
>>> My problem is that the 3rd result with a Rank of 64 should have a rank
>>> of 192 based upon the occurence of the word "TouchPoints".
>>>
>>> If I rebuild the index then I get the correct results back, but with the
>>> knowledge that it'll start going wrong again.
>>>
>>> The table in question is set to Track Changes : Automatic.
>>>
>>> Title field is VARCHAR(1000).
>>>
>>> Version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23
>>> 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard
>>> Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
>>>
>>> Any suggestions of what might be causing this problem?
>>>
>>> Are there some settings that I need to change to ensure that the
>>> Rankings get updated correctly?
>>>
>>> Do I need to go through the crawl logs to see if there is a problem that
>>> stops population (even though I'm sure that the record in question
>>> hasn't been changed).
>>>
>>> Anyone else seen this issue?
>>>
>>> Many thanks,
>>>
>>> Larry.
>>>
>>>
>>
>
>
date: Tue, 15 Jul 2008 11:04:58 -0400
author: Hilary Cotter
|
|
