2005 fti query performance issue

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)
)
use containstable. It allows you to limit your results set to the first 200
or so rows.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:51042F5E-9BCF-43AE-A232-73670F90EEEB@.microsoft.com...
>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)
> )
|||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
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:51042F5E-9BCF-43AE-A232-73670F90EEEB@.microsoft.com...
> 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)
> )

2005 ft index help

I have numerous vldb's that my mgmt want to have full text indexed. Usually
there are betwen two ad four columns per table that would need the FTI put on
them. FTI on sql2000 failed miserably as it was slow and my tables were
probably too large (smallest is 10 million rows and largest is 800 million
rows).
I would like to know if 2005 FTI, since its been incorporated into the sql
engine, has had its upper limits increased in regards to the size of db can
be indexed.
also, can you replicate a FTI to another server along with its table?
thanks!!!
I believe it was tested to 20 million rows, if not more.
Yes, you can replicate SQL Server 2005 Full Text Indexes and their tables.
Hilary
"Carl Henthorn" wrote:

> I have numerous vldb's that my mgmt want to have full text indexed. Usually
> there are betwen two ad four columns per table that would need the FTI put on
> them. FTI on sql2000 failed miserably as it was slow and my tables were
> probably too large (smallest is 10 million rows and largest is 800 million
> rows).
> I would like to know if 2005 FTI, since its been incorporated into the sql
> engine, has had its upper limits increased in regards to the size of db can
> be indexed.
> also, can you replicate a FTI to another server along with its table?
> thanks!!!
|||that is not very comforting. Why is FT not tested against larger databases?
Is there a max size to the catalog? is there a peformance issue?
"Hilary Cotter" wrote:
[vbcol=seagreen]
> I believe it was tested to 20 million rows, if not more.
> Yes, you can replicate SQL Server 2005 Full Text Indexes and their tables.
> Hilary
> "Carl Henthorn" wrote:
|||I am not sure what the limits that Microsoft has tested it against.
My statistic comes from here:
http://msdn.microsoft.com/library/de...05ftsearch.asp
In this sentence:
.. For example, on the same hardware, with the same data set, building a
full-text index on 20 million rows of character-based text data took roughly
14 days in SQL Server 2000, while in SQL Server 2005, the same index
required less than 10 hours.
But reading on I see this:
SQL Server 2005 full-text catalogs, by contrast, have been tested with and
can support up to 2,000,000,000 rows of data (based on the 4-byte internal
DocId). Further, the indexing process also scales up to that amount of data
on a larger number of CPUs. Scalability of the text engine at indexing time
over multiple CPUs has also improved significantly over previous releases
(the full-text engine scales well up to roughly 16 CPUs on a 32-bit
platform).
I take it that is not a typo and Andrew means 2 billion.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:0EF93B7D-A916-4BB3-B171-3DF9A29A48A7@.microsoft.com...[vbcol=seagreen]
> that is not very comforting. Why is FT not tested against larger
> databases?
> Is there a max size to the catalog? is there a peformance issue?
> "Hilary Cotter" wrote: