 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / November 2005SQL 2005 returns fewer records in containstable
Hi, We migrated our database to SQL Server 2005 RTM Enterprise (build 9.00.1399.06) and we have noticed that the full-text index is returning significantly fewer matches in a containstable function compared to that of SQL Server 2000. We use the containstable function with a top_n_by_rank of 400 most in most of our stroed procedures and so far we are seeing very few records being retuned in SQL 2005 searches. Instances where we were seeing 400 records being returned (maximum limit) in SQL Server 2000, we're only seeing 156 records being retuned from the full-text searches in SQL 2005. We are not so concerned about the ranking but the fact that fewer records are being returned means that we need to re-examine and possibly change the stored procedure logic to cater for the fewer records being retrieved. Based on these results can we assume that the matching algorithm has been changed in SQL 2005? The other interesting fact to note that is a contains function on the same search string returns more than 800 records from both SQL 2000 and 2005. In SQL 2000, this same search would yeild 400 records in a conatainstable function but the fact that SQL 2005 is only returning 156 records is very concerning. Is there an explanation for this behaviour? Feedback is greatly appreciated. Cheers, Priyanga Are you perhaps doing any trimming? This is where you limit your results set coming back from MSSearch of MSFTESQL and then trim by date, here is an example select * From authors join (select * from containstable(authors, *, 'ringer',1000)) as a on a.[key]=authors.rowguid where state='ca' Here we are limiting the results set to the first 1000 and them trimming the results set to only ringers who live in CA. Now what might be happening is that in SQL 2000 you that the first 1000 results coming back from MSSearch is ringers who live in CA, whereas in SQL 2005 the only the 500 of the first 1000 results returned by MSFTESQL are in CA, and hence you get a lower number of results. I would try to remove the trimming and limiting condition (the number of rows) and see if the results are identical.  SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > Hi, > [quoted text clipped - 24 lines] > Cheers, > Priyanga Hi Hilary, We are not trimming the resultset with a where condition. A simple test like this would yeild very different results between SQL 2000 and 2005. select * From authors join (select * from containstable(authors, *, 'ringer',1000)) as a on a.[key]=authors.rowguid The only different thing we are doing is specifying the full-text field as apposed to *. Intereseting thing to note is that in SQL 2005 if we use containstable(authors, firstname, 'ringer'), without a top-n-by-rank, then it would return over 800 records but as soon as we specify a top-n-by-rank of 400, the results drop to 145. This did not happen with SQL 2000. Testing in SQL Server 2000 indicates that a minimum of 400 records would always be returned when a containstable without top-n-by-rank return more than 400 records. Cheers, Priyanga > Are you perhaps doing any trimming? This is where you limit your results > set coming back from MSSearch of MSFTESQL and then trim by date, here is [quoted text clipped - 44 lines] >> Cheers, >> Priyanga This is strange. How many rows are there? SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > Hi Hilary, > [quoted text clipped - 65 lines] >>> Cheers, >>> Priyanga Hilary, There are 56316 records in the underlying table. In the full-text catalog properties, "Table Full-Text Item Count" has 56316 as well. Underlying table indexes have been rebuilt and statistics updated as well as the full-text index being full populated. Priyanga > This is strange. How many rows are there? >> Hi Hilary, [quoted text clipped - 67 lines] >>>> Cheers, >>>> Priyanga Hilary, We overcame this issue by BCPing out the data, creating a new table, BCPing data in and then rebuilding the full-text index. We have not been able to reproduce this issue in any of our environments. Cheers, Priyanga > Hilary, > [quoted text clipped - 77 lines] >>>>> Cheers, >>>>> Priyanga |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.