Hello,
I'm having a problem that I'm hoping one of the Sql Server guru's on this forum can help me with. This applies to SQL Server 2005.
I'm working on an appliction that used the FTE index ranking feature. We're having a problem that all of the rank values start coming back as ZERO. If we rebuild the index for the effected table, the rank values start working, but eventually it stops working again. Recently, the rank values have been reverting to zero within a minute of the rebuild.
For reference, below is the sql code that we are using to get our search results.
select
row_number() over (order by RANK DESC) as rowNumber,
[KEY],
rank
from
freetexttable(searchIndex, *, 'outlook')
Here is the result that we get once the rankings stop working (sorry for the formatting, couldn't get it to paste good into the editor). All the rankings are zero:
rowNumber KEY rank
-- -- --
1 7 0
2 11 0
3 12 0
4 13 0
5 14 0
6 19 0
7 20 0
8 21 0
9 22 0
10 24 0
11 25 0
12 26 0
13 27 0
14 29 0
15 30 0
16 31 0
17 32 0
18 33 0
19 34 0
20 35 0
21 36 0
22 37 0
23 38 0
24 39 0
25 40 0
26 41 0
27 42 0
28 43 0
29 44 0
30 45 0
31 46 0
32 47 0
33 48 0
34 49 0
35 50 0
36 51 0
37 52 0
38 53 0
39 56 0
40 57 0
41 59 0
42 60 0
43 62 0
44 64 0
45 66 0
46 67 0
47 70 0
48 73 0
49 75 0
50 77 0
51 78 0
52 81 0
53 84 0
54 85 0
55 86 0
56 87 0
57 88 0
58 89 0
59 92 0
60 93 0
61 94 0
62 95 0
(62 row(s) affected)
Here is the result that we get immediately after rebuilding the FTE index. We get rankings for a brief time after the rebuild:
rowNumber KEY rank
-- -- --
1 25 885
2 89 878
3 12 866
4 57 860
5 66 860
6 95 849
7 44 849
8 92 843
9 88 843
10 85 818
11 33 818
12 26 818
13 94 808
14 84 805
15 52 805
16 56 805
17 14 805
18 41 805
19 46 805
20 47 805
21 51 782
22 29 778
23 31 763
24 48 763
25 50 763
26 42 763
27 39 763
28 35 763
29 81 763
30 73 729
31 20 729
32 7 729
33 22 727
34 11 692
35 40 692
36 43 692
37 38 692
38 49 692
39 53 692
40 59 692
41 87 692
42 36 677
43 37 673
44 34 673
45 45 673
46 13 673
47 86 673
48 93 673
49 60 642
50 62 617
51 21 617
52 30 583
53 64 583
54 75 529
55 70 529
56 32 529
57 24 529
58 27 529
59 19 529
60 77 516
61 78 473
62 67 473
(62 row(s) affected)
Any help would be greatly appreciated.
Thanks.
Mike
Did you turn on pre computed rank? Does this repro if you omit the row_number() column?
Also, can you do a master merge to see if the problem repros?
|||Hi Feng,
Thanks so much for replying. Sorry for the delayed response.
I did turn on precompute rank after reading your post but it didn't seem to make any difference. I had already established that omitting row_number() didn't help. One thing that is interesting is that the term 'outlook' seems to be what causes no ranks to be returned. When I do searches on other topics such as email, imap, or even 'outlook imap', I seem to get rankings. For me, this means that the problem is not a critical one, although I do wonder why its happening. It's almost as if SQL Server considers 'outlook' to be a noise word.
Any other advice would be gratefully received.
Thanks again.
Mike
|||Is the index the only index in the catalog?
I cannot think of any reason why 'outlook' get the special treatment. We need a repro and data to dive deeper into the problem.
|||Feng,
The index is the only one in the full text catalog. I do have a second full text catalog indexing another table.
Can you give me more detail on what you mean by "repro and data"?
Thanks.
Mike
|||Basically, I mean a small data set and a sql script that can reproduce the bug, so that we can debug it to see what is happening. If you can provide such a repro, please make it "generic", that is, strip out all the confidential/sensitive data -- otherwise I think you need to go through the formality of MS customer support.
No comments:
Post a Comment