Re: Fulltext query with custom rank

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Yuri,

if containstable(your_column,@your_value) returns 100.000 results,
then my_score_fn will have to do a lot of sorting.

This is exactly the problem that I am facing, where out of about one
million indexed rows easily 10 to 100 thousand results are being
returned by weak queries.

For your function my_score_fn it would probably not be of much value
to do what I am trying, since my_score_fn takes the search argument as
a parameter. So you cannot precalculate your desired ranking and store
it into an additional column. But my "Score" is totally independant of
the search argument. The RANK that sql server calculates is useless
for me. Since

containstable(Products, Name, '"Screw*"', 10)

gives the top 10 results by RANK, I only see the chance to manipulate
RANK generation somehow. But I apprehend that this is not possible at
all.

Regards
DC


On 16 Mai, 17:14, ynogin <yno...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
DC,
The syntax I use is more like:

Select top(@n_rows) *,my_score_fn(your_column,@your_value)
from your_table
where containstable(your_column,@your_value)
order by my_score_fn(your_column,@your_value) desc

You also can use freetexttable function instead.

Thanks,
Yuri



"DC" wrote:
Hi Yuri,

very interesting, were you able to manipulate the fulltext index
ranking, i.e. would

containstable(Products, Name, '"Screw*"', 10)

return the top 10 results as calculated by your algorithm?

Do you maybe have a link or a topic to look for about this kind of CLR
integration?

Regards
DC

On 15 Mai, 20:41, ynogin <yno...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
DC,
I've created my own CLR function and use it instead of FTS rank. It gives
much better result (by similarity)
I use Levenshtein Edit distance to calculate the score.

Thanks,

Yuri

"DC" wrote:
Hi,

I brought this up once ago, but I read something which might open a
new possibility. What I am trying to do is this:

select p.Name, p.Score, ft.Rank
from Products p
join (
select [key], rank from
containstable(Products, Name, '"Screw*"', 10)
) as ft
on ft.[key]= p.ProductId
order by p.Score desc

Let's say "Products" contains one million products and there are 20000
matching products containing "screw" as a part of their name. What I
want to get are the top 10 products matching the query, but the FT
rank should equal the Score rank. I don't want this:

containstable(Products, Name, '"Screw*"', 50000)

and then join the resulting 20000 rows with products and order the set
by Score, since that will be too expensive.

Best would be, if I could actually set the value that the ranking
algorithm is based on. The indexer would simply use Score as the
predominant factor for the ranking. So

containstable(Products, Name, '"Screw*"', 10)

would return the first 10 matches but sorted by Score.

From what I know this is not possible with FT in 2000 or 2005, but I
read that it is possible to use CLR integration to customize the
indexing process. However, I could not find anything in the docs about
that. Maybe one can only create indexes and stuff like that, but I am
still hoping that someone has a clue on how to possibly manipulate FT
rank in SQL Server 2005.

Kind regards
DC- Zitierten Text ausblenden -

- Zitierten Text anzeigen -- Zitierten Text ausblenden -

- Zitierten Text anzeigen -


.

Relevant Pages

  • Fulltext query with custom rank
    ... matching products containing "screw" as a part of their name. ... rank should equal the Score rank. ... if I could actually set the value that the ranking ... The indexer would simply use Score as the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Fulltext query with custom rank
    ... This way you will cut out most of the low "similarity" matches and your ... So you cannot precalculate your desired ranking and store ... The RANK that sql server calculates is useless ... The indexer would simply use Score as the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Fulltext query with custom rank
    ... will return the first 200 matches sorted by RANK, ... So you cannot precalculate your desired ranking and store ... want to get are the top 10 products matching the query, ... The indexer would simply use Score as the ...
    (microsoft.public.sqlserver.fulltext)
  • RE: How EXACTLY does Indexing Service determine rank
    ... the point that they are adjacent becoming a phrase and raising the rank ... The ranking mechanism is weighted so that the more highly inflected ... the linguistic engine and ranking algorithm ... Indexing Service is based on ranking formulas that are used everywhere from ...
    (microsoft.public.inetserver.indexserver)
  • Re: Increasing your PAGE RANK
    ... >> Participating in link schemes is a way to increase page rank, ... so the site 'spam ranking' will ... I have a "useful links" page on one site for people who want ... > to do a link exchange. ...
    (alt.internet.search-engines)