Re: WEIGHT confusion (SQL 2k, running on Win2k3)

From: John Kane (jt-kane_at_comcast.net)
Date: 10/08/04 

Date: Thu, 7 Oct 2004 22:38:59 -0700

Hi Duncan,
As for your first question, yes it is possible, but I need to know more
about your table Bars. Could you provide a row count and the output of
sp_help Bars ? As for your second question: The row for "Gallery Rendezvous"
does contain "Soho" in both columns VenueName and Addr3, but what is the
length of the text for both of these columns? Specifically, what is the word
count of this row, less noise words from noise.eng (UK_English noise word
file)? As for "Soho (The)", this too as Soho in both columns, and what is
the length (word count - noise words) in this row?

Thanks,
John

"Duncan Welch" <dunc@ntpcl.f9.co.uk> wrote in message
news:u1gq6E5qEHA.3900@TK2MSFTNGP10.phx.gbl...
> Hi John (or indeed anyone who can answer my two questions!),
>
> After much playing, I'd removed all the weighting and partial matching
from
> my FTS SQL statements, as it just wasn't producing the results I wanted.
> I've now got no options but to use it (new requirement) and it's gone back
> to driving me mad again.
>
> I've converted my SQL statement as per your recommendation, but I'm
getting
> very slow (you did warn me of this) and very strange results. My new
> [cut-down] query looks like:
>
> SELECT DISTINCT Bars.RefNo, Bars.Venue, Bars.Addr3, ISNULL(A.Rank, 0),
> ISNULL(B.Rank, 0)
> FROM Bars,
> CONTAINSTABLE(Bars, VenueName, 'Soho') as A,
> CONTAINSTABLE(Bars, Addr3, 'Soho') as B
> WHERE
> A.[KEY] = Bars.RefNo OR
> B.[KEY] = Bars.RefNo
>
> First question - is there a more efficient way of doing this type of
query?
>
> Second question - do you know why the results are coming up like:
>
> RefNo VenueName Addr3 A.Rank B.Rank
> 2112 Gallery Rendezvous Soho 320 96
> 2437 Pillars of Hercules (The) Soho 160 96
> 252 Abigail's Party Soho 160 96
> 471 Sugar Club (The) Soho 160 96
> 2718 Soho (The) Soho 160 96
>
> As the search was for Soho, you'll notice that they all have a B.Rank of
> 96 - cool. However, why should the first item (Gallery Rendezvous) have an
> A.Rank of 320 and the last item (The Soho) have an A.Rank of 160 when the
> first doesn't contain the word "Soho" at all, and the last does?
>
> Thanks in advance,
>
> Duncan
>
>