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 / December 2004

Tip: Looking for answers? Try searching our database.

2005 fti query performance issue

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Carl Henthorn - 09 Dec 2004 16:03 GMT
I have a 10 million row table with two columns(name, address) full text
indexed. I want to search the table for
the first 200 occurances of "Bob jones" in the name field. I have ran
another query to find out that there are actully 15,291 rows with "Bob Jones"
in the name field. I want just the first 200.
When I run the FT search, it takes 2 minutes for the query to finish. When I
run a simple query
 (select * from tst where name = 'Bob jones'),
the query comes back in under two seconds. It appears that the query is
searching the whole catalog before going to the data table. Is this expected?
How can I get the query to search the catalog and stop after the first 200
occurances are found?

Here is the query I am using:

Select name, address from tst
where contains(Name, '"Bob Jones"')

Table schema is as follows:
create table tst(
uid int identity(1,1),
Name varchar(255),
address varchar(255)
)
Hilary Cotter - 10 Dec 2004 01:54 GMT
use containstable. It allows you to limit your results set to the first 200
or so rows.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

>I have a 10 million row table with two columns(name, address) full text
> indexed. I want to search the table for
[quoted text clipped - 23 lines]
> address varchar(255)
> )
John Kane - 10 Dec 2004 02:57 GMT
Carl,
You should be able to use TOP x in your select list, where x is 200 in your
SQL 2005 FTS query, for example:

Select TOP 200 name, address from tst
 where contains(Name, '"Bob Jones"')

You may think that using CONTAINSTABLE (or FREETEXTTABLE) with Top_N_Rank
would also meet your requirement of getting the "first 200 occurrences of
"Bob jones" in the name field" (emphasis on first), but this would be
incorrect.  The Top_N_Rank parameter limits the top number of rows by RANK
and not the *first* rows that contain your search phrase.

Regards,
John

> I have a 10 million row table with two columns(name, address) full text
> indexed. I want to search the table for
[quoted text clipped - 20 lines]
> address varchar(255)
> )
 
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.