sql server full text search: Newbie rank order confusion -- sql server full text search

Re: Newbie rank order confusion

dotnw NO[at]SPAM hotmail.com
8/31/2004 10:29:17 AM

Hi John,

[quoted text, click to view]

Sure...

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

16

[quoted text, click to view]
Breaker"

I believe it was slightly different - UK_English

[quoted text, click to view]
digits) from the noise word file: noise.enu?

I have now done this - thanks.

[quoted text, click to view]

I have now run this on my full-text tables.

[quoted text, click to view]
Store_BasicSearchableItems this could also explain the low RANK
values.

I see! Actually, I only have 16 rows as I am just learning about
full-text searching. My final table will only have around 500 rows,
so this information worries me a little.

[quoted text, click to view]

This seems very interesting, but unfortunately I can't get their SQL
example to work. Query analyzer doesn't like this line -

WHERE T.key=CT.key AND T.a > 5

If I could use their example code, I code modify my stored procedure
and get better results.

Using my tweaked original SQL:

select *
from Store_BasicSearchableItems,
containstable(Store_BasicSearchableItems, ItemName, '"Apple 5"', 10)
tblSearchResults
where [key] = Store_BasicSearchableItems.ItemId
order by rank desc

for the search of "Apple 5", I get these results in this order:

Apple, Apple 10, Apple 5, Apple 4, etc.

(Again, all results have the same rank value, this time 32)

TIA,
-dnw.

[quoted text, click to view]

Re: Newbie rank order confusion

John Kane
8/31/2004 12:43:10 PM

You're welcome, DNW,
You're using SQL Sever 2000 SP3 Developer Edition on Win2K SP4 using
UK_English, and since you're using UK_English, you should edit the noise.eng
(and NOT the US_English noise word file noise.enu) and then re-run a Full
Population.

16 rows is certainly not large enough of a table to get variations in the
query specific RANK values, but you might be ok with 500 rows, but even so,
I'd recommend that you artificially scale up to 500 rows and re-test your
FTS queries with a larger table...

Below is a short & simple explanation of how RANK is calculated:
1. The number of times a word appears in a document.
2. The closer the searched for words are to each other, the higher the rank,
until the point that they are adjacent becoming a phrase and raising the
rank even higher.
3. The ranking mechanism is weighted so that the more highly inflected the
word is from the version asked for originally, the lower its rank in the
result set. For example, "swim" would be closer to "swims" and further from
"swimmer" because "swim" and "swimmer" are less related grammatically. In
other words, the plural noun form is more related grammatically than the
past-tense verb form of the same word. When resolving queries, the
linguistic engine and ranking algorithm take these linguistic features into
account.

As your CONTAINSTABLE query table is very small (even with 500 rows), I'd
recommend that you remove the Top_N_Rank parameter, i.e., the value 10 in
your query as this is mainly implemented for performance reasons against
very large FT-enable tables.

Additionally, you're searching on "Apple 5" (within double quotes) so this
qualifies as a "phrase search", but the single digit 5 is ignored as it is
still in the noise.eng (UK_English noise word file), so Apple without any
single digits will list first, even though all rows returned have the same
query specific rank value of 32.

I've recommend that you do the following: 1) remove all single digits from
noise.eng, 2) run a Full Population, 3) remove the Top_N_Rank value of 10
from your containstable query, and if possible 4) re-test with a larger
table.

Regards,
John

[quoted text, click to view]

Re: Newbie rank order confusion

Dot Net Work
8/31/2004 2:32:20 PM

Hi John,

I have completed all of your suggestions, and I have some interesting
results.

As a summary, my table now looks like this:

Apple 1
Apple 2
etc, up to
Apple 1000
Apple <-- I added this record at the end.

I ran 4 variations of my stored procedure.

Variation 1.

select *
from Store_BasicSearchableItems,
containstable(Store_BasicSearchableItems, ItemName, 'Apple')
tblSearchResults
where [key] = Store_BasicSearchableItems.ItemId
order by rank desc

Produced these results inside Query Analyzer:

Apple 1
Apple 2
etc, up to
Apple 1000
Apple

Apple appears at the end, and all rows have a rank of 96.

Variation 2:

select top 10 *
<the remainder of the sp is the same>

Variation 2 results:

Apple 904
Apple 95
Apple 641
etc -- 10 rows listed

There is no "Apple" record! All rows are ranked 96.

Variation 3:

select *
from Store_BasicSearchableItems,
containstable(Store_BasicSearchableItems, ItemName, @sSearchText, 10)
tblSearchResults
where [key] = Store_BasicSearchableItems.ItemId
order by rank desc

(I removed the top 10, but added in 10 for the top_n_rank)

Variation 3 results:

Similar to variation 2 results. Again no "Apple" record. Rank values
are all 96.

Variation 4:

select top 10 *
<the remainder is the same as the sp for variation 3 right above>

Variation 4 results:

Similar to variation 3 results. No "Apple" record, and rank values all
96.

In all 4 tests, a search for "Apple" only yields "Apple" in 1 test, and
that appears last in the results list.

Does this behaviour strike you as being odd? I must be doing something
wrong?!

TIA,
-dnw.

*** Sent via Developersdex http://www.developersdex.com ***

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server full text search group.