Full-Text Search Query Question - Performance

I have a table with 3M rows that contains a varchar(2000) field with
various keywords. Here is the table structure:

PKColumn
ImageID
FullTextColumn

There is an association table:
ImageID
ContractID

Now, I want to do a query where the ContractID = x and Contains some
word in the FullTextColumn. There is an association table that maps
Images to Contracts - so I can't use the trick of putting the Contract
code in the FullTextColumn.

I'm finding that first the FTS service is performing a search on the
Keyword (which can take a long time if 100K rows are returned) then
joining to the association table for the particular contract.

Is there anyway to make this faster by telling the FTS service, only
search this subset of rows for the keyword based on the contract.

Sorry if this sounds convoluted. Appreciate any help you can suggest.

Thanks!jimdandy@.shaw.ca (Jim Dandy) wrote in message news:<705c8539.0405271024.5ce1d19b@.posting.google.com>...
> I have a table with 3M rows that contains a varchar(2000) field with
> various keywords. Here is the table structure:
> PKColumn
> ImageID
> FullTextColumn
> There is an association table:
> ImageID
> ContractID
> Now, I want to do a query where the ContractID = x and Contains some
> word in the FullTextColumn. There is an association table that maps
> Images to Contracts - so I can't use the trick of putting the Contract
> code in the FullTextColumn.
> I'm finding that first the FTS service is performing a search on the
> Keyword (which can take a long time if 100K rows are returned) then
> joining to the association table for the particular contract.
> Is there anyway to make this faster by telling the FTS service, only
> search this subset of rows for the keyword based on the contract.
> Sorry if this sounds convoluted. Appreciate any help you can suggest.
> Thanks!

You might want to post this in microsoft.public.sqlserver.fulltext to
see if you get a better reply.

Simon

Full-text search does not automatically update index when CHANGE_TRACKING AUTO

On Sql Server 2005 Standard if I insert a new row into SomeTable that has a full-text index:

insert SomeTable(keywords)values('this is a test')

and then query:

SELECT * FROM Message WHERE Contains(keywords, ' "test" ');

I get the expected rows all rows that have "test" in the keyword.

On Sql Server 2005 Express new rows are not returned unless I rebuild the catalog.

Is this a known limitation of Express?

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE [dbo].[SomeTable](
[key] [int] IDENTITY(1,1) NOT NULL,
[keywords] [nvarchar](255) NOT NULL
CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED

CREATE FULLTEXT CATALOG ft AS DEFAULT;

CREATE FULLTEXT INDEX ON [dbo].[SomeTable] KEY INDEX [PK_SomeTable] ON [ft] WITH CHANGE_TRACKING AUTO
ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ADD ([keywords])
ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ENABLE

Are you rebuilding the catalog everytime to get the new rows?

full-text search : noun & verb variatrions

My full-text search works fine and it returns correct results, including
records that contain the variation of the keywords.
I bind the result to a gridview (ASP.NET 2.0), and programatically replace
the matched keywords with a highlighted background etc.
The problem is, my program only highlights the exact keywords, but not the
variations of the keywords. (because I don't know what the latter is)
Is there a way that I can query from SQL server what the noun & verb
variations of a specific search word is?
Thanks.Not without building a lookup table of variations. The algorithm which does
the stemming seems to be an implementation of Porter Stemming algorithm.
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
"News User" <NewsUser@.newsuser.com> wrote in message
news:OTSoM9gpGHA.148@.TK2MSFTNGP04.phx.gbl...
> My full-text search works fine and it returns correct results, including
> records that contain the variation of the keywords.
> I bind the result to a gridview (ASP.NET 2.0), and programatically replace
> the matched keywords with a highlighted background etc.
> The problem is, my program only highlights the exact keywords, but not the
> variations of the keywords. (because I don't know what the latter is)
> Is there a way that I can query from SQL server what the noun & verb
> variations of a specific search word is?
> Thanks.
>|||"News User" <NewsUser@.newsuser.com> wrote in message
news:OTSoM9gpGHA.148@.TK2MSFTNGP04.phx.gbl...
> My full-text search works fine and it returns correct results, including
> records that contain the variation of the keywords.
> I bind the result to a gridview (ASP.NET 2.0), and programatically replace
> the matched keywords with a highlighted background etc.
> The problem is, my program only highlights the exact keywords, but not the
> variations of the keywords. (because I don't know what the latter is)
> Is there a way that I can query from SQL server what the noun & verb
> variations of a specific search word is?
>
Try asking in news:microsoft.public.sqlserver.fulltext

> Thanks.
>|||Thanks for the great tip. I went to this page:
http://www.tartarus.org/~martin/PorterStemmer/
and found a T-SQL script that I hope I can implement on my SQL server. I
will give it a shot.
THanks!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%235C3eOhpGHA.3584@.TK2MSFTNGP03.phx.gbl...
> Not without building a lookup table of variations. The algorithm which
> does the stemming seems to be an implementation of Porter Stemming
> algorithm.
> --
> 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
>
> "News User" <NewsUser@.newsuser.com> wrote in message
> news:OTSoM9gpGHA.148@.TK2MSFTNGP04.phx.gbl...
>

full-text search : noun & verb variatrions

My full-text search works fine and it returns correct results, including
records that contain the variation of the keywords.
I bind the result to a gridview (ASP.NET 2.0), and programatically replace
the matched keywords with a highlighted background etc.
The problem is, my program only highlights the exact keywords, but not the
variations of the keywords. (because I don't know what the latter is)
Is there a way that I can query from SQL server what the noun & verb
variations of a specific search word is?
Thanks.Not without building a lookup table of variations. The algorithm which does
the stemming seems to be an implementation of Porter Stemming algorithm.
--
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
"News User" <NewsUser@.newsuser.com> wrote in message
news:OTSoM9gpGHA.148@.TK2MSFTNGP04.phx.gbl...
> My full-text search works fine and it returns correct results, including
> records that contain the variation of the keywords.
> I bind the result to a gridview (ASP.NET 2.0), and programatically replace
> the matched keywords with a highlighted background etc.
> The problem is, my program only highlights the exact keywords, but not the
> variations of the keywords. (because I don't know what the latter is)
> Is there a way that I can query from SQL server what the noun & verb
> variations of a specific search word is?
> Thanks.
>|||"News User" <NewsUser@.newsuser.com> wrote in message
news:OTSoM9gpGHA.148@.TK2MSFTNGP04.phx.gbl...
> My full-text search works fine and it returns correct results, including
> records that contain the variation of the keywords.
> I bind the result to a gridview (ASP.NET 2.0), and programatically replace
> the matched keywords with a highlighted background etc.
> The problem is, my program only highlights the exact keywords, but not the
> variations of the keywords. (because I don't know what the latter is)
> Is there a way that I can query from SQL server what the noun & verb
> variations of a specific search word is?
>
Try asking in news:microsoft.public.sqlserver.fulltext
> Thanks.
>|||Thanks for the great tip. I went to this page:
http://www.tartarus.org/~martin/PorterStemmer/
and found a T-SQL script that I hope I can implement on my SQL server. I
will give it a shot.
THanks!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%235C3eOhpGHA.3584@.TK2MSFTNGP03.phx.gbl...
> Not without building a lookup table of variations. The algorithm which
> does the stemming seems to be an implementation of Porter Stemming
> algorithm.
> --
> 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
>
> "News User" <NewsUser@.newsuser.com> wrote in message
> news:OTSoM9gpGHA.148@.TK2MSFTNGP04.phx.gbl...
>> My full-text search works fine and it returns correct results, including
>> records that contain the variation of the keywords.
>> I bind the result to a gridview (ASP.NET 2.0), and programatically
>> replace the matched keywords with a highlighted background etc.
>> The problem is, my program only highlights the exact keywords, but not
>> the variations of the keywords. (because I don't know what the latter is)
>> Is there a way that I can query from SQL server what the noun & verb
>> variations of a specific search word is?
>> Thanks.
>