SQL Server Full-Text Search - SQL 2005 returns fewer records in containstable

Author

SQL 2005 returns fewer records in containstable

Priyanga Karunathilake

2005-11-21, 7:23 am

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

Hilary Cotter

2005-11-21, 9:23 am

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(author
s, *, '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.

--
Hilary 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

"Priyanga Karunathilake" <priyanga@internode.on.net> wrote in message
news:4381b7e6$1@dust
er.adelaide.on.net...
> 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
>

Priyanga Karunathilake

2005-11-21, 1:23 pm

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(author
s, *, '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(author
s, 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

"Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:%23lVdMpq7FHA.3224@TK2MSFTNGP09.phx.gbl...
> 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(author
s, *, '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.
>
> --
> Hilary 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
>
> "Priyanga Karunathilake" <priyanga@internode.on.net> wrote in message
> news:4381b7e6$1@dust
er.adelaide.on.net...
>
>

Hilary Cotter

2005-11-21, 1:23 pm

This is strange. How many rows are there?
--
Hilary 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

"Priyanga Karunathilake" <priyanga@internode.on.net> wrote in message
news:43821109$1@dust
er.adelaide.on.net...
> 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(author
s, *, '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(author
s, 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
>
>
>
> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
> news:%23lVdMpq7FHA.3224@TK2MSFTNGP09.phx.gbl...
>
>

Priyanga Karunathilake

2005-11-21, 8:24 pm

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

"Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:es7ak2s7FHA.1148@tk2msftngp13.phx.gbl...
> This is strange. How many rows are there?
> --
> Hilary 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
>
> "Priyanga Karunathilake" <priyanga@internode.on.net> wrote in message
> news:43821109$1@dust
er.adelaide.on.net...
>
>

Priyanga Karunathilake

2005-11-24, 7:23 am

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

"Priyanga Karunathilake" <priyanga@internode.on.net> wrote in message
news:438233a7$1@dust
er.adelaide.on.net...
> 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
>
>
> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
> news:es7ak2s7FHA.1148@tk2msftngp13.phx.gbl...
>
>

Sponsored Links