I have a full text indexed xml column in sql server 2005 containing the
following xml snippet:
<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says 'tis but our fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him along</pagetext>
</page>
</folder>
</plays>
I'd like to be able to search this document for keywords, such as
"belief" to return the value '@.id=9267' i.e. return the id value of the
containing node ('page') where the string "belief" was found.
Likewise, if the search was for "him" then '@.id=9267' and '@.id=8423'
would be returned.
Could anyone give me pointers as to how to implement this in XQuery /
Full-text?
Many thanks, David
Here is a possible implementation:
begin
declare @.x xml
set @.x='
<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him along</pagetext>
</page>
</folder>
</plays>
'
declare @.x2 xml
set @.x2 = (SELECT T.c.query('.') AS risultato FROM
@.x.nodes('/plays/folder/page[@.id]') T(c) where T.c.value('.','varchar(50)')
like '%Hor%')
select @.x2.value('(/page/@.id)[1]','int')
end
OK?
"firechaser@.talk21.com" wrote:
> I have a full text indexed xml column in sql server 2005 containing the
> following xml snippet:
> <plays>
> <folder id="681">
> <page id="3155">
> <submitdate>12-10-02</submitdate>
> <pagetext>Horatio says 'tis but our fantasy,</pagetext>
> </page>
> <page id="9267">
> <submitdate>09-04-04</submitdate>
> <pagetext>And will not let belief take hold of him</pagetext>
> </page>
> </folder>
> <folder id="902">
> <page id="1853">
> <submitdate>22-11-05</submitdate>
> <pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
> </page>
> <page id="8423">
> <submitdate>31-05-02</submitdate>
> <pagetext>Therefore I have entreated him along</pagetext>
> </page>
> </folder>
> </plays>
>
> I'd like to be able to search this document for keywords, such as
> "belief" to return the value '@.id=9267' i.e. return the id value of the
> containing node ('page') where the string "belief" was found.
> Likewise, if the search was for "him" then '@.id=9267' and '@.id=8423'
> would be returned.
> Could anyone give me pointers as to how to implement this in XQuery /
> Full-text?
> Many thanks, David
>
|||Thanks very much for your time Luca.
Unfortunately I was hoping to use the full-text capabilities in SQL
Server 2005 to produce *multiple* search results from any single xml
snippet stored in a single (and known in advance) row.
All the FTS examples I've seen are of the type
select xmlcol from t1
where contains(xmlcol, '"dog cat"')
which return the whole xml record. Is there a way to accomplish this on
a single record as per xml snippet in my original post which exists as
an xml document in a single row in the table?
I will know *in advance* which record in the table needs to be
queried, so I need to be able to query the actual content of the xml
cell and return 0,1 or more matching results based upon that xml
snippet and *not* based upon matches spanning several rows.
Looking at the above snippet, I need to be able to search for the word
"him" which would give me '@.id=9267' and '@.id=8423' returned.
I'm not looking to search across rows - that appears straightforward -
I'm trying to perform searches *within* the xml cell that exits in a
single (and at the moment, unique) record in my table.
I'd be very grateful for any help in trying to explain this, as I'm at
a loss at the moment.
Thanks again, David
|||try this
create database FullTextXML
use FullTextXML
GO
drop table XMLFULLText
drop XML SCHEMA COLLECTION PlaysSchema
CREATE XML SCHEMA COLLECTION PlaysSchema AS '
<xsd:schema targetNamespace="http://www.plays.com/plays"
xmlns ="http://www.plays.com/plays"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="plays"><xsd:complexType><xsd:complexContent>
<xsd:restriction base="xsd:anyType"><xsd:choice
maxOccurs="unbounded"><xsd:element name="folder">
<xsd:complexType><xsd:complexContent><xsd:restrict ion
base="xsd:anyType"><xsd:sequence>
<xsd:element name="page" minOccurs="0"
maxOccurs="unbounded"><xsd:complexType>
<xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence>
<xsd:element name="submitdate" type="xsd:string" minOccurs="0" />
<xsd:element name="pagetext" type="xsd:string" minOccurs="0" />
</xsd:sequence><xsd:attribute name="id" type="xsd:string"
/></xsd:restriction></xsd:complexContent>
</xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="id"
type="xsd:string" />
</xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:choice></xsd:restriction>
</xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>'
GO
Create Table XMLFULLText (pk int not null identity constraint XMLFULLTEXTPK
primary key, XMLDOC XML(PlaysSchema))
GO
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder></plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
sp_fulltext_database 'enable'
GO
create fulltext catalog test as default
GO
create fulltext index on XMLFULLText (XMLDOC) key index XMLFULLTextPK
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned. OK, lets make this a little more interesting
GO
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let beliefs take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned.
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 row returned. OK, lets restrict this to contents coming from an element
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>belief</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--2 rows returned, one in page text and one in submit date
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 rows returned. OK, lets restrict this to contents coming from an element
select * from XMLFulltext where contains(*,'belief')
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/plays/folder/page/pagetext/text()[contains(.,"belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC.query('
-- <pd:plays>
--{/pd:folder/pd:page/pd:pagetext}
-- </pd:plays>
--') AS Result
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays/pd:folder/pd:page/pd:pagetext[1]/text()[contains(.,"belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription'
AS pd)
SELECT CatalogDescription.query('
<pd:Product ProductModelID="{
(/pd:ProductDescription/@.ProductModelID)[1] }">
<Picture>
{ /pd:ProductDescription/pd:Picture/pd:Angle }
{ /pd:ProductDescription/pd:Picture/pd:Size }
</Picture>
</pd:Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription/pd:Picture') = 1
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Angle)[1]',
'varchar(20)') = 'front'
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Size)[1]',
'varchar(20)') = 'small'
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[1]/pd:submitdate[contains(.,"belief")]')=1
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[2]/pd:pagetext[contains(.,"belief")]')=1
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page/pd:pagetext[contains(.,"belief")]')=1
Hilary Cotter
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
<firechaser@.talk21.com> wrote in message
news:1139433546.490894.304730@.g47g2000cwa.googlegr oups.com...
> Thanks very much for your time Luca.
> Unfortunately I was hoping to use the full-text capabilities in SQL
> Server 2005 to produce *multiple* search results from any single xml
> snippet stored in a single (and known in advance) row.
> All the FTS examples I've seen are of the type
> select xmlcol from t1
> where contains(xmlcol, '"dog cat"')
> which return the whole xml record. Is there a way to accomplish this on
> a single record as per xml snippet in my original post which exists as
> an xml document in a single row in the table?
> I will know *in advance* which record in the table needs to be
> queried, so I need to be able to query the actual content of the xml
> cell and return 0,1 or more matching results based upon that xml
> snippet and *not* based upon matches spanning several rows.
> Looking at the above snippet, I need to be able to search for the word
> "him" which would give me '@.id=9267' and '@.id=8423' returned.
> I'm not looking to search across rows - that appears straightforward -
> I'm trying to perform searches *within* the xml cell that exits in a
> single (and at the moment, unique) record in my table.
> I'd be very grateful for any help in trying to explain this, as I'm at
> a loss at the moment.
> Thanks again, David
>
|||Even with Hilary's workaround there is no good full-text search support at
the sub XML datatype level yet. Mainly because the XQuery language is just
now working on a full-text extension to XQuery.
Can I please ask you to file a feature request at
http://lab.msdn.microsoft.com/productfeedback/
We are certainly looking into adding such functionality and actually
customer use cases are important for us to motivate the investment to our
purse holders.
As a short-term work around, I recommend to split the sections into one per
row (using the nodes() method) and then FT-index that column.
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1139433546.490894.304730@.g47g2000cwa.googlegr oups.com...
> Thanks very much for your time Luca.
> Unfortunately I was hoping to use the full-text capabilities in SQL
> Server 2005 to produce *multiple* search results from any single xml
> snippet stored in a single (and known in advance) row.
> All the FTS examples I've seen are of the type
> select xmlcol from t1
> where contains(xmlcol, '"dog cat"')
> which return the whole xml record. Is there a way to accomplish this on
> a single record as per xml snippet in my original post which exists as
> an xml document in a single row in the table?
> I will know *in advance* which record in the table needs to be
> queried, so I need to be able to query the actual content of the xml
> cell and return 0,1 or more matching results based upon that xml
> snippet and *not* based upon matches spanning several rows.
> Looking at the above snippet, I need to be able to search for the word
> "him" which would give me '@.id=9267' and '@.id=8423' returned.
> I'm not looking to search across rows - that appears straightforward -
> I'm trying to perform searches *within* the xml cell that exits in a
> single (and at the moment, unique) record in my table.
> I'd be very grateful for any help in trying to explain this, as I'm at
> a loss at the moment.
> Thanks again, David
>
2012年3月27日星期二
full text search within single xml document
I have a full text indexed xml column in sql server 2005 containing the
following xml snippet:
<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says 'tis but our fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him along</pagetext>
</page>
</folder>
</plays>
I'd like to be able to search this document for keywords, such as
"belief" to return the value '@.id=9267' i.e. return the id value of the
containing node ('page') where the string "belief" was found.
Likewise, if the search was for "him" then '@.id=9267' and '@.id=8423'
would be returned.
Could anyone give me pointers as to how to implement this in XQuery /
Full-text?
Many thanks, DavidHere is a possible implementation:
begin
declare @.x xml
set @.x='
<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him along</pagetext>
</page>
</folder>
</plays>
'
declare @.x2 xml
set @.x2 = (SELECT T.c.query('.') AS risultato FROM
@.x.nodes('/plays/folder/page[@.id]') T(c) where T.c.value('.','varchar(50)')
like '%Hor%')
select @.x2.value('(/page/@.id)[1]','int')
end
OK?
"firechaser@.talk21.com" wrote:
> I have a full text indexed xml column in sql server 2005 containing the
> following xml snippet:
> <plays>
> <folder id="681">
> <page id="3155">
> <submitdate>12-10-02</submitdate>
> <pagetext>Horatio says 'tis but our fantasy,</pagetext>
> </page>
> <page id="9267">
> <submitdate>09-04-04</submitdate>
> <pagetext>And will not let belief take hold of him</pagetext>
> </page>
> </folder>
> <folder id="902">
> <page id="1853">
> <submitdate>22-11-05</submitdate>
> <pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
> </page>
> <page id="8423">
> <submitdate>31-05-02</submitdate>
> <pagetext>Therefore I have entreated him along</pagetext>
> </page>
> </folder>
> </plays>
>
> I'd like to be able to search this document for keywords, such as
> "belief" to return the value '@.id=9267' i.e. return the id value of the
> containing node ('page') where the string "belief" was found.
> Likewise, if the search was for "him" then '@.id=9267' and '@.id=8423'
> would be returned.
> Could anyone give me pointers as to how to implement this in XQuery /
> Full-text?
> Many thanks, David
>|||Thanks very much for your time Luca.
Unfortunately I was hoping to use the full-text capabilities in SQL
Server 2005 to produce *multiple* search results from any single xml
snippet stored in a single (and known in advance) row.
All the FTS examples I've seen are of the type
select xmlcol from t1
where contains(xmlcol, '"dog cat"')
which return the whole xml record. Is there a way to accomplish this on
a single record as per xml snippet in my original post which exists as
an xml document in a single row in the table?
I will know *in advance* which record in the table needs to be
queried, so I need to be able to query the actual content of the xml
cell and return 0,1 or more matching results based upon that xml
snippet and *not* based upon matches spanning several rows.
Looking at the above snippet, I need to be able to search for the word
"him" which would give me '@.id=9267' and '@.id=8423' returned.
I'm not looking to search across rows - that appears straightforward -
I'm trying to perform searches *within* the xml cell that exits in a
single (and at the moment, unique) record in my table.
I'd be very grateful for any help in trying to explain this, as I'm at
a loss at the moment.
Thanks again, David|||try this
create database FullTextXML
use FullTextXML
GO
drop table XMLFULLText
drop XML SCHEMA COLLECTION PlaysSchema
CREATE XML SCHEMA COLLECTION PlaysSchema AS '
<xsd:schema targetNamespace="http://www.plays.com/plays"
xmlns ="http://www.plays.com/plays"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="plays"><xsd:complexType><xsd:complexContent>
<xsd:restriction base="xsd:anyType"><xsd:choice
maxOccurs="unbounded"><xsd:element name="folder">
<xsd:complexType><xsd:complexContent><xsd:restriction
base="xsd:anyType"><xsd:sequence>
<xsd:element name="page" minOccurs="0"
maxOccurs="unbounded"><xsd:complexType>
<xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence>
<xsd:element name="submitdate" type="xsd:string" minOccurs="0" />
<xsd:element name="pagetext" type="xsd:string" minOccurs="0" />
</xsd:sequence><xsd:attribute name="id" type="xsd:string"
/></xsd:restriction></xsd:complexContent>
</xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="id"
type="xsd:string" />
</xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd
:choice></xsd:restriction>
</xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>'
GO
Create Table XMLFULLText (pk int not null identity constraint XMLFULLTEXTPK
primary key, XMLDOC XML(PlaysSchema))
GO
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder></plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
sp_fulltext_database 'enable'
GO
create fulltext catalog test as default
GO
create fulltext index on XMLFULLText (XMLDOC) key index XMLFULLTextPK
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned. OK, lets make this a little more interesting
GO
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let beliefs take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned.
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 row returned. OK, lets restrict this to contents coming from an element
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>belief</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--2 rows returned, one in page text and one in submit date
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 rows returned. OK, lets restrict this to contents coming from an element
select * from XMLFulltext where contains(*,'belief')
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/plays/folder/page/pagetext/text()[contains(.,"belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC.query('
-- <pd:plays>
--{/pd:folder/pd:page/pd:pagetext}
-- </pd:plays>
--') AS Result
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays/pd:folder/pd:page/pd:pagetext[1]/text()[contains(.,"
belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductMode
lDescription'
AS pd)
SELECT CatalogDescription.query('
<pd:Product ProductModelID="{
(/pd:ProductDescription/@.ProductModelID)[1] }">
<Picture>
{ /pd:ProductDescription/pd:Picture/pd:Angle }
{ /pd:ProductDescription/pd:Picture/pd:Size }
</Picture>
</pd:Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription/pd:Picture') = 1
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Angle)[1]',
'varchar(20)') = 'front'
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Size)[1]',
'varchar(20)') = 'small'
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[1]/pd:submitdate[contains(.,
"belief")]')=1
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[2]/pd:pagetext[contains(.,"b
elief")]')=1
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page/pd:pagetext[contains(.,"beli
ef")]')=1
Hilary Cotter
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
<firechaser@.talk21.com> wrote in message
news:1139433546.490894.304730@.g47g2000cwa.googlegroups.com...
> Thanks very much for your time Luca.
> Unfortunately I was hoping to use the full-text capabilities in SQL
> Server 2005 to produce *multiple* search results from any single xml
> snippet stored in a single (and known in advance) row.
> All the FTS examples I've seen are of the type
> select xmlcol from t1
> where contains(xmlcol, '"dog cat"')
> which return the whole xml record. Is there a way to accomplish this on
> a single record as per xml snippet in my original post which exists as
> an xml document in a single row in the table?
> I will know *in advance* which record in the table needs to be
> queried, so I need to be able to query the actual content of the xml
> cell and return 0,1 or more matching results based upon that xml
> snippet and *not* based upon matches spanning several rows.
> Looking at the above snippet, I need to be able to search for the word
> "him" which would give me '@.id=9267' and '@.id=8423' returned.
> I'm not looking to search across rows - that appears straightforward -
> I'm trying to perform searches *within* the xml cell that exits in a
> single (and at the moment, unique) record in my table.
> I'd be very grateful for any help in trying to explain this, as I'm at
> a loss at the moment.
> Thanks again, David
>|||Even with Hilary's workaround there is no good full-text search support at
the sub XML datatype level yet. Mainly because the XQuery language is just
now working on a full-text extension to XQuery.
Can I please ask you to file a feature request at
http://lab.msdn.microsoft.com/productfeedback/
We are certainly looking into adding such functionality and actually
customer use cases are important for us to motivate the investment to our
purse holders.
As a short-term work around, I recommend to split the sections into one per
row (using the nodes() method) and then FT-index that column.
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1139433546.490894.304730@.g47g2000cwa.googlegroups.com...
> Thanks very much for your time Luca.
> Unfortunately I was hoping to use the full-text capabilities in SQL
> Server 2005 to produce *multiple* search results from any single xml
> snippet stored in a single (and known in advance) row.
> All the FTS examples I've seen are of the type
> select xmlcol from t1
> where contains(xmlcol, '"dog cat"')
> which return the whole xml record. Is there a way to accomplish this on
> a single record as per xml snippet in my original post which exists as
> an xml document in a single row in the table?
> I will know *in advance* which record in the table needs to be
> queried, so I need to be able to query the actual content of the xml
> cell and return 0,1 or more matching results based upon that xml
> snippet and *not* based upon matches spanning several rows.
> Looking at the above snippet, I need to be able to search for the word
> "him" which would give me '@.id=9267' and '@.id=8423' returned.
> I'm not looking to search across rows - that appears straightforward -
> I'm trying to perform searches *within* the xml cell that exits in a
> single (and at the moment, unique) record in my table.
> I'd be very grateful for any help in trying to explain this, as I'm at
> a loss at the moment.
> Thanks again, David
>
following xml snippet:
<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says 'tis but our fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him along</pagetext>
</page>
</folder>
</plays>
I'd like to be able to search this document for keywords, such as
"belief" to return the value '@.id=9267' i.e. return the id value of the
containing node ('page') where the string "belief" was found.
Likewise, if the search was for "him" then '@.id=9267' and '@.id=8423'
would be returned.
Could anyone give me pointers as to how to implement this in XQuery /
Full-text?
Many thanks, DavidHere is a possible implementation:
begin
declare @.x xml
set @.x='
<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him along</pagetext>
</page>
</folder>
</plays>
'
declare @.x2 xml
set @.x2 = (SELECT T.c.query('.') AS risultato FROM
@.x.nodes('/plays/folder/page[@.id]') T(c) where T.c.value('.','varchar(50)')
like '%Hor%')
select @.x2.value('(/page/@.id)[1]','int')
end
OK?
"firechaser@.talk21.com" wrote:
> I have a full text indexed xml column in sql server 2005 containing the
> following xml snippet:
> <plays>
> <folder id="681">
> <page id="3155">
> <submitdate>12-10-02</submitdate>
> <pagetext>Horatio says 'tis but our fantasy,</pagetext>
> </page>
> <page id="9267">
> <submitdate>09-04-04</submitdate>
> <pagetext>And will not let belief take hold of him</pagetext>
> </page>
> </folder>
> <folder id="902">
> <page id="1853">
> <submitdate>22-11-05</submitdate>
> <pagetext>Touching this dreaded sight, twice seen of us:</pagetext>
> </page>
> <page id="8423">
> <submitdate>31-05-02</submitdate>
> <pagetext>Therefore I have entreated him along</pagetext>
> </page>
> </folder>
> </plays>
>
> I'd like to be able to search this document for keywords, such as
> "belief" to return the value '@.id=9267' i.e. return the id value of the
> containing node ('page') where the string "belief" was found.
> Likewise, if the search was for "him" then '@.id=9267' and '@.id=8423'
> would be returned.
> Could anyone give me pointers as to how to implement this in XQuery /
> Full-text?
> Many thanks, David
>|||Thanks very much for your time Luca.
Unfortunately I was hoping to use the full-text capabilities in SQL
Server 2005 to produce *multiple* search results from any single xml
snippet stored in a single (and known in advance) row.
All the FTS examples I've seen are of the type
select xmlcol from t1
where contains(xmlcol, '"dog cat"')
which return the whole xml record. Is there a way to accomplish this on
a single record as per xml snippet in my original post which exists as
an xml document in a single row in the table?
I will know *in advance* which record in the table needs to be
queried, so I need to be able to query the actual content of the xml
cell and return 0,1 or more matching results based upon that xml
snippet and *not* based upon matches spanning several rows.
Looking at the above snippet, I need to be able to search for the word
"him" which would give me '@.id=9267' and '@.id=8423' returned.
I'm not looking to search across rows - that appears straightforward -
I'm trying to perform searches *within* the xml cell that exits in a
single (and at the moment, unique) record in my table.
I'd be very grateful for any help in trying to explain this, as I'm at
a loss at the moment.
Thanks again, David|||try this
create database FullTextXML
use FullTextXML
GO
drop table XMLFULLText
drop XML SCHEMA COLLECTION PlaysSchema
CREATE XML SCHEMA COLLECTION PlaysSchema AS '
<xsd:schema targetNamespace="http://www.plays.com/plays"
xmlns ="http://www.plays.com/plays"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="plays"><xsd:complexType><xsd:complexContent>
<xsd:restriction base="xsd:anyType"><xsd:choice
maxOccurs="unbounded"><xsd:element name="folder">
<xsd:complexType><xsd:complexContent><xsd:restriction
base="xsd:anyType"><xsd:sequence>
<xsd:element name="page" minOccurs="0"
maxOccurs="unbounded"><xsd:complexType>
<xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence>
<xsd:element name="submitdate" type="xsd:string" minOccurs="0" />
<xsd:element name="pagetext" type="xsd:string" minOccurs="0" />
</xsd:sequence><xsd:attribute name="id" type="xsd:string"
/></xsd:restriction></xsd:complexContent>
</xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="id"
type="xsd:string" />
</xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd
:choice></xsd:restriction>
</xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>'
GO
Create Table XMLFULLText (pk int not null identity constraint XMLFULLTEXTPK
primary key, XMLDOC XML(PlaysSchema))
GO
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder></plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
sp_fulltext_database 'enable'
GO
create fulltext catalog test as default
GO
create fulltext index on XMLFULLText (XMLDOC) key index XMLFULLTextPK
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned. OK, lets make this a little more interesting
GO
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let beliefs take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned.
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 row returned. OK, lets restrict this to contents coming from an element
declare @.XMLDOC XML
set @.XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>belief</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@.XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--2 rows returned, one in page text and one in submit date
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 rows returned. OK, lets restrict this to contents coming from an element
select * from XMLFulltext where contains(*,'belief')
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/plays/folder/page/pagetext/text()[contains(.,"belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC.query('
-- <pd:plays>
--{/pd:folder/pd:page/pd:pagetext}
-- </pd:plays>
--') AS Result
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays/pd:folder/pd:page/pd:pagetext[1]/text()[contains(.,"
belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductMode
lDescription'
AS pd)
SELECT CatalogDescription.query('
<pd:Product ProductModelID="{
(/pd:ProductDescription/@.ProductModelID)[1] }">
<Picture>
{ /pd:ProductDescription/pd:Picture/pd:Angle }
{ /pd:ProductDescription/pd:Picture/pd:Size }
</Picture>
</pd:Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription/pd:Picture') = 1
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Angle)[1]',
'varchar(20)') = 'front'
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Size)[1]',
'varchar(20)') = 'small'
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[1]/pd:submitdate[contains(.,
"belief")]')=1
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[2]/pd:pagetext[contains(.,"b
elief")]')=1
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page/pd:pagetext[contains(.,"beli
ef")]')=1
Hilary Cotter
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
<firechaser@.talk21.com> wrote in message
news:1139433546.490894.304730@.g47g2000cwa.googlegroups.com...
> Thanks very much for your time Luca.
> Unfortunately I was hoping to use the full-text capabilities in SQL
> Server 2005 to produce *multiple* search results from any single xml
> snippet stored in a single (and known in advance) row.
> All the FTS examples I've seen are of the type
> select xmlcol from t1
> where contains(xmlcol, '"dog cat"')
> which return the whole xml record. Is there a way to accomplish this on
> a single record as per xml snippet in my original post which exists as
> an xml document in a single row in the table?
> I will know *in advance* which record in the table needs to be
> queried, so I need to be able to query the actual content of the xml
> cell and return 0,1 or more matching results based upon that xml
> snippet and *not* based upon matches spanning several rows.
> Looking at the above snippet, I need to be able to search for the word
> "him" which would give me '@.id=9267' and '@.id=8423' returned.
> I'm not looking to search across rows - that appears straightforward -
> I'm trying to perform searches *within* the xml cell that exits in a
> single (and at the moment, unique) record in my table.
> I'd be very grateful for any help in trying to explain this, as I'm at
> a loss at the moment.
> Thanks again, David
>|||Even with Hilary's workaround there is no good full-text search support at
the sub XML datatype level yet. Mainly because the XQuery language is just
now working on a full-text extension to XQuery.
Can I please ask you to file a feature request at
http://lab.msdn.microsoft.com/productfeedback/
We are certainly looking into adding such functionality and actually
customer use cases are important for us to motivate the investment to our
purse holders.
As a short-term work around, I recommend to split the sections into one per
row (using the nodes() method) and then FT-index that column.
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1139433546.490894.304730@.g47g2000cwa.googlegroups.com...
> Thanks very much for your time Luca.
> Unfortunately I was hoping to use the full-text capabilities in SQL
> Server 2005 to produce *multiple* search results from any single xml
> snippet stored in a single (and known in advance) row.
> All the FTS examples I've seen are of the type
> select xmlcol from t1
> where contains(xmlcol, '"dog cat"')
> which return the whole xml record. Is there a way to accomplish this on
> a single record as per xml snippet in my original post which exists as
> an xml document in a single row in the table?
> I will know *in advance* which record in the table needs to be
> queried, so I need to be able to query the actual content of the xml
> cell and return 0,1 or more matching results based upon that xml
> snippet and *not* based upon matches spanning several rows.
> Looking at the above snippet, I need to be able to search for the word
> "him" which would give me '@.id=9267' and '@.id=8423' returned.
> I'm not looking to search across rows - that appears straightforward -
> I'm trying to perform searches *within* the xml cell that exits in a
> single (and at the moment, unique) record in my table.
> I'd be very grateful for any help in trying to explain this, as I'm at
> a loss at the moment.
> Thanks again, David
>
Full Text Search Setup
I am trying to setup FTS on a table in my database; the column I am
interested in FTS'ing is all image datatypes. The data in the colum is
populated via asp.net; and we store the document type as a mime type e.g.
'application/msword' as it makes it easy to get data back out from the
database.
My table has teh following columns
DocumentID UniqueIdentifier,
DocumentTypeRef int
DocumentSubject varchar(100)
DocumentValue image
DocumentDocType varchar(50)
DocumentDocType sysname -- added to use FTS would rather use vc(50) field
DocumentByteSize int
DocumentFileName varchar(100)
DocumentCreated datetime
MailMergeTemplateID uniqueidentifier
ToDoID uniqueidentifier
DocumentUploadWaiting bit
The table has 50 rows in it; in my test system. mostly word documents. I
have run teh following script to setup fts (and tried the wizard without
success)
use Activities_FTS
exec sp_fulltext_database 'enable'
exec sp_fulltext_catalog 'FTS', 'create', 'D:\\sqldata'
exec sp_fulltext_table 'tblDocuments', 'create', 'FTS', 'PK_tblDocuments'
exec sp_fulltext_column @.tabname = tblDocuments,
@.colname = DocumentValue,
@.action = 'dro',
@.Type_Colname = DocumentDocType2
exec sp_fulltext_table 'tblDocuments', 'activate'
exec sp_fulltext_table 'tblDocuments', 'start_full'
select FULLTEXTCATALOGPROPERTY('FTS', 'Populatestatus')
select * from freetexttable(tblDocuments,DocumentValue , '%%') order by
[rank] desc
This is based around the steps which worked successfully from the tutorial
on this page
http://msdn.microsoft.com/library/de...extsearch.asp.
When i run the index population check it does run for about 30 seconds; but i
cannot find out why it is not indexing the documents; or is there another
query i can use to test this. (My FTS catalog never grows above 1mb)
If you have any questions please do not hestitate to reply as i am slightly
lost now
Many thanks
Chris
Can you check what your gatherer logs report? I'm not sure if storing them
as a mime type is the best way to go.
To check your gatherer log output do this:
go to c:\program files\common files\system\mssearch\bin and copy gthrlog.vbs
to %windir% then go to your catalog location for your catalog (this should
be c:\program files\microsoft sql server\mssql\ftdata\sqlserver\gatherlogs.
Now you want to id your gatherlog for your catalog. the totally brainless
what to do this is to issue another incremental population for your catalog
and then do a dir /od. Your catalog
gather logs will then appear last in the list. The syntax is
SQLXXXXXYYYYY.?.gthr where the X is your db_id (you can determine this by
doing
this select db_id('database_name') and the YYYYYY is your catalog id, which
you can determine by doing to your database in isqlw and doing this select
id, name from syscatalogs where name ='catalog_name'.
Then with this information what you do is this (from a command prompt)
cscript gthrlog.vbs SQL0017900635.6.gthr
You will get a lot of output, but it will tell you on which row(s) the
indexer had a problem. Then what you have to do is have a look at that row
and see if you can figure out what the problem is with the data in that row
"Chris Hoare" <ChrisHoare@.discussions.microsoft.com> wrote in message
news:AB36FF24-EE13-4C7A-B8E5-E110588526CC@.microsoft.com...
>I am trying to setup FTS on a table in my database; the column I am
> interested in FTS'ing is all image datatypes. The data in the colum is
> populated via asp.net; and we store the document type as a mime type e.g.
> 'application/msword' as it makes it easy to get data back out from the
> database.
> My table has teh following columns
> DocumentID UniqueIdentifier,
> DocumentTypeRef int
> DocumentSubject varchar(100)
> DocumentValue image
> DocumentDocType varchar(50)
> DocumentDocType sysname -- added to use FTS would rather use vc(50) field
> DocumentByteSize int
> DocumentFileName varchar(100)
> DocumentCreated datetime
> MailMergeTemplateID uniqueidentifier
> ToDoID uniqueidentifier
> DocumentUploadWaiting bit
> The table has 50 rows in it; in my test system. mostly word documents. I
> have run teh following script to setup fts (and tried the wizard without
> success)
> use Activities_FTS
> exec sp_fulltext_database 'enable'
> exec sp_fulltext_catalog 'FTS', 'create', 'D:\\sqldata'
> exec sp_fulltext_table 'tblDocuments', 'create', 'FTS', 'PK_tblDocuments'
> exec sp_fulltext_column @.tabname = tblDocuments,
> @.colname = DocumentValue,
> @.action = 'dro',
> @.Type_Colname = DocumentDocType2
> exec sp_fulltext_table 'tblDocuments', 'activate'
> exec sp_fulltext_table 'tblDocuments', 'start_full'
> --
> select FULLTEXTCATALOGPROPERTY('FTS', 'Populatestatus')
> select * from freetexttable(tblDocuments,DocumentValue , '%%') order by
> [rank] desc
>
> This is based around the steps which worked successfully from the tutorial
> on this page
> http://msdn.microsoft.com/library/de...extsearch.asp.
> When i run the index population check it does run for about 30 seconds;
> but i
> cannot find out why it is not indexing the documents; or is there another
> query i can use to test this. (My FTS catalog never grows above 1mb)
> If you have any questions please do not hestitate to reply as i am
> slightly
> lost now
> Many thanks
> Chris
|||Chris,
I'm assuming that you're using SQL Server 2000 as this feature is new with
SQL Server 2000, but what OS platform is it installed on? Could you post the
full output of -- SELECT @.@.version -- as this is very helpful in
troubleshooting SQL FTS issues!
You should use the datatype of sysname (or char(3) or varchar(4)) with your
column "DocumentDocType". Could you also provide details on what values you
have populated in this column? Furthermore, how exactly did you import the
MS Word documents and what is the language of the text stored in the MS Word
documents?
Additionally, the following FREETEXTTABLE query will not return any results
as the "%" (percent) symbols are ignored by the MSSearch engine (depending
upon the OS platform):
select * from freetexttable(tblDocuments,DocumentValue , '%%') order by
[rank] desc
Instead you should use the following:
declare @.searchTerm varchar(1024)
set @.searchTerm = 'some_valid_search_word_here'
select * from freetexttable(tblDocuments, DocumentValue, @.searchTerm) order
by [rank] desc
Finally, you should review your server's Application event log for any
"Microsoft Search" or MssCi source events (warnings, informational and
errors) to determine why the initial FT Indexing is failing as this is the
only place such errors or warnings are written. Have you or anyone else
changed the SQL Server (MSSQLServer) service account &/or password via
Win2K's Component Services vs. changing this in the Enterprise Manager? If
so, then you should also review KB article 277549 (Q277549) PRB: Unable to
Build Full-Text Catalog After You Modify MSSQLServer Logon Account Through
[NT4.0) Control Panel [or Win2K Component Services] at:
http://support.microsoft.com/default...B;EN-US;277549
Regards,
John
"Chris Hoare" <ChrisHoare@.discussions.microsoft.com> wrote in message
news:AB36FF24-EE13-4C7A-B8E5-E110588526CC@.microsoft.com...
> I am trying to setup FTS on a table in my database; the column I am
> interested in FTS'ing is all image datatypes. The data in the colum is
> populated via asp.net; and we store the document type as a mime type e.g.
> 'application/msword' as it makes it easy to get data back out from the
> database.
> My table has teh following columns
> DocumentID UniqueIdentifier,
> DocumentTypeRef int
> DocumentSubject varchar(100)
> DocumentValue image
> DocumentDocType varchar(50)
> DocumentDocType sysname -- added to use FTS would rather use vc(50) field
> DocumentByteSize int
> DocumentFileName varchar(100)
> DocumentCreated datetime
> MailMergeTemplateID uniqueidentifier
> ToDoID uniqueidentifier
> DocumentUploadWaiting bit
> The table has 50 rows in it; in my test system. mostly word documents. I
> have run teh following script to setup fts (and tried the wizard without
> success)
> use Activities_FTS
> exec sp_fulltext_database 'enable'
> exec sp_fulltext_catalog 'FTS', 'create', 'D:\\sqldata'
> exec sp_fulltext_table 'tblDocuments', 'create', 'FTS', 'PK_tblDocuments'
> exec sp_fulltext_column @.tabname = tblDocuments,
> @.colname = DocumentValue,
> @.action = 'dro',
> @.Type_Colname = DocumentDocType2
> exec sp_fulltext_table 'tblDocuments', 'activate'
> exec sp_fulltext_table 'tblDocuments', 'start_full'
> --
> select FULLTEXTCATALOGPROPERTY('FTS', 'Populatestatus')
> select * from freetexttable(tblDocuments,DocumentValue , '%%') order by
> [rank] desc
>
> This is based around the steps which worked successfully from the tutorial
> on this page
>
http://msdn.microsoft.com/library/de...extsearch.asp.
> When i run the index population check it does run for about 30 seconds;
but i
> cannot find out why it is not indexing the documents; or is there another
> query i can use to test this. (My FTS catalog never grows above 1mb)
> If you have any questions please do not hestitate to reply as i am
slightly
> lost now
> Many thanks
> Chris
|||OK,
SQL Server details
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
The documents were all imported using request.file object in asp.net; and
were streamed into a stored procuedre. ASP.net can open these files again
(which is why we are storing the mime type.)
The column I referenced as DocumentDocType2 is typed as 'sysname.' this is
the column that i am referencing as Documenttype for sp_fulltext_column
I have changed the contents of the documentdoctype2 column to be both
'word' & 'msword' to be safe and run the recatalog command (Originally these
were mime types 'application/msword' or similar. In both cases this was the
result;
The application log shows the following result : Event ID 2001
"One or more documents stored in image columns with extension 'word' did not
get full-text indexed because loading the filter failed with error '0x1'.
Note: These documents will not be passed to MSSearch for indexing, and
therefore this failure will not be reflected in the end of crawl summary
statistics."
Though strangely it went on to say
"The end of crawl for project <SQLServer SQL0003400005> has been detected.
The Gatherer successfully processed 50 documents totaling 0K. It failed to
filter 0 documents. 0 URLs could not be reached or were denied access."
Finally there is an event for Master merge has completed on the index.
Thanks for your help
|||You're welcome, Chris,
Thanks for the version info as that does help, especially the fact that you
have SQL Server 2000 SP3 on Win2003! You may want to review all of the below
KB articles, and especially 326502 (Q326502) as it provides coding example
on how to use ASP.NET to load MS Word files via:
Response.AddHeader("Content-Disposition", "attachment;filename=blob.doc")
Response.ContentType = "application/msword"
The most likely cause of the Full Population failing is that only valid MS
Word (doc or .doc) files are supported when properly imported into a SQL
Server 2000 table's column defined with the IMAGE (or BLOB) datatype.
Additionally, by populating the column documentdoctype2 column with both
'word' & 'msword' will not succeed as the MSSearch service does not
recognize these as valid file extensions. See SQL Server 2000 BOL title
"Filtering Supported File Types" - specifically, "Microsoft SQL ServerT
2000 includes filters for these file extensions: .doc, .xls, .ppt, .txt, and
..htm".
I grant you that the second informational MSSearch message text was somewhat
mis-leading, but the key error "One or more documents stored in image
columns with extension 'word' did not get full-text indexed because loading
the filter failed with error '0x1'" indicates the FT Population failure
because the wrong value was placed in the documentdoctype2 column.
258038 (Q258038) HOWTO: Access and Modify SQL Server BLOB Data by Using the
ADO Stream Object
http://support.microsoft.com/?kbid=258038
309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#
http://support.microsoft.com/default...b;EN-US;309158
308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with
VB.NET
http://support.microsoft.com/default...b;EN-US;308042
326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through
ASP.NET
http://support.microsoft.com/?id=326502
Regards,
John
"Chris Hoare" <ChrisHoare@.discussions.microsoft.com> wrote in message
news:066C2D40-1BF5-4B26-821E-69F71F39844C@.microsoft.com...
> OK,
> SQL Server details
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> The documents were all imported using request.file object in asp.net; and
> were streamed into a stored procuedre. ASP.net can open these files again
> (which is why we are storing the mime type.)
> The column I referenced as DocumentDocType2 is typed as 'sysname.' this is
> the column that i am referencing as Documenttype for sp_fulltext_column
> I have changed the contents of the documentdoctype2 column to be both
> 'word' & 'msword' to be safe and run the recatalog command (Originally
these
> were mime types 'application/msword' or similar. In both cases this was
the
> result;
> The application log shows the following result : Event ID 2001
> "One or more documents stored in image columns with extension 'word' did
not
> get full-text indexed because loading the filter failed with error '0x1'.
> Note: These documents will not be passed to MSSearch for indexing, and
> therefore this failure will not be reflected in the end of crawl summary
> statistics."
> Though strangely it went on to say
> "The end of crawl for project <SQLServer SQL0003400005> has been detected.
> The Gatherer successfully processed 50 documents totaling 0K. It failed to
> filter 0 documents. 0 URLs could not be reached or were denied access."
> Finally there is an event for Master merge has completed on the index.
> Thanks for your help
sql
interested in FTS'ing is all image datatypes. The data in the colum is
populated via asp.net; and we store the document type as a mime type e.g.
'application/msword' as it makes it easy to get data back out from the
database.
My table has teh following columns
DocumentID UniqueIdentifier,
DocumentTypeRef int
DocumentSubject varchar(100)
DocumentValue image
DocumentDocType varchar(50)
DocumentDocType sysname -- added to use FTS would rather use vc(50) field
DocumentByteSize int
DocumentFileName varchar(100)
DocumentCreated datetime
MailMergeTemplateID uniqueidentifier
ToDoID uniqueidentifier
DocumentUploadWaiting bit
The table has 50 rows in it; in my test system. mostly word documents. I
have run teh following script to setup fts (and tried the wizard without
success)
use Activities_FTS
exec sp_fulltext_database 'enable'
exec sp_fulltext_catalog 'FTS', 'create', 'D:\\sqldata'
exec sp_fulltext_table 'tblDocuments', 'create', 'FTS', 'PK_tblDocuments'
exec sp_fulltext_column @.tabname = tblDocuments,
@.colname = DocumentValue,
@.action = 'dro',
@.Type_Colname = DocumentDocType2
exec sp_fulltext_table 'tblDocuments', 'activate'
exec sp_fulltext_table 'tblDocuments', 'start_full'
select FULLTEXTCATALOGPROPERTY('FTS', 'Populatestatus')
select * from freetexttable(tblDocuments,DocumentValue , '%%') order by
[rank] desc
This is based around the steps which worked successfully from the tutorial
on this page
http://msdn.microsoft.com/library/de...extsearch.asp.
When i run the index population check it does run for about 30 seconds; but i
cannot find out why it is not indexing the documents; or is there another
query i can use to test this. (My FTS catalog never grows above 1mb)
If you have any questions please do not hestitate to reply as i am slightly
lost now
Many thanks
Chris
Can you check what your gatherer logs report? I'm not sure if storing them
as a mime type is the best way to go.
To check your gatherer log output do this:
go to c:\program files\common files\system\mssearch\bin and copy gthrlog.vbs
to %windir% then go to your catalog location for your catalog (this should
be c:\program files\microsoft sql server\mssql\ftdata\sqlserver\gatherlogs.
Now you want to id your gatherlog for your catalog. the totally brainless
what to do this is to issue another incremental population for your catalog
and then do a dir /od. Your catalog
gather logs will then appear last in the list. The syntax is
SQLXXXXXYYYYY.?.gthr where the X is your db_id (you can determine this by
doing
this select db_id('database_name') and the YYYYYY is your catalog id, which
you can determine by doing to your database in isqlw and doing this select
id, name from syscatalogs where name ='catalog_name'.
Then with this information what you do is this (from a command prompt)
cscript gthrlog.vbs SQL0017900635.6.gthr
You will get a lot of output, but it will tell you on which row(s) the
indexer had a problem. Then what you have to do is have a look at that row
and see if you can figure out what the problem is with the data in that row
"Chris Hoare" <ChrisHoare@.discussions.microsoft.com> wrote in message
news:AB36FF24-EE13-4C7A-B8E5-E110588526CC@.microsoft.com...
>I am trying to setup FTS on a table in my database; the column I am
> interested in FTS'ing is all image datatypes. The data in the colum is
> populated via asp.net; and we store the document type as a mime type e.g.
> 'application/msword' as it makes it easy to get data back out from the
> database.
> My table has teh following columns
> DocumentID UniqueIdentifier,
> DocumentTypeRef int
> DocumentSubject varchar(100)
> DocumentValue image
> DocumentDocType varchar(50)
> DocumentDocType sysname -- added to use FTS would rather use vc(50) field
> DocumentByteSize int
> DocumentFileName varchar(100)
> DocumentCreated datetime
> MailMergeTemplateID uniqueidentifier
> ToDoID uniqueidentifier
> DocumentUploadWaiting bit
> The table has 50 rows in it; in my test system. mostly word documents. I
> have run teh following script to setup fts (and tried the wizard without
> success)
> use Activities_FTS
> exec sp_fulltext_database 'enable'
> exec sp_fulltext_catalog 'FTS', 'create', 'D:\\sqldata'
> exec sp_fulltext_table 'tblDocuments', 'create', 'FTS', 'PK_tblDocuments'
> exec sp_fulltext_column @.tabname = tblDocuments,
> @.colname = DocumentValue,
> @.action = 'dro',
> @.Type_Colname = DocumentDocType2
> exec sp_fulltext_table 'tblDocuments', 'activate'
> exec sp_fulltext_table 'tblDocuments', 'start_full'
> --
> select FULLTEXTCATALOGPROPERTY('FTS', 'Populatestatus')
> select * from freetexttable(tblDocuments,DocumentValue , '%%') order by
> [rank] desc
>
> This is based around the steps which worked successfully from the tutorial
> on this page
> http://msdn.microsoft.com/library/de...extsearch.asp.
> When i run the index population check it does run for about 30 seconds;
> but i
> cannot find out why it is not indexing the documents; or is there another
> query i can use to test this. (My FTS catalog never grows above 1mb)
> If you have any questions please do not hestitate to reply as i am
> slightly
> lost now
> Many thanks
> Chris
|||Chris,
I'm assuming that you're using SQL Server 2000 as this feature is new with
SQL Server 2000, but what OS platform is it installed on? Could you post the
full output of -- SELECT @.@.version -- as this is very helpful in
troubleshooting SQL FTS issues!
You should use the datatype of sysname (or char(3) or varchar(4)) with your
column "DocumentDocType". Could you also provide details on what values you
have populated in this column? Furthermore, how exactly did you import the
MS Word documents and what is the language of the text stored in the MS Word
documents?
Additionally, the following FREETEXTTABLE query will not return any results
as the "%" (percent) symbols are ignored by the MSSearch engine (depending
upon the OS platform):
select * from freetexttable(tblDocuments,DocumentValue , '%%') order by
[rank] desc
Instead you should use the following:
declare @.searchTerm varchar(1024)
set @.searchTerm = 'some_valid_search_word_here'
select * from freetexttable(tblDocuments, DocumentValue, @.searchTerm) order
by [rank] desc
Finally, you should review your server's Application event log for any
"Microsoft Search" or MssCi source events (warnings, informational and
errors) to determine why the initial FT Indexing is failing as this is the
only place such errors or warnings are written. Have you or anyone else
changed the SQL Server (MSSQLServer) service account &/or password via
Win2K's Component Services vs. changing this in the Enterprise Manager? If
so, then you should also review KB article 277549 (Q277549) PRB: Unable to
Build Full-Text Catalog After You Modify MSSQLServer Logon Account Through
[NT4.0) Control Panel [or Win2K Component Services] at:
http://support.microsoft.com/default...B;EN-US;277549
Regards,
John
"Chris Hoare" <ChrisHoare@.discussions.microsoft.com> wrote in message
news:AB36FF24-EE13-4C7A-B8E5-E110588526CC@.microsoft.com...
> I am trying to setup FTS on a table in my database; the column I am
> interested in FTS'ing is all image datatypes. The data in the colum is
> populated via asp.net; and we store the document type as a mime type e.g.
> 'application/msword' as it makes it easy to get data back out from the
> database.
> My table has teh following columns
> DocumentID UniqueIdentifier,
> DocumentTypeRef int
> DocumentSubject varchar(100)
> DocumentValue image
> DocumentDocType varchar(50)
> DocumentDocType sysname -- added to use FTS would rather use vc(50) field
> DocumentByteSize int
> DocumentFileName varchar(100)
> DocumentCreated datetime
> MailMergeTemplateID uniqueidentifier
> ToDoID uniqueidentifier
> DocumentUploadWaiting bit
> The table has 50 rows in it; in my test system. mostly word documents. I
> have run teh following script to setup fts (and tried the wizard without
> success)
> use Activities_FTS
> exec sp_fulltext_database 'enable'
> exec sp_fulltext_catalog 'FTS', 'create', 'D:\\sqldata'
> exec sp_fulltext_table 'tblDocuments', 'create', 'FTS', 'PK_tblDocuments'
> exec sp_fulltext_column @.tabname = tblDocuments,
> @.colname = DocumentValue,
> @.action = 'dro',
> @.Type_Colname = DocumentDocType2
> exec sp_fulltext_table 'tblDocuments', 'activate'
> exec sp_fulltext_table 'tblDocuments', 'start_full'
> --
> select FULLTEXTCATALOGPROPERTY('FTS', 'Populatestatus')
> select * from freetexttable(tblDocuments,DocumentValue , '%%') order by
> [rank] desc
>
> This is based around the steps which worked successfully from the tutorial
> on this page
>
http://msdn.microsoft.com/library/de...extsearch.asp.
> When i run the index population check it does run for about 30 seconds;
but i
> cannot find out why it is not indexing the documents; or is there another
> query i can use to test this. (My FTS catalog never grows above 1mb)
> If you have any questions please do not hestitate to reply as i am
slightly
> lost now
> Many thanks
> Chris
|||OK,
SQL Server details
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
The documents were all imported using request.file object in asp.net; and
were streamed into a stored procuedre. ASP.net can open these files again
(which is why we are storing the mime type.)
The column I referenced as DocumentDocType2 is typed as 'sysname.' this is
the column that i am referencing as Documenttype for sp_fulltext_column
I have changed the contents of the documentdoctype2 column to be both
'word' & 'msword' to be safe and run the recatalog command (Originally these
were mime types 'application/msword' or similar. In both cases this was the
result;
The application log shows the following result : Event ID 2001
"One or more documents stored in image columns with extension 'word' did not
get full-text indexed because loading the filter failed with error '0x1'.
Note: These documents will not be passed to MSSearch for indexing, and
therefore this failure will not be reflected in the end of crawl summary
statistics."
Though strangely it went on to say
"The end of crawl for project <SQLServer SQL0003400005> has been detected.
The Gatherer successfully processed 50 documents totaling 0K. It failed to
filter 0 documents. 0 URLs could not be reached or were denied access."
Finally there is an event for Master merge has completed on the index.
Thanks for your help
|||You're welcome, Chris,
Thanks for the version info as that does help, especially the fact that you
have SQL Server 2000 SP3 on Win2003! You may want to review all of the below
KB articles, and especially 326502 (Q326502) as it provides coding example
on how to use ASP.NET to load MS Word files via:
Response.AddHeader("Content-Disposition", "attachment;filename=blob.doc")
Response.ContentType = "application/msword"
The most likely cause of the Full Population failing is that only valid MS
Word (doc or .doc) files are supported when properly imported into a SQL
Server 2000 table's column defined with the IMAGE (or BLOB) datatype.
Additionally, by populating the column documentdoctype2 column with both
'word' & 'msword' will not succeed as the MSSearch service does not
recognize these as valid file extensions. See SQL Server 2000 BOL title
"Filtering Supported File Types" - specifically, "Microsoft SQL ServerT
2000 includes filters for these file extensions: .doc, .xls, .ppt, .txt, and
..htm".
I grant you that the second informational MSSearch message text was somewhat
mis-leading, but the key error "One or more documents stored in image
columns with extension 'word' did not get full-text indexed because loading
the filter failed with error '0x1'" indicates the FT Population failure
because the wrong value was placed in the documentdoctype2 column.
258038 (Q258038) HOWTO: Access and Modify SQL Server BLOB Data by Using the
ADO Stream Object
http://support.microsoft.com/?kbid=258038
309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#
http://support.microsoft.com/default...b;EN-US;309158
308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with
VB.NET
http://support.microsoft.com/default...b;EN-US;308042
326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through
ASP.NET
http://support.microsoft.com/?id=326502
Regards,
John
"Chris Hoare" <ChrisHoare@.discussions.microsoft.com> wrote in message
news:066C2D40-1BF5-4B26-821E-69F71F39844C@.microsoft.com...
> OK,
> SQL Server details
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> The documents were all imported using request.file object in asp.net; and
> were streamed into a stored procuedre. ASP.net can open these files again
> (which is why we are storing the mime type.)
> The column I referenced as DocumentDocType2 is typed as 'sysname.' this is
> the column that i am referencing as Documenttype for sp_fulltext_column
> I have changed the contents of the documentdoctype2 column to be both
> 'word' & 'msword' to be safe and run the recatalog command (Originally
these
> were mime types 'application/msword' or similar. In both cases this was
the
> result;
> The application log shows the following result : Event ID 2001
> "One or more documents stored in image columns with extension 'word' did
not
> get full-text indexed because loading the filter failed with error '0x1'.
> Note: These documents will not be passed to MSSearch for indexing, and
> therefore this failure will not be reflected in the end of crawl summary
> statistics."
> Though strangely it went on to say
> "The end of crawl for project <SQLServer SQL0003400005> has been detected.
> The Gatherer successfully processed 50 documents totaling 0K. It failed to
> filter 0 documents. 0 URLs could not be reached or were denied access."
> Finally there is an event for Master merge has completed on the index.
> Thanks for your help
sql
2012年3月26日星期一
Full Text Search Performance
Hello,
I have full text search enabled on two large tables, the first one has 3
million records (FT index column length is around 512-1025 char), and the
other one has around 1.6 records (FT index column length is around 50-120
char)
The simplest query that uses the smiplest form of FT on the first table FT
index search needs 23-30 seconds to complete and from 10-15 seconds on the
seconds table as it the indexed column is smaller.
SQL server is deployed on 4 P servers with 8 GB of memory and attached to a
very powerful SAN system. Memory usage on the server is not very high so I
believe there is enough memory for the mssearch service to use.
I just wonder if this is the normal throughput of the Full Text search of
SQL Server. If not, I will appreciate any tips and hints that might be the
reason of the system.
Ali Salem
That depends. SQL FTS performance is most sensitive to the number of rows
you are returning. You should limit your results set as much as possible - I
think you will find that the practical limit for most applications is around
100-200 rows.
Limit it by using the the top_n_by_rank operator in ContainsTable or
FreeTextTable, ie
USE Northwind
GO
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'test',200
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> Hello,
> I have full text search enabled on two large tables, the first one has 3
> million records (FT index column length is around 512-1025 char), and the
> other one has around 1.6 records (FT index column length is around 50-120
> char)
> The simplest query that uses the smiplest form of FT on the first table FT
> index search needs 23-30 seconds to complete and from 10-15 seconds on the
> seconds table as it the indexed column is smaller.
> SQL server is deployed on 4 P servers with 8 GB of memory and attached to
a
> very powerful SAN system. Memory usage on the server is not very high so I
> believe there is enough memory for the mssearch service to use.
> I just wonder if this is the normal throughput of the Full Text search of
> SQL Server. If not, I will appreciate any tips and hints that might be the
> reason of the system.
> --
> Ali Salem
|||thanks for your reply.
I cannot limit the search in this way, as there are other parameters in my
query that can affect the returned result set and that are ourside the FTS.
I am asking for any figures about FTS performance? What should I expect from
it? Is this behavior am getting normal for the data size or, I am having
something wrong
"Hilary Cotter" wrote:
> That depends. SQL FTS performance is most sensitive to the number of rows
> you are returning. You should limit your results set as much as possible - I
> think you will find that the practical limit for most applications is around
> 100-200 rows.
> Limit it by using the the top_n_by_rank operator in ContainsTable or
> FreeTextTable, ie
> USE Northwind
> GO
> SELECT FT_TBL.Description,
> FT_TBL.CategoryName,
> KEY_TBL.RANK
> FROM Categories AS FT_TBL INNER JOIN
> CONTAINSTABLE (Categories, Description,
> 'test',200
> ) AS KEY_TBL
> ON FT_TBL.CategoryID = KEY_TBL.[KEY]
> ORDER BY KEY_TBL.RANK DESC
> "Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
> news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> a
>
>
|||No, this is highly abnormal. Performance should be sub second. However I
need to know what your queries look like, ie how many search arguments, how
many rows are returned, and what language you are querying in.
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:AECBD305-A9FD-4759-B240-B4D970B32BEA@.microsoft.com...
> thanks for your reply.
> I cannot limit the search in this way, as there are other parameters in my
> query that can affect the returned result set and that are ourside the
FTS.
> I am asking for any figures about FTS performance? What should I expect
from[vbcol=seagreen]
> it? Is this behavior am getting normal for the data size or, I am having
> something wrong
> "Hilary Cotter" wrote:
rows[vbcol=seagreen]
possible - I[vbcol=seagreen]
around[vbcol=seagreen]
3[vbcol=seagreen]
the[vbcol=seagreen]
50-120[vbcol=seagreen]
table FT[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
so I[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
|||Ali Salem,
Could you provide the full output of the following SQL script as it is very
helpful in troubleshooting SQL FTS issue and understanding your environment
and issues!
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
SELECT FULLTEXTSERVICEPROPERTY('ResourceUsage')
go
Additionally, where is your FT Catalog located as by default it is created
under the \FTDATA directory where you have SQL Server installed as well as
where your FT-enabled database files (*.mdf, *.ndf, * *.ldf) are located
relative to the location of your FT Catalog folder? If your 'ResourceUsage
is set to 3, you should increase it to 5 (dedicated) via sp_fulltext_service
'resource_usage' <value>, where <value> is 5.
Thanks,
John
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> Hello,
> I have full text search enabled on two large tables, the first one has 3
> million records (FT index column length is around 512-1025 char), and the
> other one has around 1.6 records (FT index column length is around 50-120
> char)
> The simplest query that uses the smiplest form of FT on the first table FT
> index search needs 23-30 seconds to complete and from 10-15 seconds on the
> seconds table as it the indexed column is smaller.
> SQL server is deployed on 4 P servers with 8 GB of memory and attached to
a
> very powerful SAN system. Memory usage on the server is not very high so I
> believe there is enough memory for the mssearch service to use.
> I just wonder if this is the normal throughput of the Full Text search of
> SQL Server. If not, I will appreciate any tips and hints that might be the
> reason of the system.
> --
> Ali Salem
|||Thanks.
- We are using enlgihs language.
- Returned number of rows should be very large (thousands, in some cases it
can be 100, 000). this from the Full-Text Seach, other SQL filters will be
applied as well, but from mssearch this is what will be returned. Please note
that the table size is around 3million records.
- The smiplest query such as SELECT * FROM TABLE1 WHERE CONTAINS(COL1,
'ABCD') need around 30 seconds complete.
I do appreciate any help.
thank you
"Hilary Cotter" wrote:
> No, this is highly abnormal. Performance should be sub second. However I
> need to know what your queries look like, ie how many search arguments, how
> many rows are returned, and what language you are querying in.
>
> "Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
> news:AECBD305-A9FD-4759-B240-B4D970B32BEA@.microsoft.com...
> FTS.
> from
> rows
> possible - I
> around
> 3
> the
> 50-120
> table FT
> the
> to
> so I
> of
> the
>
>
|||Below are the result of the script you requested me to run it:
My FT files are stored on SAN storage. So IO should be performing well.
Regarding the ResourceUsage, it is 3. Will not raising it to 5 harm the sql
server performance! I dont want to make the full text search fast by slowing
down SQL Server itself.
I have splitted the result into two posts so that I can post it here.
Thank you for you help
------
us_english
(1 row(s) affected)
------
------
Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
(1 row(s) affected)
name minimum maximum config_value
run_value
-- -- -- --
default full-text language 0 2147483647 1033 1033
ftcatid NAME
PATH
STATUS NUMBER_FULLTEXT_TABLES
------
------
------
-- -- --
5 ICSubject
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
6 Subject
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
7 Names
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
(3 row(s) affected)
TABLE_OWNER
TABLE_NAME
FULLTEXT_KEY_INDEX_NAME
FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME
------
------
------
-- -- --
----
dbo
IC_SUBJECTS
PK_IC_SUBJECTS
1 1 ICSubject
dbo
MAIN
PK_INOUT_MAIN
1 1 Subject
dbo
PERSONS
PK_PERSONS
1 1 Names
(3 row(s) affected)
TABLE_OWNER
TABLE_ID TABLE_NAME
FULLTEXT_COLUMN_NAME
FULLTEXT_COLID FULLTEXT_BLOBTP_COLNAME
FULLTEXT_BLOBTP_COLID
FULLTEXT_LANGUAGE
------
-- --
-----
-----
-- --
--- --
dbo
2069582411 IC_SUBJECTS
ITEM_DESCRIPTION_AR
3 NULL
NULL 0
dbo
274100017 MAIN
SUBJECT
42 NULL
NULL 0
dbo
466100701 PERSONS
FIRSTNAME_AR
4 NULL
NULL 0
dbo
466100701 PERSONS
LASTNAME_AR
8 NULL
NULL 0
dbo
466100701 PERSONS
FULL_NAME
9 NULL
NULL 0
dbo
466100701 PERSONS
FATHERNAME_AR
12 NULL
NULL 0
dbo
466100701 PERSONS
GRANDFATHERNAME_AR
14 NULL
NULL 0
(7 row(s) affected)
Name
Owner
Type Created_datetime
------
------
-- --
MAIN
dbo
user table 2004-10-14 18:14:31.457
|||The Rest of the script result, one more post is required
Column_name
Type
Computed Length Prec Scale Nullable
TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- -- --
-- --
-- --
DOCID
int
no 4 10 0 no
(n/a)
(n/a) NULL
DESCRIPTION
varchar
no 255 yes
no
no Arabic_CI_AS
DOCDATE
datetime
no 8 yes
(n/a)
(n/a) NULL
RDOCDATE
datetime
no 8 yes
(n/a)
(n/a) NULL
EXT_PARTY_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
EXT_PARTY_REP_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBCAT_ID
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
STATUS_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
CONFID_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
REFERENCE_NO
varchar
no 50 yes
no
no Arabic_CI_AS
FORWARD_TO
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
FORWARD_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
TO_REMIND
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
REMIND_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
REMARKS
varchar
no 1024 yes
no
no Arabic_CI_AS
REVISION_NO
varchar
no 50 yes
no
no Arabic_CI_AS
TRAN_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
USER_ID
varchar
no 32 yes
no
no Arabic_CI_AS
SYSTEM_ID
varchar
no 50 yes
no
no Arabic_CI_AS
IP
varchar
no 50 yes
no
no Arabic_CI_AS
CHECK_SUM
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
SUBMIT_TYPE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBJECT_TYPE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBJECT
nvarchar
no 8000 yes
(n/a)
(n/a) Arabic_CI_AS
PRIORITY_NO
int
no 4 10 0 yes
(n/a)
(n/a) NULL
PROCESS_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
REMIND_TIME
varchar
no 20 yes
no
no Arabic_CI_AS
WAITING_FOR_REPLY
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SITE_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
DELIVERY_METHOD_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
GROUPID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
LINK_STATUS
int
no 4 10 0 yes
(n/a)
(n/a) NULL
INITIAL_PROCEDURE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
CREATOR_USER_ID
varchar
no 32 yes
no
no Arabic_CI_AS
IS_SPLITTED
bit
no 1 yes
(n/a)
(n/a) NULL
CHAR_FIELD2
varchar
no 500 yes
no
no Arabic_CI_AS
CHAR_FIELD5_AR
nvarchar
no 100 yes
(n/a)
(n/a) Arabic_CI_AS
NUM_FIELD1
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
CHAR_FIELD66_AR
nvarchar
no 2000 yes
(n/a)
(n/a) Arabic_CI_AS
NUM_FIELD6
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
FLOAT_FIELD1
int
no 4 10 0 yes
(n/a)
(n/a) NULL
FLOAT_FIELD2
int
no 4 10 0 yes
(n/a)
(n/a) NULL
NUM_FIELD2
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD3
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD4
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD5
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
DATE_FIELD1
datetime
no 8 yes
(n/a)
(n/a) NULL
DATE_FIELD2
datetime
no 8 yes
(n/a)
(n/a) NULL
CHAR_FIELD1
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD3
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD4
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD5
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD1_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
CHAR_FIELD2_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
CHAR_FIELD4_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
Identity
Seed Increment
Not For Replication
------
-- ---
--- --
No identity column defined.
NULL NULL
NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
SECONDARY2
|||Last post of the script result.
index_name
index_description
index_keys
------
------
----- --
------
IX_Index1
nonclustered located on SECONDARY2
DOCID, STATUS_ID, CABID, YEAR,
CONFID_ID, DELIVERY_PLACE, PRIORITY_NO,
IX_Index2
nonclustered located on SECONDARY4
INOUTDOCNO, CABID, DOC_ORIGN_SOURCE,
YEAR, DOCDATE, OUTBOUND_REQUEST
IX_Index3
nonclustered located on SECONDARY2
GROUPID
IX_Index4
nonclustered located on SECONDARY4
YEAR, EXT_DOCNO, RDOCDATE_HJ,
EXT_PARTY_ID, DIWAN_REP_ID, EXT_PARTY_REP_ID
IX_Index5
nonclustered located on SECONDARY2
REQUEST_NO
IX_Index6
nonclustered located on SECONDARY4
STATUS_ID, CABID
PK_MAIN
clustered, unique, primary key located on SECONDARY2
DOCNO
constraint_type
constraint_name
delete_action update_action status_enabled
status_for_replication constraint_keys
------
-- -- -- --
-- --
------
-----
DEFAULT on column OUTBOUND_REQUEST
DF_MAIN_OUTBOUND_REQUEST
(n/a) (n/a) (n/a) (n/a)
(0)
FOREIGN KEY
FK_MAIN_EMPLOYEES
No Action No Action Enabled
Not_For_Replication USER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_EMPLOYEES1
No Action No Action Enabled
Not_For_Replication WRITER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_EMPLOYEES2
No Action No Action Enabled
Not_For_Replication CREATOR_USER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_LO_CONFIDENTIAL
No Action No Action Enabled
Not_For_Replication CONFID_ID
REFERENCES dbo.LO_CONFIDENTIAL (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_DOC_SOURCE_TYPE
No Action No Action Enabled
Not_For_Replication DOC_ORIGN_SOURCE
REFERENCES dbo.LO_DOC_SOURCE_TYPE
(TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS
No Action No Action Enabled
Not_For_Replication DELIVERY_METHOD_ID
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS1
No Action No Action Enabled
Not_For_Replication LINK_STATUS
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS4
No Action No Action Enabled
Not_For_Replication RECEIVE_METHOD_ID
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_PRIORITY
No Action No Action Enabled
Not_For_Replication PRIORITY_NO
REFERENCES dbo.LO_PRIORITY (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION
No Action No Action Enabled
Not_For_Replication EXT_PARTY_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION1
No Action No Action Enabled
Not_For_Replication EXT_PARTY_REP_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION2
No Action No Action Enabled
Not_For_Replication CITY_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SIGNATURE_TYPE
No Action No Action Enabled
Not_For_Replication DIWAN_REP_ID
REFERENCES dbo.LO_SIGNATURE_TYPE (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SITE
No Action No Action Enabled
Not_For_Replication DELIVERY_PLACE
REFERENCES dbo.LO_SITE (SITE_ID)
FOREIGN KEY
FK_MAIN_LO_STATUS
No Action No Action Enabled
Not_For_Replication STATUS_ID
REFERENCES dbo.LO_STATUS (TAB_ID)
FOREIGN KEY
FK_MAIN_MAIN_GROUP_HDR
No Action No Action Enabled
Not_For_Replication GROUPID
REFERENCES dbo.MAIN_GROUP_HDR (
GROUPID)
FOREIGN KEY
FK_MAIN_PROCESSES
No Action No Action Enabled
Not_For_Replication INITIAL_PROCEDURE
REFERENCES dbo.PROCESSES (
PROCESS_ID)
PRIMARY KEY (clustered)
PK_MAIN
(n/a) (n/a) (n/a) (n/a)
DOCNO
Table is referenced by foreign key
------
------
dbo.TASKS: FK_TASKS_MAIN
dbo.ASKST_CHAIN: FK_TASKS_CHAIN_MAIN
dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN
dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN1
dbo.ATTACHMENTS_MOV: FK_ATTACH_MOV_MAIN
dbo.COPY: FK_COPY_MAIN
dbo.INQUIRY: FK_INQUIRY_MAIN
dbo.LINKS_F: FK_LINKS_F_MAIN
dbo.CLASS: FK_CLASS_MAIN
dbo.XLASS: FK_CLASS_MAIN
dbo.REQUEST: FK_REQUEST_MAIN
dbo.REQUEST_DTL: FK_REQUEST_DTL_MAIN
dbo.MAIN_FOLLOW: FK_MAIN_FOLLOW_MAIN
dbo.MAIN_GROUP_DTL: FK_MAIN_GROUP_DTL_MAIN
dbo.MAIN_TRACK: FK_MAIN_TRACK_MAIN
dbo.PERSONS: FK_PERSONS_MAIN
dbo.RESERVED: FK_RESERVED_MAIN
dbo.SAVE_TRACK: FK_SAVE_TRACK_MAIN
Table is referenced by views
------
3
(1 row(s) affected)
|||Ali,
First of all, thank you for providing this info as it is most helpful! You
are using SQL Server 2000 SP2 on Windows 2000 Server SP3 and your default
language is US_English (1033). However, all of your char, varchar and
nvarchar columns using the Arabic_CI_AS collation with the FT-enabled column
"Language for Word Breaker" set to Neutral. Can I assume that these column
contain Arabic text?
Yes, I can understand why you want the Resource_Usage level to remain at 3,
but you might try setting to 5, as you can always set it lower if it is
affecting your other SQL Server processing. Although, this bump in the
MSSearch resource usage might not be significant. Note, it affect how much
memory MSSearch can use up to a max if 512MB RAM, if available as well as
the number of concurrent connections allowed.
What is the drive letter for your SAN storage? Could you also run the
following SQL query and post it's results?
sp_helpdb <full_text_enabled_database_name>
I'm not sure if the use of Arabic text is causing the poor query performance
(even with the Neutral wordbreaker), but language is a consideration with
FTS query performance issues.
Thanks again,
John
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:D20604B8-21E5-496F-A957-1F1CF8949716@.microsoft.com...
> Last post of the script result.
>
>
> index_name
>
> index_description
>
> index_keys
>
>
>
> ----
----
> --
> ----
> ----
-- --
> ----
----
> ----
--
> IX_Index1
>
> nonclustered located on SECONDARY2
>
> DOCID, STATUS_ID, CABID, YEAR,
> CONFID_ID, DELIVERY_PLACE, PRIORITY_NO,
> IX_Index2
>
> nonclustered located on SECONDARY4
>
> INOUTDOCNO, CABID, DOC_ORIGN_SOURCE,
> YEAR, DOCDATE, OUTBOUND_REQUEST
> IX_Index3
>
> nonclustered located on SECONDARY2
>
> GROUPID
> IX_Index4
>
> nonclustered located on SECONDARY4
>
> YEAR, EXT_DOCNO, RDOCDATE_HJ,
> EXT_PARTY_ID, DIWAN_REP_ID, EXT_PARTY_REP_ID
> IX_Index5
>
> nonclustered located on SECONDARY2
>
> REQUEST_NO
> IX_Index6
>
> nonclustered located on SECONDARY4
>
> STATUS_ID, CABID
> PK_MAIN
>
> clustered, unique, primary key located on SECONDARY2
>
> DOCNO
>
> constraint_type
>
> constraint_name
>
> delete_action update_action status_enabled
> status_for_replication constraint_keys
>
>
>
> ----
----
> --
> ----
--
> -- -- -- --
> -- --
> ----
----
> ----
--
> DEFAULT on column OUTBOUND_REQUEST
>
> DF_MAIN_OUTBOUND_REQUEST
>
> (n/a) (n/a) (n/a) (n/a)
> (0)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES
>
> No Action No Action Enabled
> Not_For_Replication USER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES1
>
> No Action No Action Enabled
> Not_For_Replication WRITER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES2
>
> No Action No Action Enabled
> Not_For_Replication CREATOR_USER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_LO_CONFIDENTIAL
>
> No Action No Action Enabled
> Not_For_Replication CONFID_ID
>
>
>
> REFERENCES dbo.LO_CONFIDENTIAL (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_DOC_SOURCE_TYPE
>
> No Action No Action Enabled
> Not_For_Replication DOC_ORIGN_SOURCE
>
>
>
> REFERENCES dbo.LO_DOC_SOURCE_TYPE
> (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS
>
> No Action No Action Enabled
> Not_For_Replication DELIVERY_METHOD_ID
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS1
>
> No Action No Action Enabled
> Not_For_Replication LINK_STATUS
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS4
>
> No Action No Action Enabled
> Not_For_Replication RECEIVE_METHOD_ID
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_PRIORITY
>
> No Action No Action Enabled
> Not_For_Replication PRIORITY_NO
>
>
>
> REFERENCES dbo.LO_PRIORITY (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION
>
> No Action No Action Enabled
> Not_For_Replication EXT_PARTY_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION1
>
> No Action No Action Enabled
> Not_For_Replication EXT_PARTY_REP_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION2
>
> No Action No Action Enabled
> Not_For_Replication CITY_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SIGNATURE_TYPE
>
> No Action No Action Enabled
> Not_For_Replication DIWAN_REP_ID
>
>
>
> REFERENCES dbo.LO_SIGNATURE_TYPE (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SITE
>
> No Action No Action Enabled
> Not_For_Replication DELIVERY_PLACE
>
>
>
> REFERENCES dbo.LO_SITE (SITE_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_STATUS
>
> No Action No Action Enabled
> Not_For_Replication STATUS_ID
>
>
>
> REFERENCES dbo.LO_STATUS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_MAIN_GROUP_HDR
>
> No Action No Action Enabled
> Not_For_Replication GROUPID
>
>
>
> REFERENCES dbo.MAIN_GROUP_HDR (
> GROUPID)
> FOREIGN KEY
>
> FK_MAIN_PROCESSES
>
> No Action No Action Enabled
> Not_For_Replication INITIAL_PROCEDURE
>
>
>
> REFERENCES dbo.PROCESSES (
> PROCESS_ID)
> PRIMARY KEY (clustered)
>
> PK_MAIN
>
> (n/a) (n/a) (n/a) (n/a)
> DOCNO
>
> Table is referenced by foreign key
>
>
>
> ----
----
> ----
----
> --
> dbo.TASKS: FK_TASKS_MAIN
> dbo.ASKST_CHAIN: FK_TASKS_CHAIN_MAIN
> dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN
> dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN1
> dbo.ATTACHMENTS_MOV: FK_ATTACH_MOV_MAIN
> dbo.COPY: FK_COPY_MAIN
> dbo.INQUIRY: FK_INQUIRY_MAIN
> dbo.LINKS_F: FK_LINKS_F_MAIN
> dbo.CLASS: FK_CLASS_MAIN
> dbo.XLASS: FK_CLASS_MAIN
> dbo.REQUEST: FK_REQUEST_MAIN
> dbo.REQUEST_DTL: FK_REQUEST_DTL_MAIN
> dbo.MAIN_FOLLOW: FK_MAIN_FOLLOW_MAIN
> dbo.MAIN_GROUP_DTL: FK_MAIN_GROUP_DTL_MAIN
> dbo.MAIN_TRACK: FK_MAIN_TRACK_MAIN
> dbo.PERSONS: FK_PERSONS_MAIN
> dbo.RESERVED: FK_RESERVED_MAIN
> dbo.SAVE_TRACK: FK_SAVE_TRACK_MAIN
> Table is referenced by views
>
> ----
----
> --
>
> --
> 3
> (1 row(s) affected)
>
sql
I have full text search enabled on two large tables, the first one has 3
million records (FT index column length is around 512-1025 char), and the
other one has around 1.6 records (FT index column length is around 50-120
char)
The simplest query that uses the smiplest form of FT on the first table FT
index search needs 23-30 seconds to complete and from 10-15 seconds on the
seconds table as it the indexed column is smaller.
SQL server is deployed on 4 P servers with 8 GB of memory and attached to a
very powerful SAN system. Memory usage on the server is not very high so I
believe there is enough memory for the mssearch service to use.
I just wonder if this is the normal throughput of the Full Text search of
SQL Server. If not, I will appreciate any tips and hints that might be the
reason of the system.
Ali Salem
That depends. SQL FTS performance is most sensitive to the number of rows
you are returning. You should limit your results set as much as possible - I
think you will find that the practical limit for most applications is around
100-200 rows.
Limit it by using the the top_n_by_rank operator in ContainsTable or
FreeTextTable, ie
USE Northwind
GO
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'test',200
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> Hello,
> I have full text search enabled on two large tables, the first one has 3
> million records (FT index column length is around 512-1025 char), and the
> other one has around 1.6 records (FT index column length is around 50-120
> char)
> The simplest query that uses the smiplest form of FT on the first table FT
> index search needs 23-30 seconds to complete and from 10-15 seconds on the
> seconds table as it the indexed column is smaller.
> SQL server is deployed on 4 P servers with 8 GB of memory and attached to
a
> very powerful SAN system. Memory usage on the server is not very high so I
> believe there is enough memory for the mssearch service to use.
> I just wonder if this is the normal throughput of the Full Text search of
> SQL Server. If not, I will appreciate any tips and hints that might be the
> reason of the system.
> --
> Ali Salem
|||thanks for your reply.
I cannot limit the search in this way, as there are other parameters in my
query that can affect the returned result set and that are ourside the FTS.
I am asking for any figures about FTS performance? What should I expect from
it? Is this behavior am getting normal for the data size or, I am having
something wrong
"Hilary Cotter" wrote:
> That depends. SQL FTS performance is most sensitive to the number of rows
> you are returning. You should limit your results set as much as possible - I
> think you will find that the practical limit for most applications is around
> 100-200 rows.
> Limit it by using the the top_n_by_rank operator in ContainsTable or
> FreeTextTable, ie
> USE Northwind
> GO
> SELECT FT_TBL.Description,
> FT_TBL.CategoryName,
> KEY_TBL.RANK
> FROM Categories AS FT_TBL INNER JOIN
> CONTAINSTABLE (Categories, Description,
> 'test',200
> ) AS KEY_TBL
> ON FT_TBL.CategoryID = KEY_TBL.[KEY]
> ORDER BY KEY_TBL.RANK DESC
> "Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
> news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> a
>
>
|||No, this is highly abnormal. Performance should be sub second. However I
need to know what your queries look like, ie how many search arguments, how
many rows are returned, and what language you are querying in.
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:AECBD305-A9FD-4759-B240-B4D970B32BEA@.microsoft.com...
> thanks for your reply.
> I cannot limit the search in this way, as there are other parameters in my
> query that can affect the returned result set and that are ourside the
FTS.
> I am asking for any figures about FTS performance? What should I expect
from[vbcol=seagreen]
> it? Is this behavior am getting normal for the data size or, I am having
> something wrong
> "Hilary Cotter" wrote:
rows[vbcol=seagreen]
possible - I[vbcol=seagreen]
around[vbcol=seagreen]
3[vbcol=seagreen]
the[vbcol=seagreen]
50-120[vbcol=seagreen]
table FT[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
so I[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
|||Ali Salem,
Could you provide the full output of the following SQL script as it is very
helpful in troubleshooting SQL FTS issue and understanding your environment
and issues!
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
SELECT FULLTEXTSERVICEPROPERTY('ResourceUsage')
go
Additionally, where is your FT Catalog located as by default it is created
under the \FTDATA directory where you have SQL Server installed as well as
where your FT-enabled database files (*.mdf, *.ndf, * *.ldf) are located
relative to the location of your FT Catalog folder? If your 'ResourceUsage
is set to 3, you should increase it to 5 (dedicated) via sp_fulltext_service
'resource_usage' <value>, where <value> is 5.
Thanks,
John
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> Hello,
> I have full text search enabled on two large tables, the first one has 3
> million records (FT index column length is around 512-1025 char), and the
> other one has around 1.6 records (FT index column length is around 50-120
> char)
> The simplest query that uses the smiplest form of FT on the first table FT
> index search needs 23-30 seconds to complete and from 10-15 seconds on the
> seconds table as it the indexed column is smaller.
> SQL server is deployed on 4 P servers with 8 GB of memory and attached to
a
> very powerful SAN system. Memory usage on the server is not very high so I
> believe there is enough memory for the mssearch service to use.
> I just wonder if this is the normal throughput of the Full Text search of
> SQL Server. If not, I will appreciate any tips and hints that might be the
> reason of the system.
> --
> Ali Salem
|||Thanks.
- We are using enlgihs language.
- Returned number of rows should be very large (thousands, in some cases it
can be 100, 000). this from the Full-Text Seach, other SQL filters will be
applied as well, but from mssearch this is what will be returned. Please note
that the table size is around 3million records.
- The smiplest query such as SELECT * FROM TABLE1 WHERE CONTAINS(COL1,
'ABCD') need around 30 seconds complete.
I do appreciate any help.
thank you
"Hilary Cotter" wrote:
> No, this is highly abnormal. Performance should be sub second. However I
> need to know what your queries look like, ie how many search arguments, how
> many rows are returned, and what language you are querying in.
>
> "Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
> news:AECBD305-A9FD-4759-B240-B4D970B32BEA@.microsoft.com...
> FTS.
> from
> rows
> possible - I
> around
> 3
> the
> 50-120
> table FT
> the
> to
> so I
> of
> the
>
>
|||Below are the result of the script you requested me to run it:
My FT files are stored on SAN storage. So IO should be performing well.
Regarding the ResourceUsage, it is 3. Will not raising it to 5 harm the sql
server performance! I dont want to make the full text search fast by slowing
down SQL Server itself.
I have splitted the result into two posts so that I can post it here.
Thank you for you help
------
us_english
(1 row(s) affected)
------
------
Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
(1 row(s) affected)
name minimum maximum config_value
run_value
-- -- -- --
default full-text language 0 2147483647 1033 1033
ftcatid NAME
PATH
STATUS NUMBER_FULLTEXT_TABLES
------
------
------
-- -- --
5 ICSubject
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
6 Subject
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
7 Names
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
(3 row(s) affected)
TABLE_OWNER
TABLE_NAME
FULLTEXT_KEY_INDEX_NAME
FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME
------
------
------
-- -- --
----
dbo
IC_SUBJECTS
PK_IC_SUBJECTS
1 1 ICSubject
dbo
MAIN
PK_INOUT_MAIN
1 1 Subject
dbo
PERSONS
PK_PERSONS
1 1 Names
(3 row(s) affected)
TABLE_OWNER
TABLE_ID TABLE_NAME
FULLTEXT_COLUMN_NAME
FULLTEXT_COLID FULLTEXT_BLOBTP_COLNAME
FULLTEXT_BLOBTP_COLID
FULLTEXT_LANGUAGE
------
-- --
-----
-----
-- --
--- --
dbo
2069582411 IC_SUBJECTS
ITEM_DESCRIPTION_AR
3 NULL
NULL 0
dbo
274100017 MAIN
SUBJECT
42 NULL
NULL 0
dbo
466100701 PERSONS
FIRSTNAME_AR
4 NULL
NULL 0
dbo
466100701 PERSONS
LASTNAME_AR
8 NULL
NULL 0
dbo
466100701 PERSONS
FULL_NAME
9 NULL
NULL 0
dbo
466100701 PERSONS
FATHERNAME_AR
12 NULL
NULL 0
dbo
466100701 PERSONS
GRANDFATHERNAME_AR
14 NULL
NULL 0
(7 row(s) affected)
Name
Owner
Type Created_datetime
------
------
-- --
MAIN
dbo
user table 2004-10-14 18:14:31.457
|||The Rest of the script result, one more post is required
Column_name
Type
Computed Length Prec Scale Nullable
TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- -- --
-- --
-- --
DOCID
int
no 4 10 0 no
(n/a)
(n/a) NULL
DESCRIPTION
varchar
no 255 yes
no
no Arabic_CI_AS
DOCDATE
datetime
no 8 yes
(n/a)
(n/a) NULL
RDOCDATE
datetime
no 8 yes
(n/a)
(n/a) NULL
EXT_PARTY_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
EXT_PARTY_REP_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBCAT_ID
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
STATUS_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
CONFID_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
REFERENCE_NO
varchar
no 50 yes
no
no Arabic_CI_AS
FORWARD_TO
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
FORWARD_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
TO_REMIND
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
REMIND_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
REMARKS
varchar
no 1024 yes
no
no Arabic_CI_AS
REVISION_NO
varchar
no 50 yes
no
no Arabic_CI_AS
TRAN_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
USER_ID
varchar
no 32 yes
no
no Arabic_CI_AS
SYSTEM_ID
varchar
no 50 yes
no
no Arabic_CI_AS
IP
varchar
no 50 yes
no
no Arabic_CI_AS
CHECK_SUM
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
SUBMIT_TYPE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBJECT_TYPE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBJECT
nvarchar
no 8000 yes
(n/a)
(n/a) Arabic_CI_AS
PRIORITY_NO
int
no 4 10 0 yes
(n/a)
(n/a) NULL
PROCESS_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
REMIND_TIME
varchar
no 20 yes
no
no Arabic_CI_AS
WAITING_FOR_REPLY
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SITE_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
DELIVERY_METHOD_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
GROUPID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
LINK_STATUS
int
no 4 10 0 yes
(n/a)
(n/a) NULL
INITIAL_PROCEDURE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
CREATOR_USER_ID
varchar
no 32 yes
no
no Arabic_CI_AS
IS_SPLITTED
bit
no 1 yes
(n/a)
(n/a) NULL
CHAR_FIELD2
varchar
no 500 yes
no
no Arabic_CI_AS
CHAR_FIELD5_AR
nvarchar
no 100 yes
(n/a)
(n/a) Arabic_CI_AS
NUM_FIELD1
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
CHAR_FIELD66_AR
nvarchar
no 2000 yes
(n/a)
(n/a) Arabic_CI_AS
NUM_FIELD6
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
FLOAT_FIELD1
int
no 4 10 0 yes
(n/a)
(n/a) NULL
FLOAT_FIELD2
int
no 4 10 0 yes
(n/a)
(n/a) NULL
NUM_FIELD2
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD3
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD4
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD5
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
DATE_FIELD1
datetime
no 8 yes
(n/a)
(n/a) NULL
DATE_FIELD2
datetime
no 8 yes
(n/a)
(n/a) NULL
CHAR_FIELD1
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD3
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD4
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD5
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD1_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
CHAR_FIELD2_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
CHAR_FIELD4_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
Identity
Seed Increment
Not For Replication
------
-- ---
--- --
No identity column defined.
NULL NULL
NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
SECONDARY2
|||Last post of the script result.
index_name
index_description
index_keys
------
------
----- --
------
IX_Index1
nonclustered located on SECONDARY2
DOCID, STATUS_ID, CABID, YEAR,
CONFID_ID, DELIVERY_PLACE, PRIORITY_NO,
IX_Index2
nonclustered located on SECONDARY4
INOUTDOCNO, CABID, DOC_ORIGN_SOURCE,
YEAR, DOCDATE, OUTBOUND_REQUEST
IX_Index3
nonclustered located on SECONDARY2
GROUPID
IX_Index4
nonclustered located on SECONDARY4
YEAR, EXT_DOCNO, RDOCDATE_HJ,
EXT_PARTY_ID, DIWAN_REP_ID, EXT_PARTY_REP_ID
IX_Index5
nonclustered located on SECONDARY2
REQUEST_NO
IX_Index6
nonclustered located on SECONDARY4
STATUS_ID, CABID
PK_MAIN
clustered, unique, primary key located on SECONDARY2
DOCNO
constraint_type
constraint_name
delete_action update_action status_enabled
status_for_replication constraint_keys
------
-- -- -- --
-- --
------
-----
DEFAULT on column OUTBOUND_REQUEST
DF_MAIN_OUTBOUND_REQUEST
(n/a) (n/a) (n/a) (n/a)
(0)
FOREIGN KEY
FK_MAIN_EMPLOYEES
No Action No Action Enabled
Not_For_Replication USER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_EMPLOYEES1
No Action No Action Enabled
Not_For_Replication WRITER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_EMPLOYEES2
No Action No Action Enabled
Not_For_Replication CREATOR_USER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_LO_CONFIDENTIAL
No Action No Action Enabled
Not_For_Replication CONFID_ID
REFERENCES dbo.LO_CONFIDENTIAL (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_DOC_SOURCE_TYPE
No Action No Action Enabled
Not_For_Replication DOC_ORIGN_SOURCE
REFERENCES dbo.LO_DOC_SOURCE_TYPE
(TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS
No Action No Action Enabled
Not_For_Replication DELIVERY_METHOD_ID
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS1
No Action No Action Enabled
Not_For_Replication LINK_STATUS
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS4
No Action No Action Enabled
Not_For_Replication RECEIVE_METHOD_ID
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_PRIORITY
No Action No Action Enabled
Not_For_Replication PRIORITY_NO
REFERENCES dbo.LO_PRIORITY (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION
No Action No Action Enabled
Not_For_Replication EXT_PARTY_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION1
No Action No Action Enabled
Not_For_Replication EXT_PARTY_REP_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION2
No Action No Action Enabled
Not_For_Replication CITY_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SIGNATURE_TYPE
No Action No Action Enabled
Not_For_Replication DIWAN_REP_ID
REFERENCES dbo.LO_SIGNATURE_TYPE (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SITE
No Action No Action Enabled
Not_For_Replication DELIVERY_PLACE
REFERENCES dbo.LO_SITE (SITE_ID)
FOREIGN KEY
FK_MAIN_LO_STATUS
No Action No Action Enabled
Not_For_Replication STATUS_ID
REFERENCES dbo.LO_STATUS (TAB_ID)
FOREIGN KEY
FK_MAIN_MAIN_GROUP_HDR
No Action No Action Enabled
Not_For_Replication GROUPID
REFERENCES dbo.MAIN_GROUP_HDR (
GROUPID)
FOREIGN KEY
FK_MAIN_PROCESSES
No Action No Action Enabled
Not_For_Replication INITIAL_PROCEDURE
REFERENCES dbo.PROCESSES (
PROCESS_ID)
PRIMARY KEY (clustered)
PK_MAIN
(n/a) (n/a) (n/a) (n/a)
DOCNO
Table is referenced by foreign key
------
------
dbo.TASKS: FK_TASKS_MAIN
dbo.ASKST_CHAIN: FK_TASKS_CHAIN_MAIN
dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN
dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN1
dbo.ATTACHMENTS_MOV: FK_ATTACH_MOV_MAIN
dbo.COPY: FK_COPY_MAIN
dbo.INQUIRY: FK_INQUIRY_MAIN
dbo.LINKS_F: FK_LINKS_F_MAIN
dbo.CLASS: FK_CLASS_MAIN
dbo.XLASS: FK_CLASS_MAIN
dbo.REQUEST: FK_REQUEST_MAIN
dbo.REQUEST_DTL: FK_REQUEST_DTL_MAIN
dbo.MAIN_FOLLOW: FK_MAIN_FOLLOW_MAIN
dbo.MAIN_GROUP_DTL: FK_MAIN_GROUP_DTL_MAIN
dbo.MAIN_TRACK: FK_MAIN_TRACK_MAIN
dbo.PERSONS: FK_PERSONS_MAIN
dbo.RESERVED: FK_RESERVED_MAIN
dbo.SAVE_TRACK: FK_SAVE_TRACK_MAIN
Table is referenced by views
------
3
(1 row(s) affected)
|||Ali,
First of all, thank you for providing this info as it is most helpful! You
are using SQL Server 2000 SP2 on Windows 2000 Server SP3 and your default
language is US_English (1033). However, all of your char, varchar and
nvarchar columns using the Arabic_CI_AS collation with the FT-enabled column
"Language for Word Breaker" set to Neutral. Can I assume that these column
contain Arabic text?
Yes, I can understand why you want the Resource_Usage level to remain at 3,
but you might try setting to 5, as you can always set it lower if it is
affecting your other SQL Server processing. Although, this bump in the
MSSearch resource usage might not be significant. Note, it affect how much
memory MSSearch can use up to a max if 512MB RAM, if available as well as
the number of concurrent connections allowed.
What is the drive letter for your SAN storage? Could you also run the
following SQL query and post it's results?
sp_helpdb <full_text_enabled_database_name>
I'm not sure if the use of Arabic text is causing the poor query performance
(even with the Neutral wordbreaker), but language is a consideration with
FTS query performance issues.
Thanks again,
John
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:D20604B8-21E5-496F-A957-1F1CF8949716@.microsoft.com...
> Last post of the script result.
>
>
> index_name
>
> index_description
>
> index_keys
>
>
>
> ----
----
> --
> ----
> ----
-- --
> ----
----
> ----
--
> IX_Index1
>
> nonclustered located on SECONDARY2
>
> DOCID, STATUS_ID, CABID, YEAR,
> CONFID_ID, DELIVERY_PLACE, PRIORITY_NO,
> IX_Index2
>
> nonclustered located on SECONDARY4
>
> INOUTDOCNO, CABID, DOC_ORIGN_SOURCE,
> YEAR, DOCDATE, OUTBOUND_REQUEST
> IX_Index3
>
> nonclustered located on SECONDARY2
>
> GROUPID
> IX_Index4
>
> nonclustered located on SECONDARY4
>
> YEAR, EXT_DOCNO, RDOCDATE_HJ,
> EXT_PARTY_ID, DIWAN_REP_ID, EXT_PARTY_REP_ID
> IX_Index5
>
> nonclustered located on SECONDARY2
>
> REQUEST_NO
> IX_Index6
>
> nonclustered located on SECONDARY4
>
> STATUS_ID, CABID
> PK_MAIN
>
> clustered, unique, primary key located on SECONDARY2
>
> DOCNO
>
> constraint_type
>
> constraint_name
>
> delete_action update_action status_enabled
> status_for_replication constraint_keys
>
>
>
> ----
----
> --
> ----
--
> -- -- -- --
> -- --
> ----
----
> ----
--
> DEFAULT on column OUTBOUND_REQUEST
>
> DF_MAIN_OUTBOUND_REQUEST
>
> (n/a) (n/a) (n/a) (n/a)
> (0)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES
>
> No Action No Action Enabled
> Not_For_Replication USER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES1
>
> No Action No Action Enabled
> Not_For_Replication WRITER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES2
>
> No Action No Action Enabled
> Not_For_Replication CREATOR_USER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_LO_CONFIDENTIAL
>
> No Action No Action Enabled
> Not_For_Replication CONFID_ID
>
>
>
> REFERENCES dbo.LO_CONFIDENTIAL (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_DOC_SOURCE_TYPE
>
> No Action No Action Enabled
> Not_For_Replication DOC_ORIGN_SOURCE
>
>
>
> REFERENCES dbo.LO_DOC_SOURCE_TYPE
> (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS
>
> No Action No Action Enabled
> Not_For_Replication DELIVERY_METHOD_ID
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS1
>
> No Action No Action Enabled
> Not_For_Replication LINK_STATUS
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS4
>
> No Action No Action Enabled
> Not_For_Replication RECEIVE_METHOD_ID
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_PRIORITY
>
> No Action No Action Enabled
> Not_For_Replication PRIORITY_NO
>
>
>
> REFERENCES dbo.LO_PRIORITY (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION
>
> No Action No Action Enabled
> Not_For_Replication EXT_PARTY_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION1
>
> No Action No Action Enabled
> Not_For_Replication EXT_PARTY_REP_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION2
>
> No Action No Action Enabled
> Not_For_Replication CITY_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SIGNATURE_TYPE
>
> No Action No Action Enabled
> Not_For_Replication DIWAN_REP_ID
>
>
>
> REFERENCES dbo.LO_SIGNATURE_TYPE (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SITE
>
> No Action No Action Enabled
> Not_For_Replication DELIVERY_PLACE
>
>
>
> REFERENCES dbo.LO_SITE (SITE_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_STATUS
>
> No Action No Action Enabled
> Not_For_Replication STATUS_ID
>
>
>
> REFERENCES dbo.LO_STATUS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_MAIN_GROUP_HDR
>
> No Action No Action Enabled
> Not_For_Replication GROUPID
>
>
>
> REFERENCES dbo.MAIN_GROUP_HDR (
> GROUPID)
> FOREIGN KEY
>
> FK_MAIN_PROCESSES
>
> No Action No Action Enabled
> Not_For_Replication INITIAL_PROCEDURE
>
>
>
> REFERENCES dbo.PROCESSES (
> PROCESS_ID)
> PRIMARY KEY (clustered)
>
> PK_MAIN
>
> (n/a) (n/a) (n/a) (n/a)
> DOCNO
>
> Table is referenced by foreign key
>
>
>
> ----
----
> ----
----
> --
> dbo.TASKS: FK_TASKS_MAIN
> dbo.ASKST_CHAIN: FK_TASKS_CHAIN_MAIN
> dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN
> dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN1
> dbo.ATTACHMENTS_MOV: FK_ATTACH_MOV_MAIN
> dbo.COPY: FK_COPY_MAIN
> dbo.INQUIRY: FK_INQUIRY_MAIN
> dbo.LINKS_F: FK_LINKS_F_MAIN
> dbo.CLASS: FK_CLASS_MAIN
> dbo.XLASS: FK_CLASS_MAIN
> dbo.REQUEST: FK_REQUEST_MAIN
> dbo.REQUEST_DTL: FK_REQUEST_DTL_MAIN
> dbo.MAIN_FOLLOW: FK_MAIN_FOLLOW_MAIN
> dbo.MAIN_GROUP_DTL: FK_MAIN_GROUP_DTL_MAIN
> dbo.MAIN_TRACK: FK_MAIN_TRACK_MAIN
> dbo.PERSONS: FK_PERSONS_MAIN
> dbo.RESERVED: FK_RESERVED_MAIN
> dbo.SAVE_TRACK: FK_SAVE_TRACK_MAIN
> Table is referenced by views
>
> ----
----
> --
>
> --
> 3
> (1 row(s) affected)
>
sql
Full text search of varbinary column
I'm looking for suggestions on the best approach for creating a full text
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to convert
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.
You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
http://www.zetainteractive.com - Shift Happens!
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
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.
|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:
> You could create the ifilter and have it emit the text data which is stored
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
> news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
>
>
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to convert
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.
You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
http://www.zetainteractive.com - Shift Happens!
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
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.
|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:
> You could create the ifilter and have it emit the text data which is stored
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
> news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
>
>
Full text search of varbinary column
I'm looking for suggestions on the best approach for creating a full text
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to conver
t
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
http://www.zetainteractive.com - Shift Happens!
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
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:
> You could create the ifilter and have it emit the text data which is store
d
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in messag
e
> news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
>
>
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to conver
t
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
http://www.zetainteractive.com - Shift Happens!
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
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:
> You could create the ifilter and have it emit the text data which is store
d
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in messag
e
> news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
>
>
Full text search of varbinary column
I'm looking for suggestions on the best approach for creating a full text
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to convert
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
--
http://www.zetainteractive.com - Shift Happens!
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
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:
> You could create the ifilter and have it emit the text data which is stored
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
> news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> > I'm looking for suggestions on the best approach for creating a full text
> > search index on a varbinary column. The varbinary data is the binary
> > serialization of a custom clr datatype (but to be clear -- the column is
> > typed varbinary, not the custom type).
> >
> > I thought I might be able to create a computed column that converted the
> > varbinary to the custom type, then used methods on the custom type to
> > convert
> > to string. But it seems like I can't do that without persisting the
> > computed
> > column which I really don't want to do.
> >
> > Another approach I'm considering is to create an iFilter for the datatype,
> > then create a computed column that just returns the column type I'd
> > register
> > for the iFilter. That just seems a little heavy weight to me and has
> > deployment headaches.
> >
> > I can't help think I'm missing something obvious. Any ideas?
> >
> > Thanks.
>
>sql
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to convert
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
--
http://www.zetainteractive.com - Shift Happens!
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
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:
> You could create the ifilter and have it emit the text data which is stored
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
> news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> > I'm looking for suggestions on the best approach for creating a full text
> > search index on a varbinary column. The varbinary data is the binary
> > serialization of a custom clr datatype (but to be clear -- the column is
> > typed varbinary, not the custom type).
> >
> > I thought I might be able to create a computed column that converted the
> > varbinary to the custom type, then used methods on the custom type to
> > convert
> > to string. But it seems like I can't do that without persisting the
> > computed
> > column which I really don't want to do.
> >
> > Another approach I'm considering is to create an iFilter for the datatype,
> > then create a computed column that just returns the column type I'd
> > register
> > for the iFilter. That just seems a little heavy weight to me and has
> > deployment headaches.
> >
> > I can't help think I'm missing something obvious. Any ideas?
> >
> > Thanks.
>
>sql
2012年3月22日星期四
Full Text Search Engine in Chinese Simplied
contains(name,'"张三"')
will not find the row in database with column named "name" and "张三" is sure there,but will find '张三一'，'张三二' why?
name column is sure in the fulltext category and data population is finished!ID Name Sex
-
1 张三 男
2 张三一 男
3 张三二 女
4 张三四 女
I execute the T-SQL Statement : contains(name, '"张三"'),and the Result :
ID Name Sex
-
2 张三一 男
3 张三二 女
4 张三四 女
Why the Row named 张三 is not in result？|||While I don't read Chinese, could you reply with the full output of the following SQL code?
select objectproperty(OBJECT_ID(N'<table_name>'), 'TableFulltextItemCount')
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Please, note the LCID values from sp_help_fulltext_columns for the FULLTEXT_LANGUAGE column. This information should help identify what the problem is for your Full Text Search (FTS) enabled table.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
select * from Table1 where freetext (*, N'张三')
Full Text Search Engine in Chinese Simplied
contains(name,'"张三"')
will not find the row in database with column named "name" and "张三" is sure there,but will find '张三一'，'张三二' why?
name column is sure in the fulltext category and data population is finished!ID Name Sex
-
1 张三 男
2 张三一 男
3 张三二 女
4 张三四 女
I execute the T-SQL Statement : contains(name, '"张三"'),and the Result :
ID Name Sex
-
2 张三一 男
3 张三二 女
4 张三四 女
Why the Row named 张三 is not in result？
|||While I don't read Chinese, could you reply with the full output of the following SQL code?
select objectproperty(OBJECT_ID(N'<table_name>'), 'TableFulltextItemCount')
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Please, note the LCID values from sp_help_fulltext_columns for the FULLTEXT_LANGUAGE column. This information should help identify what the problem is for your Full Text Search (FTS) enabled table.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
select * from Table1 where freetext (*, N'张三')
2012年3月21日星期三
Full Text Search - Conatins doesnot work
Our SQL Server 2000 (sp1) on a Windows 2000 system is configured for a full text search.
The ntext column having a full text index has a value as follows:
Rob Proctor's Tips: Creating a "Tropical" Get-Away At Home
when I search it by
SELECT NTEXT_COL
FROM FULL_TEXT_TABLE
WHERE CONTAINS(NTEXT_COL, N'"Tropical Get Away"')
it displays the result as
NTEXT_COL
======================================
Rob Proctor's Tips: Creating a "Tropical" Get-Away At Home
But the query
SELECT NTEXT_COL
FROM FULL_TEXT_TABLE
WHERE CONTAINS(NTEXT_COL, N'"Tropical Get Aw*"')
do not return any rows..
Can anybody give me the reason for this behaviour, or is it a known bug?
Thanks
Sajan'Get' is included in Noise-word file and therefore query does not return what do you expect. Just remove hyphen from Get-Away (GetAway), repopulate index, run query and you'll see.
SELECT NTEXT_COL
FROM FULL_TEXT_TABLE
WHERE CONTAINS(NTEXT_COL, N'"Tropical GetAw*"')
I agree it is stupid but I guess it was designed this way. You could update list of your Noise words but size of indexes will be increased...
The ntext column having a full text index has a value as follows:
Rob Proctor's Tips: Creating a "Tropical" Get-Away At Home
when I search it by
SELECT NTEXT_COL
FROM FULL_TEXT_TABLE
WHERE CONTAINS(NTEXT_COL, N'"Tropical Get Away"')
it displays the result as
NTEXT_COL
======================================
Rob Proctor's Tips: Creating a "Tropical" Get-Away At Home
But the query
SELECT NTEXT_COL
FROM FULL_TEXT_TABLE
WHERE CONTAINS(NTEXT_COL, N'"Tropical Get Aw*"')
do not return any rows..
Can anybody give me the reason for this behaviour, or is it a known bug?
Thanks
Sajan'Get' is included in Noise-word file and therefore query does not return what do you expect. Just remove hyphen from Get-Away (GetAway), repopulate index, run query and you'll see.
SELECT NTEXT_COL
FROM FULL_TEXT_TABLE
WHERE CONTAINS(NTEXT_COL, N'"Tropical GetAw*"')
I agree it is stupid but I guess it was designed this way. You could update list of your Noise words but size of indexes will be increased...
Full Text Search
Hi,
Would below mentioned thing is possible in sqlserver 2005 full text.
User Table - Table
Id Description - Column name
1 'Mission Impossible' - a row
I want to ge this row if the search criteria is 'pos'. it is simillar to ' %pos%' in like search
is it possible to get that row using Contains() or Freetext(). I tried the following, but it doesnt return any rows.
CONTAINS(Description, ' "*pos*" ')
~ Arjun
SQLServer 2005 does not support arbitrary substring or regular expression yet. Right now you can only do prefix using fulltext.
|||
have u enabled the full text search in your server.
if so then you need not give the % symbol in the text which is to be searched. There is another option in the CONTAINS function.checkt it out.
please try this.
Ravi.
Full text search
Hi,
Is full text search only for image column?
ThanksNo, also the (n)(var)char columns.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is full text search only for image column?
> Thanks
>|||Thanks Tibor.
What's the benefit for full text search.
If I run a select statement in sql query, Is this using full text serach
automatically?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#N4KrddbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> No, also the (n)(var)char columns.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Is full text search only for image column?
> >
> > Thanks
> >
> >|||You need to use special TSQL language elements to use full text indexes, and there in is the power
of FT indexes. Read in Book sOnline about CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:%239yPeldbFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor.
> What's the benefit for full text search.
> If I run a select statement in sql query, Is this using full text serach
> automatically?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#N4KrddbFHA.2968@.TK2MSFTNGP10.phx.gbl...
>> No, also the (n)(var)char columns.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > Is full text search only for image column?
>> >
>> > Thanks
>> >
>> >
>
Is full text search only for image column?
ThanksNo, also the (n)(var)char columns.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is full text search only for image column?
> Thanks
>|||Thanks Tibor.
What's the benefit for full text search.
If I run a select statement in sql query, Is this using full text serach
automatically?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#N4KrddbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> No, also the (n)(var)char columns.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Is full text search only for image column?
> >
> > Thanks
> >
> >|||You need to use special TSQL language elements to use full text indexes, and there in is the power
of FT indexes. Read in Book sOnline about CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:%239yPeldbFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor.
> What's the benefit for full text search.
> If I run a select statement in sql query, Is this using full text serach
> automatically?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#N4KrddbFHA.2968@.TK2MSFTNGP10.phx.gbl...
>> No, also the (n)(var)char columns.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > Is full text search only for image column?
>> >
>> > Thanks
>> >
>> >
>
Full text Pattern Search
Hi,
Would below mentioned thing is possible in sqlserver 2005 full text.
User Table - Table
Id Description - Column name
1 'Mission Impossible' - a row
I want to ge this row if the search criteria is 'pos'. it is simillar
to ' %pos%' in like search
is it possible to get that row using Contains() or Freetext(). I
tried the following, but it doesnt return any rows.
CONTAINS(Description, ' "*pos*" ')
~ Arjun
No, you can't do this directly. While you can do some suffix (end of word)
based searches, you can't do prefix (beginning of word) based searches.
It may be possible to use the thesaurus expansion feature if you know in
advance what all stem would be.
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
<priyananth@.gmail.com> wrote in message
news:1160545045.049080.147130@.m73g2000cwd.googlegr oups.com...
> Hi,
> Would below mentioned thing is possible in sqlserver 2005 full text.
> User Table - Table
> Id Description - Column name
> 1 'Mission Impossible' - a row
> I want to ge this row if the search criteria is 'pos'. it is simillar
> to ' %pos%' in like search
> is it possible to get that row using Contains() or Freetext(). I
> tried the following, but it doesnt return any rows.
> CONTAINS(Description, ' "*pos*" ')
> ~ Arjun
>
|||Hi Hilary,
Thank you for u r reply. Would it be possible to modify the indexing
mechanism which does. or could you please explain how to do it in
thesaurus.
Thanks.
~Arjun.
Hilary Cotter wrote:[vbcol=seagreen]
> No, you can't do this directly. While you can do some suffix (end of word)
> based searches, you can't do prefix (beginning of word) based searches.
> It may be possible to use the thesaurus expansion feature if you know in
> advance what all stem would be.
> --
> 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
>
> <priyananth@.gmail.com> wrote in message
> news:1160545045.049080.147130@.m73g2000cwd.googlegr oups.com...
|||Hi Hilary,
Thank you for u r reply. Would it be possible to modify the indexing
mechanism which does. or could you please explain how to do it in
thesaurus.
Thanks.
~Arjun.
Hilary Cotter wrote:[vbcol=seagreen]
> No, you can't do this directly. While you can do some suffix (end of word)
> based searches, you can't do prefix (beginning of word) based searches.
> It may be possible to use the thesaurus expansion feature if you know in
> advance what all stem would be.
> --
> 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
>
> <priyananth@.gmail.com> wrote in message
> news:1160545045.049080.147130@.m73g2000cwd.googlegr oups.com...
|||You would have to write your own word breaker to do this, and then write a
stemmer to stem for prefixes. I still don't think it can be done.
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
<priyananth@.gmail.com> wrote in message
news:1161075470.863928.272330@.i3g2000cwc.googlegro ups.com...
> Hi Hilary,
> Thank you for u r reply. Would it be possible to modify the indexing
> mechanism which does. or could you please explain how to do it in
> thesaurus.
> Thanks.
> ~Arjun.
>
> Hilary Cotter wrote:
>
|||I have the same problem.
It seems crippling that full text indexing does not support searching for
terms in the middle of token. Is there really that great of a demand for
searching the beginnings of words? Are there any third party products you
can recommend that will help me provide a search feature that will integrate
with SQL server?
CONTAINS term* works great but doesn't return the results I need and
'%term%' is far too slow to use in a production environment over 169000 rows
and multiple columns.
Your input is greatly appreciated.
"Hilary Cotter" wrote:
> You would have to write your own word breaker to do this, and then write a
> stemmer to stem for prefixes. I still don't think it can be done.
> --
> 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
>
> <priyananth@.gmail.com> wrote in message
> news:1161075470.863928.272330@.i3g2000cwc.googlegro ups.com...
>
>
|||You might want to look at Lucene. The story on its performance is complex.
Basically SQL FTS does a really good job and offers better performance than
Lucene on a single server when you get big. When you get big you start
needing many machines to scale using lucene. The other problem with lucence
is that when you want to do property searches you end up storing data in
your full-text index and you search response times decline radically. So you
need to partition which means more machines.
How about you return your results from sql fts and then trim them using the
like? You will need to use the thesaurus option to expand your search
criteria to all possible terms you might be looking for.
Hilary Cotter
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
"Uriah" <Uriah@.discussions.microsoft.com> wrote in message
news:521E3F54-443B-4855-9A80-5DB71C3096F2@.microsoft.com...[vbcol=seagreen]
>I have the same problem.
> It seems crippling that full text indexing does not support searching for
> terms in the middle of token. Is there really that great of a demand for
> searching the beginnings of words? Are there any third party products you
> can recommend that will help me provide a search feature that will
> integrate
> with SQL server?
> CONTAINS term* works great but doesn't return the results I need and
> '%term%' is far too slow to use in a production environment over 169000
> rows
> and multiple columns.
> Your input is greatly appreciated.
> "Hilary Cotter" wrote:
|||My goal is to have a customer searching for parts over several fields and
finding any one of several tokens anywhere within the text (similar to the
LIKE '%term%' functionality). For example, they might want to search for
'56789' and get back the part with part number 'GAR56789'. I don't see how
it is possible to do that with FTS at all. If I knew what the search terms
were going to be I could certainly pre-add them to the thesaurus but the
point of the search is that I can't predict what terms they might enter.
Maybe this is where SQL server functionality breaks down and it's time to
move on to search appliance or software platform. I'll take a look at Lucene.
"Hilary Cotter" wrote:
> You might want to look at Lucene. The story on its performance is complex.
> Basically SQL FTS does a really good job and offers better performance than
> Lucene on a single server when you get big. When you get big you start
> needing many machines to scale using lucene. The other problem with lucence
> is that when you want to do property searches you end up storing data in
> your full-text index and you search response times decline radically. So you
> need to partition which means more machines.
> How about you return your results from sql fts and then trim them using the
> like? You will need to use the thesaurus option to expand your search
> criteria to all possible terms you might be looking for.
> --
> Hilary Cotter
> 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
>
> "Uriah" <Uriah@.discussions.microsoft.com> wrote in message
> news:521E3F54-443B-4855-9A80-5DB71C3096F2@.microsoft.com...
>
>
|||If it is always a prefix you might want to store your content in reverse and
then reverse your query and do a wildcard.
Hilary Cotter
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
"Uriah" <Uriah@.discussions.microsoft.com> wrote in message
news:B695EBD3-11F8-4DEA-A81D-A1828178C577@.microsoft.com...[vbcol=seagreen]
> My goal is to have a customer searching for parts over several fields and
> finding any one of several tokens anywhere within the text (similar to the
> LIKE '%term%' functionality). For example, they might want to search for
> '56789' and get back the part with part number 'GAR56789'. I don't see
> how
> it is possible to do that with FTS at all. If I knew what the search
> terms
> were going to be I could certainly pre-add them to the thesaurus but the
> point of the search is that I can't predict what terms they might enter.
> Maybe this is where SQL server functionality breaks down and it's time to
> move on to search appliance or software platform. I'll take a look at
> Lucene.
> "Hilary Cotter" wrote:
Would below mentioned thing is possible in sqlserver 2005 full text.
User Table - Table
Id Description - Column name
1 'Mission Impossible' - a row
I want to ge this row if the search criteria is 'pos'. it is simillar
to ' %pos%' in like search
is it possible to get that row using Contains() or Freetext(). I
tried the following, but it doesnt return any rows.
CONTAINS(Description, ' "*pos*" ')
~ Arjun
No, you can't do this directly. While you can do some suffix (end of word)
based searches, you can't do prefix (beginning of word) based searches.
It may be possible to use the thesaurus expansion feature if you know in
advance what all stem would be.
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
<priyananth@.gmail.com> wrote in message
news:1160545045.049080.147130@.m73g2000cwd.googlegr oups.com...
> Hi,
> Would below mentioned thing is possible in sqlserver 2005 full text.
> User Table - Table
> Id Description - Column name
> 1 'Mission Impossible' - a row
> I want to ge this row if the search criteria is 'pos'. it is simillar
> to ' %pos%' in like search
> is it possible to get that row using Contains() or Freetext(). I
> tried the following, but it doesnt return any rows.
> CONTAINS(Description, ' "*pos*" ')
> ~ Arjun
>
|||Hi Hilary,
Thank you for u r reply. Would it be possible to modify the indexing
mechanism which does. or could you please explain how to do it in
thesaurus.
Thanks.
~Arjun.
Hilary Cotter wrote:[vbcol=seagreen]
> No, you can't do this directly. While you can do some suffix (end of word)
> based searches, you can't do prefix (beginning of word) based searches.
> It may be possible to use the thesaurus expansion feature if you know in
> advance what all stem would be.
> --
> 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
>
> <priyananth@.gmail.com> wrote in message
> news:1160545045.049080.147130@.m73g2000cwd.googlegr oups.com...
|||Hi Hilary,
Thank you for u r reply. Would it be possible to modify the indexing
mechanism which does. or could you please explain how to do it in
thesaurus.
Thanks.
~Arjun.
Hilary Cotter wrote:[vbcol=seagreen]
> No, you can't do this directly. While you can do some suffix (end of word)
> based searches, you can't do prefix (beginning of word) based searches.
> It may be possible to use the thesaurus expansion feature if you know in
> advance what all stem would be.
> --
> 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
>
> <priyananth@.gmail.com> wrote in message
> news:1160545045.049080.147130@.m73g2000cwd.googlegr oups.com...
|||You would have to write your own word breaker to do this, and then write a
stemmer to stem for prefixes. I still don't think it can be done.
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
<priyananth@.gmail.com> wrote in message
news:1161075470.863928.272330@.i3g2000cwc.googlegro ups.com...
> Hi Hilary,
> Thank you for u r reply. Would it be possible to modify the indexing
> mechanism which does. or could you please explain how to do it in
> thesaurus.
> Thanks.
> ~Arjun.
>
> Hilary Cotter wrote:
>
|||I have the same problem.
It seems crippling that full text indexing does not support searching for
terms in the middle of token. Is there really that great of a demand for
searching the beginnings of words? Are there any third party products you
can recommend that will help me provide a search feature that will integrate
with SQL server?
CONTAINS term* works great but doesn't return the results I need and
'%term%' is far too slow to use in a production environment over 169000 rows
and multiple columns.
Your input is greatly appreciated.
"Hilary Cotter" wrote:
> You would have to write your own word breaker to do this, and then write a
> stemmer to stem for prefixes. I still don't think it can be done.
> --
> 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
>
> <priyananth@.gmail.com> wrote in message
> news:1161075470.863928.272330@.i3g2000cwc.googlegro ups.com...
>
>
|||You might want to look at Lucene. The story on its performance is complex.
Basically SQL FTS does a really good job and offers better performance than
Lucene on a single server when you get big. When you get big you start
needing many machines to scale using lucene. The other problem with lucence
is that when you want to do property searches you end up storing data in
your full-text index and you search response times decline radically. So you
need to partition which means more machines.
How about you return your results from sql fts and then trim them using the
like? You will need to use the thesaurus option to expand your search
criteria to all possible terms you might be looking for.
Hilary Cotter
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
"Uriah" <Uriah@.discussions.microsoft.com> wrote in message
news:521E3F54-443B-4855-9A80-5DB71C3096F2@.microsoft.com...[vbcol=seagreen]
>I have the same problem.
> It seems crippling that full text indexing does not support searching for
> terms in the middle of token. Is there really that great of a demand for
> searching the beginnings of words? Are there any third party products you
> can recommend that will help me provide a search feature that will
> integrate
> with SQL server?
> CONTAINS term* works great but doesn't return the results I need and
> '%term%' is far too slow to use in a production environment over 169000
> rows
> and multiple columns.
> Your input is greatly appreciated.
> "Hilary Cotter" wrote:
|||My goal is to have a customer searching for parts over several fields and
finding any one of several tokens anywhere within the text (similar to the
LIKE '%term%' functionality). For example, they might want to search for
'56789' and get back the part with part number 'GAR56789'. I don't see how
it is possible to do that with FTS at all. If I knew what the search terms
were going to be I could certainly pre-add them to the thesaurus but the
point of the search is that I can't predict what terms they might enter.
Maybe this is where SQL server functionality breaks down and it's time to
move on to search appliance or software platform. I'll take a look at Lucene.
"Hilary Cotter" wrote:
> You might want to look at Lucene. The story on its performance is complex.
> Basically SQL FTS does a really good job and offers better performance than
> Lucene on a single server when you get big. When you get big you start
> needing many machines to scale using lucene. The other problem with lucence
> is that when you want to do property searches you end up storing data in
> your full-text index and you search response times decline radically. So you
> need to partition which means more machines.
> How about you return your results from sql fts and then trim them using the
> like? You will need to use the thesaurus option to expand your search
> criteria to all possible terms you might be looking for.
> --
> Hilary Cotter
> 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
>
> "Uriah" <Uriah@.discussions.microsoft.com> wrote in message
> news:521E3F54-443B-4855-9A80-5DB71C3096F2@.microsoft.com...
>
>
|||If it is always a prefix you might want to store your content in reverse and
then reverse your query and do a wildcard.
Hilary Cotter
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
"Uriah" <Uriah@.discussions.microsoft.com> wrote in message
news:B695EBD3-11F8-4DEA-A81D-A1828178C577@.microsoft.com...[vbcol=seagreen]
> My goal is to have a customer searching for parts over several fields and
> finding any one of several tokens anywhere within the text (similar to the
> LIKE '%term%' functionality). For example, they might want to search for
> '56789' and get back the part with part number 'GAR56789'. I don't see
> how
> it is possible to do that with FTS at all. If I knew what the search
> terms
> were going to be I could certainly pre-add them to the thesaurus but the
> point of the search is that I can't predict what terms they might enter.
> Maybe this is where SQL server functionality breaks down and it's time to
> move on to search appliance or software platform. I'll take a look at
> Lucene.
> "Hilary Cotter" wrote:
订阅：
博文 (Atom)