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
>
2012年3月26日星期一
Full Text Search of terms including asterisks
I have a document containing the term *sosos
Searching for this with different levels of wildcard placement results as follows:
1 CONTAINS([TargetField] , ' "*sosos"') results in 1 item
2 CONTAINS([TargetField] , ' "*sosos*"') results in 1 item
3 CONTAINS([TargetField] , ' "*soso*"') results in 1 item
4 CONTAINS([TargetField] , ' "*sos*"') results in 1 item
5 CONTAINS([TargetField] , ' "*so*"') results in anything where a term starts with SO
6 CONTAINS([TargetField] , ' "*s*"') results many more where a term starts with S
7 CONTAINS([TargetField] , ' "**"') results in nothing retrieved
Searches 1-4 result in what I would expect. Searches 5-7 do not. Is this a bug or am I missing something?
The * can only be used as a suffix not a prefix.
Have a look at my post
Generally * are treated as noise and so removed that is why you are gettnig the results you are
2012年3月22日星期四
Full Text Search Engine and russian language support...
I try to develope application with full text search in SQl tables by fields, that containing russian text. I had to use "neutral" for language for word breaker.
Is there are russian addon's for MS SQL 2000, that can solve this problem? Would be included in MS SQL 2005 russian language support? If not - how can I solve it by other way?
2012年3月11日星期日
Full text index deteriorating in use
Very strange situation on one of our SQL 2005 boxes.
- A Full text catalog containing a single full text index
- The index is of a single table containing a uniqueidentifier column and five varchar(max) columns, all indexed
- About 300,000 rows in the table
If I create the index and immediately run a query like this:
SELECT COUNT(*) FROM FREETEXTTABLE(tablename, columnname, 'term1 term2 term3', 5000)
I get the expected result: 5000 (there are actually about 185,000 matching rows if that same query is run without the top_n_by_rank parameter).
But then if I run the same query repeatedly over the next 10-15 minutes, the returned value decreases...4616, 3505...ending up around 507. It's like the full text index rots away before my eyes.
The database is purely read-only. We do run stored procedures against it that construct temporary tables, but nothing puts new data into this particular table. Doesn't matter whether the full-text index is set to manual or automatic repopulation. The timestamps on the index files on disk don't change. Nothing odd shows up in the Windows event logs or SQL Server logs.
I'm stumped. Any ideas?
http://www.sql-server-performance.com/full_text_search.asp
http://www.sql-server-performance.com/tb_search_optimization.asp
2012年2月19日星期日
FTS query performance on SQL 2005
I am seeing some query performance issues on a full-text search on SQL 2005.
My table has about 20 million rows, containing an integer primary key and a
field of type text. This is running on a dual core Xeon 2.8 Ghz processor, 2
GB RAM, 15k RPM drives in a RAID 5 configuration.
My query looks like this:
select ItemID FROM Item WHERE CONTAINS(ItemText, 'there')
which returns about 40,000 rows, but the query takes over 2 minutes! Is that
normal performance for this beefy server for such a simple query? If I add
"TOP 20" after the SELECT, the query takes under 1 second.
I have checked the hardware and I can't seem to find constraints, either in
CPU, memory or disk. Any ideas why that query takes over 2 minutes?
Thanks.
Speed depends on the complexity of your query and the amount of rows you are
returning. In your case you have a simple query and what appears to be
causing the performance problem. I would use containstable with the
top_n_by_rank parameter to limit your results set to 100 or 200 rows.
Is this SQL 2005? There are some optimizations for SQL 2005 which will offer
better performance.
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
"Bahama Joe" <someone@.microsoft.com> wrote in message
news:ub9L57y%23GHA.1224@.TK2MSFTNGP05.phx.gbl...
>I am seeing some query performance issues on a full-text search on SQL
>2005. My table has about 20 million rows, containing an integer primary key
>and a field of type text. This is running on a dual core Xeon 2.8 Ghz
>processor, 2 GB RAM, 15k RPM drives in a RAID 5 configuration.
> My query looks like this:
> select ItemID FROM Item WHERE CONTAINS(ItemText, 'there')
> which returns about 40,000 rows, but the query takes over 2 minutes! Is
> that normal performance for this beefy server for such a simple query? If
> I add "TOP 20" after the SELECT, the query takes under 1 second.
> I have checked the hardware and I can't seem to find constraints, either
> in CPU, memory or disk. Any ideas why that query takes over 2 minutes?
> Thanks.
>
|||Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
referring to? I believe I have applied all optimizations that I've been able
to find in the various posts and online docs.
I know that I can limit the size of the resultset with top_n_by_rank, but in
this case, I'm trying to get back the full set of results. Another form of
my query is to do a "select count(*)", which has the same response times. I
believe this is because the full resultset is returned from the FTE back to
SQL Server, and then the count is taken on that. Is there a way to structure
the query to tell the FTE that you just want the count of results, so that
it doesn't ship the full results back to SQL Server?
Essentially, I'm trying to create a search engine, where the results will be
displayed back to the user in paginated form, so it will always display a
subset of the resultset, but I'd like to also display "Showing results 1-20
of 40,000", so I need a way to get the size of the resultset.
With regard to hardware, would you expect that I will get the most bang for
my buck by a) spreading my data across more disks (via RAID) in a single
server, b) creating a cluster of separate servers, c) adding more memory, or
d) adding more CPU's?
Thanks.
|||Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
referring to? I believe I have applied all optimizations that I've been able
to find in the various posts and online docs.
I know that I can limit the size of the resultset with top_n_by_rank, but in
this case, I'm trying to get back the full set of results. Another form of
my query is to do a "select count(*)", which has the same response times. I
believe this is because the full resultset is returned from the FTE back to
SQL Server, and then the count is taken on that. Is there a way to structure
the query to tell the FTE that you just want the count of results, so that
it doesn't ship the full results back to SQL Server?
Essentially, I'm trying to create a search engine, where the results will be
displayed back to the user in paginated form, so it will always display a
subset of the resultset, but I'd like to also display "Showing results 1-20
of 40,000", so I need a way to get the size of the resultset.
With regard to hardware, would you expect that I will get the most bang for
my buck by a) spreading my data across more disks (via RAID) in a single
server, b) creating a cluster of separate servers, c) adding more memory, or
d) adding more CPU's?
Thanks.
|||I'm struggling with the same issues as you.
Basically what i do is bank on the fact that it is rare for most people to
look beyond the first page of results, so I write the results of the search
to a table, and return it to a data reader displaying the first 25 results
and a count of all search results. Repeat searches go against the cached
table.
The optimizations are
1) use 64 bit
2) use a high resource_usage keeping in mind this can cause locking
3) reorganize your catalogs frequently
4) set ft crawl bandwidth (max) and ft notify bandwidth (max) to 0
5) max full-text crawl range to the number of cpu's on your system
6) convert your binary data to text
and you get the best bank for your buck by placing the full-text catalog on
its own disk subsystem and controller with the fastest disks available. RAID
5 offers best read performance, but for frequently updated catalogs use raid
10.
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
"Bahama Joe" <someone@.microsoft.com> wrote in message
news:epadrr9%23GHA.4712@.TK2MSFTNGP03.phx.gbl...
> Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
> referring to? I believe I have applied all optimizations that I've been
> able
> to find in the various posts and online docs.
> I know that I can limit the size of the resultset with top_n_by_rank, but
> in
> this case, I'm trying to get back the full set of results. Another form of
> my query is to do a "select count(*)", which has the same response times.
> I
> believe this is because the full resultset is returned from the FTE back
> to
> SQL Server, and then the count is taken on that. Is there a way to
> structure
> the query to tell the FTE that you just want the count of results, so that
> it doesn't ship the full results back to SQL Server?
> Essentially, I'm trying to create a search engine, where the results will
> be
> displayed back to the user in paginated form, so it will always display a
> subset of the resultset, but I'd like to also display "Showing results
> 1-20
> of 40,000", so I need a way to get the size of the resultset.
> With regard to hardware, would you expect that I will get the most bang
> for
> my buck by a) spreading my data across more disks (via RAID) in a single
> server, b) creating a cluster of separate servers, c) adding more memory,
> or
> d) adding more CPU's?
> Thanks.
>
>
|||Thanks for the reply. I had already applied optimizations 2, 4 and 5, based
on one of your earlier posts in another thread. I don't have access to a
64-bit server, but this is a good suggestion. My data is static data, so I
don't expect I'll need to reorganize the catalogs frequently. Also, I only
have text data stored in a single column of type 'text', no binary data.
With regard to caching, I find that subsequent queries using the same search
string (i.e. to support the user going to the next page) come back in about
3-4 ms, meaning that there's already some good caching in place within SQL
Server. It seems that relying on that should be sufficient, instead of
creating my own sepearate caching mechanism as you describe. Is that not
your experience? The most pressing issue I'm trying to resolve is why the
first query (without a top_n_by_rank) can sometimes take 2-3 minutes to
respond.
Thanks.
|||This could be caching of the catalog pages. What is your max server memory
setting?
sp_configure 'max server memory (MB)'
Also what happens if you just issue a new contains query ie like this
select * from TableName where contains(*,'"George Bush"')
supposing you have not searched for George Bush recently and he is in your
content?
It could be that the other tables are causing the performance hit and not
your full-text queries themselves.
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
"Bahama Joe" <someone@.microsoft.com> wrote in message
news:ON3tvnE$GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Thanks for the reply. I had already applied optimizations 2, 4 and 5,
> based on one of your earlier posts in another thread. I don't have access
> to a 64-bit server, but this is a good suggestion. My data is static data,
> so I don't expect I'll need to reorganize the catalogs frequently. Also, I
> only have text data stored in a single column of type 'text', no binary
> data.
> With regard to caching, I find that subsequent queries using the same
> search string (i.e. to support the user going to the next page) come back
> in about 3-4 ms, meaning that there's already some good caching in place
> within SQL Server. It seems that relying on that should be sufficient,
> instead of creating my own sepearate caching mechanism as you describe. Is
> that not your experience? The most pressing issue I'm trying to resolve is
> why the first query (without a top_n_by_rank) can sometimes take 2-3
> minutes to respond.
> Thanks.
>
|||Hello Bahama,
Is this an upgrade or a new SQL install?
Make sure you update your statistics and possibly rebuild your indexes.
To get a count it is better to do select count(1) from containstable (table,
column,search)
To get the paged results do SELECT TOP x where x covers the page the users
wants.
Make sure you SQL box isn't using all the memory, you need to share it with
full text.
How big is your database? 20 million rows and 2Gb of cache doesn't allow
for much of you DB to be cached.
What indexes do you have on your tables?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Thanks for the reply. I had already applied optimizations 2, 4 and 5,
> based on one of your earlier posts in another thread. I don't have
> access to a 64-bit server, but this is a good suggestion. My data is
> static data, so I don't expect I'll need to reorganize the catalogs
> frequently. Also, I only have text data stored in a single column of
> type 'text', no binary data.
> With regard to caching, I find that subsequent queries using the same
> search string (i.e. to support the user going to the next page) come
> back in about 3-4 ms, meaning that there's already some good caching
> in place within SQL Server. It seems that relying on that should be
> sufficient, instead of creating my own sepearate caching mechanism as
> you describe. Is that not your experience? The most pressing issue I'm
> trying to resolve is why the first query (without a top_n_by_rank) can
> sometimes take 2-3 minutes to respond.
> Thanks.
>
|||Hilary,
Here is the result of that query:
name=max server memory (MB)
minimum=16
maximum=2147483647
config_value=4096
run_value=4096
Is that configured correctly for a server w/ 2GB of physical RAM?
Also, in one of your earlier posts, you mentioned setting "max full-text
crawl range" to the number of CPU's on the system. I have a dual-core Xeon
processor, meaning it has 2 CPU's (in one chip). However, due to Hyper
Threading, Windows and SQL Server see this server as having 4 CPU's, so I
have that value set to 4. Is that a good setting?
All of my tests have been on just the single table that contains my
full-text index, such as "select ItemID from Item where
contains(ItemText,'"George Bush"')", so there are no other tables involved.
Thanks for all of your insightful replies.
|||Hello Simon,
This is a new SQL install. I just created the table, loaded it with all 20
million rows from a text file via "Import data...", then created the
full-text index (which took about 2 hours). My table basically includes an
"int" primary key (clustered index) column and a "text" column with the text
to be indexed. The table also includes 2 other "text" fields, but they're
not involved in these queries. The result is a database .MDF file of approx.
12GB and a full-text index with files of approx. 2GB, with the full-text
index containing approx. 1.4 million unique keys.
When I look at task manager for memory usage, I see the following:
sqlservr.exe --> mem usage=740MB, VM size=749MB
msftesql.exe --> mem usage=7.6MB, VM size=5MB
Right after a reboot, these numbers are continually rising, but after a
number of queries, these numbers steady out to the ones listed above. Based
on this, would you recommend that I change my memory configuration?
Thanks.
My table has about 20 million rows, containing an integer primary key and a
field of type text. This is running on a dual core Xeon 2.8 Ghz processor, 2
GB RAM, 15k RPM drives in a RAID 5 configuration.
My query looks like this:
select ItemID FROM Item WHERE CONTAINS(ItemText, 'there')
which returns about 40,000 rows, but the query takes over 2 minutes! Is that
normal performance for this beefy server for such a simple query? If I add
"TOP 20" after the SELECT, the query takes under 1 second.
I have checked the hardware and I can't seem to find constraints, either in
CPU, memory or disk. Any ideas why that query takes over 2 minutes?
Thanks.
Speed depends on the complexity of your query and the amount of rows you are
returning. In your case you have a simple query and what appears to be
causing the performance problem. I would use containstable with the
top_n_by_rank parameter to limit your results set to 100 or 200 rows.
Is this SQL 2005? There are some optimizations for SQL 2005 which will offer
better performance.
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
"Bahama Joe" <someone@.microsoft.com> wrote in message
news:ub9L57y%23GHA.1224@.TK2MSFTNGP05.phx.gbl...
>I am seeing some query performance issues on a full-text search on SQL
>2005. My table has about 20 million rows, containing an integer primary key
>and a field of type text. This is running on a dual core Xeon 2.8 Ghz
>processor, 2 GB RAM, 15k RPM drives in a RAID 5 configuration.
> My query looks like this:
> select ItemID FROM Item WHERE CONTAINS(ItemText, 'there')
> which returns about 40,000 rows, but the query takes over 2 minutes! Is
> that normal performance for this beefy server for such a simple query? If
> I add "TOP 20" after the SELECT, the query takes under 1 second.
> I have checked the hardware and I can't seem to find constraints, either
> in CPU, memory or disk. Any ideas why that query takes over 2 minutes?
> Thanks.
>
|||Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
referring to? I believe I have applied all optimizations that I've been able
to find in the various posts and online docs.
I know that I can limit the size of the resultset with top_n_by_rank, but in
this case, I'm trying to get back the full set of results. Another form of
my query is to do a "select count(*)", which has the same response times. I
believe this is because the full resultset is returned from the FTE back to
SQL Server, and then the count is taken on that. Is there a way to structure
the query to tell the FTE that you just want the count of results, so that
it doesn't ship the full results back to SQL Server?
Essentially, I'm trying to create a search engine, where the results will be
displayed back to the user in paginated form, so it will always display a
subset of the resultset, but I'd like to also display "Showing results 1-20
of 40,000", so I need a way to get the size of the resultset.
With regard to hardware, would you expect that I will get the most bang for
my buck by a) spreading my data across more disks (via RAID) in a single
server, b) creating a cluster of separate servers, c) adding more memory, or
d) adding more CPU's?
Thanks.
|||Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
referring to? I believe I have applied all optimizations that I've been able
to find in the various posts and online docs.
I know that I can limit the size of the resultset with top_n_by_rank, but in
this case, I'm trying to get back the full set of results. Another form of
my query is to do a "select count(*)", which has the same response times. I
believe this is because the full resultset is returned from the FTE back to
SQL Server, and then the count is taken on that. Is there a way to structure
the query to tell the FTE that you just want the count of results, so that
it doesn't ship the full results back to SQL Server?
Essentially, I'm trying to create a search engine, where the results will be
displayed back to the user in paginated form, so it will always display a
subset of the resultset, but I'd like to also display "Showing results 1-20
of 40,000", so I need a way to get the size of the resultset.
With regard to hardware, would you expect that I will get the most bang for
my buck by a) spreading my data across more disks (via RAID) in a single
server, b) creating a cluster of separate servers, c) adding more memory, or
d) adding more CPU's?
Thanks.
|||I'm struggling with the same issues as you.
Basically what i do is bank on the fact that it is rare for most people to
look beyond the first page of results, so I write the results of the search
to a table, and return it to a data reader displaying the first 25 results
and a count of all search results. Repeat searches go against the cached
table.
The optimizations are
1) use 64 bit
2) use a high resource_usage keeping in mind this can cause locking
3) reorganize your catalogs frequently
4) set ft crawl bandwidth (max) and ft notify bandwidth (max) to 0
5) max full-text crawl range to the number of cpu's on your system
6) convert your binary data to text
and you get the best bank for your buck by placing the full-text catalog on
its own disk subsystem and controller with the fastest disks available. RAID
5 offers best read performance, but for frequently updated catalogs use raid
10.
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
"Bahama Joe" <someone@.microsoft.com> wrote in message
news:epadrr9%23GHA.4712@.TK2MSFTNGP03.phx.gbl...
> Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
> referring to? I believe I have applied all optimizations that I've been
> able
> to find in the various posts and online docs.
> I know that I can limit the size of the resultset with top_n_by_rank, but
> in
> this case, I'm trying to get back the full set of results. Another form of
> my query is to do a "select count(*)", which has the same response times.
> I
> believe this is because the full resultset is returned from the FTE back
> to
> SQL Server, and then the count is taken on that. Is there a way to
> structure
> the query to tell the FTE that you just want the count of results, so that
> it doesn't ship the full results back to SQL Server?
> Essentially, I'm trying to create a search engine, where the results will
> be
> displayed back to the user in paginated form, so it will always display a
> subset of the resultset, but I'd like to also display "Showing results
> 1-20
> of 40,000", so I need a way to get the size of the resultset.
> With regard to hardware, would you expect that I will get the most bang
> for
> my buck by a) spreading my data across more disks (via RAID) in a single
> server, b) creating a cluster of separate servers, c) adding more memory,
> or
> d) adding more CPU's?
> Thanks.
>
>
|||Thanks for the reply. I had already applied optimizations 2, 4 and 5, based
on one of your earlier posts in another thread. I don't have access to a
64-bit server, but this is a good suggestion. My data is static data, so I
don't expect I'll need to reorganize the catalogs frequently. Also, I only
have text data stored in a single column of type 'text', no binary data.
With regard to caching, I find that subsequent queries using the same search
string (i.e. to support the user going to the next page) come back in about
3-4 ms, meaning that there's already some good caching in place within SQL
Server. It seems that relying on that should be sufficient, instead of
creating my own sepearate caching mechanism as you describe. Is that not
your experience? The most pressing issue I'm trying to resolve is why the
first query (without a top_n_by_rank) can sometimes take 2-3 minutes to
respond.
Thanks.
|||This could be caching of the catalog pages. What is your max server memory
setting?
sp_configure 'max server memory (MB)'
Also what happens if you just issue a new contains query ie like this
select * from TableName where contains(*,'"George Bush"')
supposing you have not searched for George Bush recently and he is in your
content?
It could be that the other tables are causing the performance hit and not
your full-text queries themselves.
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
"Bahama Joe" <someone@.microsoft.com> wrote in message
news:ON3tvnE$GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Thanks for the reply. I had already applied optimizations 2, 4 and 5,
> based on one of your earlier posts in another thread. I don't have access
> to a 64-bit server, but this is a good suggestion. My data is static data,
> so I don't expect I'll need to reorganize the catalogs frequently. Also, I
> only have text data stored in a single column of type 'text', no binary
> data.
> With regard to caching, I find that subsequent queries using the same
> search string (i.e. to support the user going to the next page) come back
> in about 3-4 ms, meaning that there's already some good caching in place
> within SQL Server. It seems that relying on that should be sufficient,
> instead of creating my own sepearate caching mechanism as you describe. Is
> that not your experience? The most pressing issue I'm trying to resolve is
> why the first query (without a top_n_by_rank) can sometimes take 2-3
> minutes to respond.
> Thanks.
>
|||Hello Bahama,
Is this an upgrade or a new SQL install?
Make sure you update your statistics and possibly rebuild your indexes.
To get a count it is better to do select count(1) from containstable (table,
column,search)
To get the paged results do SELECT TOP x where x covers the page the users
wants.
Make sure you SQL box isn't using all the memory, you need to share it with
full text.
How big is your database? 20 million rows and 2Gb of cache doesn't allow
for much of you DB to be cached.
What indexes do you have on your tables?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Thanks for the reply. I had already applied optimizations 2, 4 and 5,
> based on one of your earlier posts in another thread. I don't have
> access to a 64-bit server, but this is a good suggestion. My data is
> static data, so I don't expect I'll need to reorganize the catalogs
> frequently. Also, I only have text data stored in a single column of
> type 'text', no binary data.
> With regard to caching, I find that subsequent queries using the same
> search string (i.e. to support the user going to the next page) come
> back in about 3-4 ms, meaning that there's already some good caching
> in place within SQL Server. It seems that relying on that should be
> sufficient, instead of creating my own sepearate caching mechanism as
> you describe. Is that not your experience? The most pressing issue I'm
> trying to resolve is why the first query (without a top_n_by_rank) can
> sometimes take 2-3 minutes to respond.
> Thanks.
>
|||Hilary,
Here is the result of that query:
name=max server memory (MB)
minimum=16
maximum=2147483647
config_value=4096
run_value=4096
Is that configured correctly for a server w/ 2GB of physical RAM?
Also, in one of your earlier posts, you mentioned setting "max full-text
crawl range" to the number of CPU's on the system. I have a dual-core Xeon
processor, meaning it has 2 CPU's (in one chip). However, due to Hyper
Threading, Windows and SQL Server see this server as having 4 CPU's, so I
have that value set to 4. Is that a good setting?
All of my tests have been on just the single table that contains my
full-text index, such as "select ItemID from Item where
contains(ItemText,'"George Bush"')", so there are no other tables involved.
Thanks for all of your insightful replies.
|||Hello Simon,
This is a new SQL install. I just created the table, loaded it with all 20
million rows from a text file via "Import data...", then created the
full-text index (which took about 2 hours). My table basically includes an
"int" primary key (clustered index) column and a "text" column with the text
to be indexed. The table also includes 2 other "text" fields, but they're
not involved in these queries. The result is a database .MDF file of approx.
12GB and a full-text index with files of approx. 2GB, with the full-text
index containing approx. 1.4 million unique keys.
When I look at task manager for memory usage, I see the following:
sqlservr.exe --> mem usage=740MB, VM size=749MB
msftesql.exe --> mem usage=7.6MB, VM size=5MB
Right after a reboot, these numbers are continually rising, but after a
number of queries, these numbers steady out to the ones listed above. Based
on this, would you recommend that I change my memory configuration?
Thanks.
FTS- not returning results when lines are searched.
Hello everyone,
I am performing full-text-search on a table containing word documents
in it's column. The search results fair when a single word is
searched. i.e. when I fire the following query:
select * from docs where contains(document, '"java"');
but if I try to search the a full line or give multiple words in the
text to be searched, then it shows no results, even if there is same
text present in the document stored in the database.
select * from docs where contains(document, '"this text is in
database"');
what could be the possible cause of the problem. Shouldn't the FTS
return the records which contains even a single word in the queried
text.
Please help me with the issue.
Thanks & Regards,
Varun Narang.
It definitely should return results for both a single token or a phrase.
However in your phrase you have noise words? Did you empty your noise word
list and replace it with a single space?
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
"Varun" <varunarang@.gmail.com> wrote in message
news:1142867652.058844.39330@.g10g2000cwb.googlegro ups.com...
> Hello everyone,
> I am performing full-text-search on a table containing word documents
> in it's column. The search results fair when a single word is
> searched. i.e. when I fire the following query:
> select * from docs where contains(document, '"java"');
> but if I try to search the a full line or give multiple words in the
> text to be searched, then it shows no results, even if there is same
> text present in the document stored in the database.
> select * from docs where contains(document, '"this text is in
> database"');
> what could be the possible cause of the problem. Shouldn't the FTS
> return the records which contains even a single word in the queried
> text.
> Please help me with the issue.
> Thanks & Regards,
> Varun Narang.
>
|||Varun wrote on 20 Mar 2006 07:14:12 -0800:
> Hello everyone,
> I am performing full-text-search on a table containing word documents
> in it's column. The search results fair when a single word is
> searched. i.e. when I fire the following query:
> select * from docs where contains(document, '"java"');
> but if I try to search the a full line or give multiple words in the
> text to be searched, then it shows no results, even if there is same
> text present in the document stored in the database.
> select * from docs where contains(document, '"this text is in
> database"');
> what could be the possible cause of the problem. Shouldn't the FTS
> return the records which contains even a single word in the queried
> text.
Because you have double quotes around the phrase, it will only look for rows
with that exact phrase in the text. If you want to search for any/all words
in any order and not necessarily in a single phrase, you need to remove
those double quotes. Also check BOL for the use of logical operators.
Dan
I am performing full-text-search on a table containing word documents
in it's column. The search results fair when a single word is
searched. i.e. when I fire the following query:
select * from docs where contains(document, '"java"');
but if I try to search the a full line or give multiple words in the
text to be searched, then it shows no results, even if there is same
text present in the document stored in the database.
select * from docs where contains(document, '"this text is in
database"');
what could be the possible cause of the problem. Shouldn't the FTS
return the records which contains even a single word in the queried
text.
Please help me with the issue.
Thanks & Regards,
Varun Narang.
It definitely should return results for both a single token or a phrase.
However in your phrase you have noise words? Did you empty your noise word
list and replace it with a single space?
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
"Varun" <varunarang@.gmail.com> wrote in message
news:1142867652.058844.39330@.g10g2000cwb.googlegro ups.com...
> Hello everyone,
> I am performing full-text-search on a table containing word documents
> in it's column. The search results fair when a single word is
> searched. i.e. when I fire the following query:
> select * from docs where contains(document, '"java"');
> but if I try to search the a full line or give multiple words in the
> text to be searched, then it shows no results, even if there is same
> text present in the document stored in the database.
> select * from docs where contains(document, '"this text is in
> database"');
> what could be the possible cause of the problem. Shouldn't the FTS
> return the records which contains even a single word in the queried
> text.
> Please help me with the issue.
> Thanks & Regards,
> Varun Narang.
>
|||Varun wrote on 20 Mar 2006 07:14:12 -0800:
> Hello everyone,
> I am performing full-text-search on a table containing word documents
> in it's column. The search results fair when a single word is
> searched. i.e. when I fire the following query:
> select * from docs where contains(document, '"java"');
> but if I try to search the a full line or give multiple words in the
> text to be searched, then it shows no results, even if there is same
> text present in the document stored in the database.
> select * from docs where contains(document, '"this text is in
> database"');
> what could be the possible cause of the problem. Shouldn't the FTS
> return the records which contains even a single word in the queried
> text.
Because you have double quotes around the phrase, it will only look for rows
with that exact phrase in the text. If you want to search for any/all words
in any order and not necessarily in a single phrase, you need to remove
those double quotes. Also check BOL for the use of logical operators.
Dan
订阅：
博文 (Atom)