Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Full-Text Search / August 2006

Tip: Looking for answers? Try searching our database.

Using a different column to rank ft search results

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
DC - 21 Aug 2006 11:17 GMT
Hi,

I am currently using the third party product "SQL Turbo" on SQL Server
2000 to archive this:

I have a table:

id   DocText   DocScore
1    foo foo      2
2    bar bar     1
... (approx. 1 mio. entries)

Now with SQL Turbo I can do something like this:

select top 100 id from ExampleTable where contains(DocText, '"foo*")
order by DocScore desc

(the actual query looks different and relies on a prepared index for
DocScore).

I require this, since a fulltext search in my application easily
matches 250,000 documents and I need to efficiently just pull out the
first few hundreds with the highest scores.

While SQL Turbo works (and its index population speed is incredible) it
also has some flaws (for example I cannot get the automatical
population to work) and it looks as if the product is not supported
anymore.

I looked out for a similar functionality in SQL Server 2005 to no
avail. Did I overlook something or does somebody maybe know a
workaround? If possible, I would like to retire SQL Turbo while
switching to SQL Server 2005.

Regards
DC
Hilary Cotter - 21 Aug 2006 12:25 GMT
You would do this

select top 100 id from ExampleTable join on (select [key], rank from
containstable(exampletable,doctext,'"foo*")) as k
join k.[key]=ExampleTable.id
order by rank desc

or
select id from ExampleTable join on (select [key], rank from
containstable(exampletable,doctext,'"foo*",100)) as k
join k.[key]=ExampleTable.id
order by rank desc

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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

> Hi,
>
[quoted text clipped - 32 lines]
> Regards
> DC
DC - 21 Aug 2006 13:06 GMT
Thank you, but "rank" is what the indexing engine thinks the rank
should be, while in my application DocScore is actually a more
elaborate column that for example contains the information how often
the document has been viewed.

So is "rank" still the only column the SQL Server 2005 FT engine can
sort by (without diggin up all matches and sort through those)? This
was all SQL Server 2000 could do.

> You would do this
>
[quoted text clipped - 59 lines]
> > Regards
> > DC
Hilary Cotter - 21 Aug 2006 14:02 GMT
No, you can sort by anything, sort by docscore if you want. I just assumed
you would want rank:)

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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

> Thank you, but "rank" is what the indexing engine thinks the rank
> should be, while in my application DocScore is actually a more
[quoted text clipped - 68 lines]
>> > Regards
>> > DC
Daniel Crichton - 22 Aug 2006 12:32 GMT
DC wrote  on 21 Aug 2006 03:17:50 -0700:

> Hi,
>
[quoted text clipped - 32 lines]
> Regards
> DC

You can use exactly what you wrote to do your ordering:

select top 100 id from ExampleTable where contains(DocText, '"foo*")
order by DocScore desc

I use this form all the time to sort by columns that the FTS has no idea
about.

Dan
DC - 22 Aug 2006 17:00 GMT
Yes, something like

select top 10 Name, StandardCost
from Production.Product
where contains(Name, '"Mount*"')
order by ListPrice desc

does work, the problem is that the FTS will dig up all matching rows
first (and in my application there are usually a lot of hits) and then
SQL Server will sort there and return the top 10 rows.

With SQL Turbo my queries are a lot faster, since I can specify an
additional ordering index (which may also reside in a different table)
and sort by that index. The SQL Trubo fulltext engine will then only
return the top n rows that I have specified.

The containstable statement allows a similar functionality, like in
Hilary's example:

select top 10 Name, StandardCost from Production.Product
join (
    select [key], rank from
    containstable(Production.Product, Name, '"Mount*"', 1000)
) as k
on k.[key]= Production.Product.ProductID
order by ListPrice desc

The containstable statement will only return the first 1000 rows here,
but these will always be sorted by rank. I need this sorted be
ListPrice instead. If I do this:

select top 10 Name, StandardCost from Production.Product
join (
    select [key], rank from
    containstable(Production.Product, Name, '"Mount*"', 10)
) as k
on k.[key]= Production.Product.ProductID
order by ListPrice desc

I will not get the 10 matching rows with the highest ListPrice.

> DC wrote  on 21 Aug 2006 03:17:50 -0700:
>
[quoted text clipped - 44 lines]
>
> Dan
Daniel Crichton - 23 Aug 2006 09:22 GMT
As Hilary pointed out, MS FTS doesn't work like this. Are you sure SQL Turbo
isn't supported? Quest Software are still listing it on their current
products page, and KB articles have been added within at least the past
month to their site. Have you tried contacting them?  http://www.quest.com

Dan

DC wrote  on 22 Aug 2006 09:00:10 -0700:

> Yes, something like
>
[quoted text clipped - 85 lines]
>>
>> Dan
DC - 24 Aug 2006 14:46 GMT
Our admins told me the product was sold twice and that they would not
get an updated version (and the current version has a couple of flaws),
but I will check again. I will also check if FTS does maybe meet our
performance requirements in the 2005 version although it digs up all
the FT matches.

Thank you, Hilary and Daniel!

> As Hilary pointed out, MS FTS doesn't work like this. Are you sure SQL Turbo
> isn't supported? Quest Software are still listing it on their current
[quoted text clipped - 94 lines]
> >>
> >> Dan
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.