none
problem with rank on freetexttable

    Question

  • 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

    Saturday, September 09, 2006 4:28 PM

All replies

  • 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?

    Monday, September 11, 2006 6:54 PM
  • 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

    Saturday, September 23, 2006 8:42 PM
  • 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.

    Monday, September 25, 2006 5:21 PM
  • 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

     

    Tuesday, September 26, 2006 11:07 AM
  • 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.
    Wednesday, September 27, 2006 4:53 PM
  • I've recently posted re: this exact same behavior and am wondering how it ever got sorted out for you?
    Wednesday, January 28, 2009 11:23 PM
  • Hi, I have same behavior as well, look here: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/433d0baf-95b1-4329-aa32-9324a517b258

    I tried set pre-compute rank, create again full text catalog, etc. Only rebuild catalog helps for few moments.

    Strange is, that on same server we have 2 another databases with own full text catalogs and there are no problems. I hope somebody here know how to fix it.

    Thursday, January 29, 2009 3:02 AM
  • Now I check folders, where are catalogs saved and looks like I have one file missing in my trouble database catalog: ftcatstate

    Is it related?

    Other working catalogs have in root folder ftcatstate and SQL.HDR files, one have additional CatalogStatus.dat and CatalogStatus.idx.

    I tried again to drop/recreate this catalog, but it didn't create this file. Anybody have any idea? Thanks for any help or idea what to try.

     

    Thursday, January 29, 2009 3:29 AM
  • Installed SQL2005 SP3, still same.
    Thursday, January 29, 2009 4:42 AM
  • Is it possible to post your tables schema here, and possibly some sample data?
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, January 29, 2009 1:01 PM
  • Hi Everybody,

    looks like I made it work, but I don't know why. What I did:

    1) I created new table for testing (script is generated)

    CREATE TABLE [dbo].[SearchTest](  
        [Id] [int] IDENTITY(1,1) NOT NULL,  
        [SearchText] [ntext] NOT NULL,  
     CONSTRAINT [PK_SearchTest] PRIMARY KEY CLUSTERED   
    (  
        [Id] ASC 
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]  
    ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
     
    GO 

    I insert in SearchText column values from previous table column, which I have trouble with rank 0. Then I set up Full Text Index on this table. I tried again my query:

    SELECT * FROM 
    FREETEXTTABLE(SearchTest, SearchText, ' test ')  
    ORDER BY RANK DESC 

    It works fine, so I thought something must be wrong with my original table, but what wonder, original table now works as well !!!!

    bvaljalo: try to create dummy table in your database and index it in your catalog, maybe it will begin works for u too.

    My solution is create dummy table in your database and index it in your catalog. Sounds strange, but I found no other way to fix it.

    • Proposed as answer by Pavel Valenta Monday, February 02, 2009 1:45 AM
    Friday, January 30, 2009 2:03 AM
  • it looks like this is an issue with the # of rows in your table. How many rows in this table? I don't see it on large tables.

    Also it does not occur with ContainsTable searches.

    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Saturday, January 31, 2009 6:26 PM
  • Thanks for your replies. Yes, my database is new and that table have only 30 rows now.

    So maybe this can be reason, why SQL engine didn't store ranking. After I created copy of this table and have 60 rows indexed, then it begin works.

    Other databases have more than 1000 rows in tables which are indexed, so there is no trouble with lost rank.
    Monday, February 02, 2009 1:38 AM