Confused about CONTAINS and CONTAINSTABLE

I have not been using FTS for long, so maybe I am missing an obvious
this here... I have a table with titles of products in them. Its about
a million rows. If I do:
set @.arg='("pda") and ("case")'
select stockcode, rank,
from containstable(product,*,@.arg, 1000)
order by rank desc
and
select stockcode from exp_product where contains(*,@.arg)
I get 20 results for the first and 25 for the second. If I do a
regular like query that returns 25 results. Why does the
containstable not return all the results?
The products titles are less than 100 characters and I did a full FTS
rebuild before the test (and it had finished).
Rob Chafer
Silverfrost
Hi Rob,
Could be something to do with three character searches that are
automatically excluded from the search. If you can change pda to
something longer and test, it might show different results.
I'm just trying to work around this one myself.
Ryan
|||Sorry, disregard that, I had an issue with quoting.
I notice that you are not using wildcards, are you after exact matches
only? Could you try;
set @.arg='("*pda*") and ("*case*")'
Ryan
|||Ryan
Thanks for your reply. It was actually only an example and the words
(specifically) were 'acer' and 'expansys' -- longer than 3 letters. I
did not use wildcards -- the words were in the titles and as single
words so did not use wildcards.
"Ryan" <ryano@.lightsdown.com> wrote:

> Sorry, disregard that, I had an issue with quoting.
> I notice that you are not using wildcards, are you after exact matches
> only? Could you try;
> set @.arg='("*pda*") and ("*case*")'
> Ryan
Rob Chafer
Silverfrost
|||This is abnormal. How many results does this return?
select stockcode, rank, from containstable(product,*,@.arg)
I take it you are using SQL 2000? Also what sp.
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
"Robert Chafer" <noreply@.silverfrost.com> wrote in message
news:uefth1tj1nel2a2e45j890aokpgp6b7bk6@.4ax.com...
> I have not been using FTS for long, so maybe I am missing an obvious
> this here... I have a table with titles of products in them. Its about
> a million rows. If I do:
> set @.arg='("pda") and ("case")'
> select stockcode, rank,
> from containstable(product,*,@.arg, 1000)
> order by rank desc
> and
> select stockcode from exp_product where contains(*,@.arg)
> I get 20 results for the first and 25 for the second. If I do a
> regular like query that returns 25 results. Why does the
> containstable not return all the results?
> The products titles are less than 100 characters and I did a full FTS
> rebuild before the test (and it had finished).
> --
> Rob Chafer
> Silverfrost
|||Robert,
I can explain this... Basically, you're using two very different methods
(CONTAINSTABLE vs. CONTAINS) with the former results limited to the
Top_N_by_RANK where N is 1000. Specifically, you should review KB article
240833 "FIX: Full-Text Search Performance Improved via Support for TOP" at
http://support.microsoft.com//defaul...b;EN-US;240833 for more
details.
Basically, you're not comparing apples-to-apples... Try the below modified
code (removed 1000)
set @.arg='("pda") and ("case")'
select stockcode, rank,from containstable(product,*,@.arg) order by rank desc
-- vs.
select stockcode from exp_product where contains(*,@.arg)
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Robert Chafer" <noreply@.silverfrost.com> wrote in message
news:uefth1tj1nel2a2e45j890aokpgp6b7bk6@.4ax.com...
>I have not been using FTS for long, so maybe I am missing an obvious
> this here... I have a table with titles of products in them. Its about
> a million rows. If I do:
> set @.arg='("pda") and ("case")'
> select stockcode, rank,
> from containstable(product,*,@.arg, 1000)
> order by rank desc
> and
> select stockcode from exp_product where contains(*,@.arg)
> I get 20 results for the first and 25 for the second. If I do a
> regular like query that returns 25 results. Why does the
> containstable not return all the results?
> The products titles are less than 100 characters and I did a full FTS
> rebuild before the test (and it had finished).
> --
> Rob Chafer
> Silverfrost
|||Hilary
Thank you for your help...from your example I realised what the issue
was. Although the recordset returned only has 20 and 25 rows, there
was an extra condition (to select language). Although the condition
was identical in both examples, the ,1000 in the containstable meant
that a few of the matches never made it through.
I live and learn
Thanks again.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote:

> This is abnormal. How many results does this return?
> select stockcode, rank, from containstable(product,*,@.arg)
> I take it you are using SQL 2000? Also what sp.
Rob Chafer
Silverfrost