Difference in ranking results from a full index rebuild verses an incremental rebuild 
Author Message
 Difference in ranking results from a full index rebuild verses an incremental rebuild

I have a problem that I find interesting and maybe someone else will too.
I'm seeing the following behavior in my full text searches and it seems
rather bizarre. I have a full text index on a varchar column in a table with
about 18,000 rows. I have a timestamp column so I can do incremental
rebuilds.

On performing the following steps:

1. Perform a full index rebuild
2. Perform a freetexttable query and note the ranking of the results ( Call
this results 1 )

3. Insert a few hundred rows of new data
4. Perform an incremental index rebuild
5. Perform a freetexttable query and note the ranking of the results ( Call
this results 2 )

6. Perform a full index rebuild
7. Perform a freetexttable query and note the ranking of the results ( Call
this results 3 )

Now comparing the 3 results I see that the new rows I added are ranked
significantly higher in the result set for results 2, after the incremental
rebuild, than they are in results 3, after the full rebuild. I have found
that a row will go from being ranked 5th from the top after the incremental
index rebuild to 30th after a full index rebuild. This variability in the
results is not acceptable to my application.

Currently I have configured the background index update functionality and
then once a night I do a full index rebuild. I would rather not have to do
the full rebuild but it seems to be to only way I can get consistent
ranking. Has anyone else had this problem and if so is there any way to get
the incremental index rebuild to return rankings that are consistent with a
full index rebuild?

Thanks,

Marvin Hamon



Sat, 22 Nov 2003 06:22:32 GMT
 Difference in ranking results from a full index rebuild verses an incremental rebuild

Hi Marvin,

I tested this on a much smaller data set and did not find any problems,
however that's not to say that your data is causing some issues. Is it
necessary to perform the incremental - prehaps change tracking would best
suit - and you could see if this fixes the ranking problem.

Another thing is that the index may need to complete a master merge before
it can produce satisfactory results.

Do you have a repro script you can send me so I can test - if I repro I will
send onto Microsoft.

Best Regards

Trevor Dwyer - SQL Server MVP


Quote:
> I have a problem that I find interesting and maybe someone else will too.
> I'm seeing the following behavior in my full text searches and it seems
> rather bizarre. I have a full text index on a varchar column in a table
with
> about 18,000 rows. I have a timestamp column so I can do incremental
> rebuilds.

> On performing the following steps:

> 1. Perform a full index rebuild
> 2. Perform a freetexttable query and note the ranking of the results (
Call
> this results 1 )

> 3. Insert a few hundred rows of new data
> 4. Perform an incremental index rebuild
> 5. Perform a freetexttable query and note the ranking of the results (
Call
> this results 2 )

> 6. Perform a full index rebuild
> 7. Perform a freetexttable query and note the ranking of the results (
Call
> this results 3 )

> Now comparing the 3 results I see that the new rows I added are ranked
> significantly higher in the result set for results 2, after the
incremental
> rebuild, than they are in results 3, after the full rebuild. I have found
> that a row will go from being ranked 5th from the top after the
incremental
> index rebuild to 30th after a full index rebuild. This variability in the
> results is not acceptable to my application.

> Currently I have configured the background index update functionality and
> then once a night I do a full index rebuild. I would rather not have to do
> the full rebuild but it seems to be to only way I can get consistent
> ranking. Has anyone else had this problem and if so is there any way to
get
> the incremental index rebuild to return rankings that are consistent with
a
> full index rebuild?

> Thanks,

> Marvin Hamon



Sat, 22 Nov 2003 08:34:15 GMT
 Difference in ranking results from a full index rebuild verses an incremental rebuild
Hi Trevor,

Thanks for the response. I could not find any reference to a master merge
operation in the documentation so I am not sure how I can tell if this is
happening or not. I do need the incremental indexing since I have new rows
inserted throughout the day and need the index to be up to date as close to
real time as possible. I am currently using change tracking and background
incremental indexing with a full indexing every night to fix the problem.

My dataset is around 18,000 rows so I will try to reproduce the problem in a
smaller dataset that I could send you.

Marvin Hamon


Quote:
> Hi Marvin,

> I tested this on a much smaller data set and did not find any problems,
> however that's not to say that your data is causing some issues. Is it
> necessary to perform the incremental - prehaps change tracking would best
> suit - and you could see if this fixes the ranking problem.

> Another thing is that the index may need to complete a master merge before
> it can produce satisfactory results.

> Do you have a repro script you can send me so I can test - if I repro I
will
> send onto Microsoft.

> Best Regards

> Trevor Dwyer - SQL Server MVP



> > I have a problem that I find interesting and maybe someone else will
too.
> > I'm seeing the following behavior in my full text searches and it seems
> > rather bizarre. I have a full text index on a varchar column in a table
> with
> > about 18,000 rows. I have a timestamp column so I can do incremental
> > rebuilds.

> > On performing the following steps:

> > 1. Perform a full index rebuild
> > 2. Perform a freetexttable query and note the ranking of the results (
> Call
> > this results 1 )

> > 3. Insert a few hundred rows of new data
> > 4. Perform an incremental index rebuild
> > 5. Perform a freetexttable query and note the ranking of the results (
> Call
> > this results 2 )

> > 6. Perform a full index rebuild
> > 7. Perform a freetexttable query and note the ranking of the results (
> Call
> > this results 3 )

> > Now comparing the 3 results I see that the new rows I added are ranked
> > significantly higher in the result set for results 2, after the
> incremental
> > rebuild, than they are in results 3, after the full rebuild. I have
found
> > that a row will go from being ranked 5th from the top after the
> incremental
> > index rebuild to 30th after a full index rebuild. This variability in
the
> > results is not acceptable to my application.

> > Currently I have configured the background index update functionality
and
> > then once a night I do a full index rebuild. I would rather not have to
do
> > the full rebuild but it seems to be to only way I can get consistent
> > ranking. Has anyone else had this problem and if so is there any way to
> get
> > the incremental index rebuild to return rankings that are consistent
with
> a
> > full index rebuild?

> > Thanks,

> > Marvin Hamon



Tue, 25 Nov 2003 00:47:17 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Difference between a full scan, and a fast full scan for an INDEX

2. full text search and ranking results

3. Full text Indexing Ranking Problem

4. full text indexing query - RANK

5. Full Text Incremental Index VERY SLOW

6. Incremental Builds of full text indexes take too long

7. Performance of Incremental Index on Full Text Catalogue

8. Incremental full text index?

9. incremental full-text indexing keeps running

10. Full Text Index Incremental Populatin is too slow

11. Full Text Indexing incremental not working

12. Incremental population: Full-text indexes


 
Powered by phpBB® Forum Software