|
|
You're in the
sql server full text search
group:Using a different column to rank ft search results
sql server full text search:
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 -- 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 [quoted text, click to view] "DC" <dc@upsize.de> wrote in message news:1156155470.860744.200660@74g2000cwt.googlegroups.com... > 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 >
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
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. [quoted text, click to view] Hilary Cotter wrote:
> 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 > > > -- > 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 > > > > "DC" <dc@upsize.de> wrote in message > news:1156155470.860744.200660@74g2000cwt.googlegroups.com... > > 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 > >
No, you can sort by anything, sort by docscore if you want. I just assumed
you would want rank:) -- 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 [quoted text, click to view] "DC" <dc@upsize.de> wrote in message news:1156162019.219248.58700@b28g2000cwb.googlegroups.com... > 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. > > Hilary Cotter wrote: >> 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 >> >> >> -- >> 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 >> >> >> >> "DC" <dc@upsize.de> wrote in message >> news:1156155470.860744.200660@74g2000cwt.googlegroups.com... >> > 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 >> > >
DC wrote on 21 Aug 2006 03:17:50 -0700:
[quoted text, click to view] > 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 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
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. [quoted text, click to view] Daniel Crichton wrote:
> DC wrote on 21 Aug 2006 03:17:50 -0700: > > > 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 > > > 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
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: [quoted text, click to view] > 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. > > Daniel Crichton wrote: >> DC wrote on 21 Aug 2006 03:17:50 -0700: >> >>> 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 >> >> 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
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! [quoted text, click to view] Daniel Crichton wrote:
> 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 > > > > 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. > > > > Daniel Crichton wrote: > >> DC wrote on 21 Aug 2006 03:17:50 -0700: > >> > >>> 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 > >> > >> 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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |