Ureader.com  
Microsoft software help and Community
   home   |   control panel login   |   archive   |  
 
SQL
ce
clients
clustering
connect
datamining
datawarehouse
dts
fulltext
jdbcdriver
msde
mseq
newusers
notificationsvcs
odbc
olap
programming
replication
reportingsvcs
security
securitytools
server
setup
sqlxml.viewmapper
tools
xml
  
 
date: Wed, 20 Aug 2008 10:24:02 -0700,    group: microsoft.public.sqlserver.xml        back       


Faster search with xquery contains()    
Hi

In our work we have a table with 37000 records (aprox). The table have a xml 
column, with  primary xml index and a secondary path index.
The xml structure is something like this:
<Usuario>
  <mbUSR_Usuario>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
    ....
    ....
  </mbUSR_Usuario>
</Usuario>

I execute the following query to check if an XML value contains the letter 
"c" in tbus_Email:

select tbob_idObj
from proOBJ_Object
where 
tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') = 1 

this query returns 2099 records in 47 seconds, which is too long for our 
time requirements.

We also tried combine full-text search with xml querying, trying to speed up 
the response time:

select tbob_idObj
from proOBJ_Object
where contains(tbob_ObjData,'c')
and 
tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') = 1

but the time of execution is near the same for the same results because 
full-text  CONTAINS() finds "c" in all records of the table and the 
CONTAINS() not allow make more specific searches.

Is there a way to improve these response times?

I really appreciate your help.
date: Wed, 20 Aug 2008 10:24:02 -0700   author:   byteman

RE: Faster search with xquery contains()    
I don't know if this will go any faster, but have you tried shredding the XML 
column and then querying it, eg

SELECT *
FROM
	(
	SELECT tbob_ObjData.value('(Usuario/mbUSR_Usuario/tbus_Email/text())[1]', 
'VARCHAR(100)' ) 
	FROM proOBJ_Object
	) x (email_address)
WHERE email_address Like '%c%'

It's not a very selective search you're doing, combined with the use of the 
wildcards, I'm not sure indexes are going to help much.


"byteman" wrote:

> Hi
> 
> In our work we have a table with 37000 records (aprox). The table have a xml 
> column, with  primary xml index and a secondary path index.
> The xml structure is something like this:
> <Usuario>
>   <mbUSR_Usuario>
>     <tbus_Email>ukel1ele@mail.cl</tbus_Email>
>     ....
>     ....
>   </mbUSR_Usuario>
> </Usuario>
> 
> I execute the following query to check if an XML value contains the letter 
> "c" in tbus_Email:
> 
> select tbob_idObj
> from proOBJ_Object
> where 
> tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') = 1 
> 
> this query returns 2099 records in 47 seconds, which is too long for our 
> time requirements.
> 
> We also tried combine full-text search with xml querying, trying to speed up 
> the response time:
> 
> select tbob_idObj
> from proOBJ_Object
> where contains(tbob_ObjData,'c')
> and 
> tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') = 1
> 
> but the time of execution is near the same for the same results because 
> full-text  CONTAINS() finds "c" in all records of the table and the 
> CONTAINS() not allow make more specific searches.
> 
> Is there a way to improve these response times?
> 
> I really appreciate your help.
> 
> 
>
date: Thu, 21 Aug 2008 02:53:00 -0700   author:   Bob

RE: Faster search with xquery contains()    
Bob 
Your query is more slow. In fact working with XML is very slow in SQL Server 
2005.  

In my experience, especially when you excute for example "select xmlcolumn 
from MyTable" and the Xml Column is medium size, can will be very slow.

"Bob" wrote:

> I don't know if this will go any faster, but have you tried shredding the XML 
> column and then querying it, eg
> 
> SELECT *
> FROM
> 	(
> 	SELECT tbob_ObjData.value('(Usuario/mbUSR_Usuario/tbus_Email/text())[1]', 
> 'VARCHAR(100)' ) 
> 	FROM proOBJ_Object
> 	) x (email_address)
> WHERE email_address Like '%c%'
> 
> It's not a very selective search you're doing, combined with the use of the 
> wildcards, I'm not sure indexes are going to help much.
> 
> 
> "byteman" wrote:
> 
> > Hi
> > 
> > In our work we have a table with 37000 records (aprox). The table have a xml 
> > column, with  primary xml index and a secondary path index.
> > The xml structure is something like this:
> > <Usuario>
> >   <mbUSR_Usuario>
> >     <tbus_Email>ukel1ele@mail.cl</tbus_Email>
> >     ....
> >     ....
> >   </mbUSR_Usuario>
> > </Usuario>
> > 
> > I execute the following query to check if an XML value contains the letter 
> > "c" in tbus_Email:
> > 
> > select tbob_idObj
> > from proOBJ_Object
> > where 
> > tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') = 1 
> > 
> > this query returns 2099 records in 47 seconds, which is too long for our 
> > time requirements.
> > 
> > We also tried combine full-text search with xml querying, trying to speed up 
> > the response time:
> > 
> > select tbob_idObj
> > from proOBJ_Object
> > where contains(tbob_ObjData,'c')
> > and 
> > tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') = 1
> > 
> > but the time of execution is near the same for the same results because 
> > full-text  CONTAINS() finds "c" in all records of the table and the 
> > CONTAINS() not allow make more specific searches.
> > 
> > Is there a way to improve these response times?
> > 
> > I really appreciate your help.
> > 
> > 
> >
date: Fri, 29 Aug 2008 12:50:00 -0700   author:   sqlextreme

RE: Faster search with xquery contains()    
I tested it out on a sample table, with 40,000 records I'd generated, and it 
wasn't slow, with out without clustered index.

"sqlextreme" wrote:

> Bob 
> Your query is more slow. In fact working with XML is very slow in SQL Server 
> 2005.  
> 
> In my experience, especially when you excute for example "select xmlcolumn 
> from MyTable" and the Xml Column is medium size, can will be very slow.
> 
> "Bob" wrote:
> 
> > I don't know if this will go any faster, but have you tried shredding the XML 
> > column and then querying it, eg
> > 
> > SELECT *
> > FROM
> > 	(
> > 	SELECT tbob_ObjData.value('(Usuario/mbUSR_Usuario/tbus_Email/text())[1]', 
> > 'VARCHAR(100)' ) 
> > 	FROM proOBJ_Object
> > 	) x (email_address)
> > WHERE email_address Like '%c%'
> > 
> > It's not a very selective search you're doing, combined with the use of the 
> > wildcards, I'm not sure indexes are going to help much.
> > 
> > 
> > "byteman" wrote:
> > 
> > > Hi
> > > 
> > > In our work we have a table with 37000 records (aprox). The table have a xml 
> > > column, with  primary xml index and a secondary path index.
> > > The xml structure is something like this:
> > > <Usuario>
> > >   <mbUSR_Usuario>
> > >     <tbus_Email>ukel1ele@mail.cl</tbus_Email>
> > >     ....
> > >     ....
> > >   </mbUSR_Usuario>
> > > </Usuario>
> > > 
> > > I execute the following query to check if an XML value contains the letter 
> > > "c" in tbus_Email:
> > > 
> > > select tbob_idObj
> > > from proOBJ_Object
> > > where 
> > > tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') = 1 
> > > 
> > > this query returns 2099 records in 47 seconds, which is too long for our 
> > > time requirements.
> > > 
> > > We also tried combine full-text search with xml querying, trying to speed up 
> > > the response time:
> > > 
> > > select tbob_idObj
> > > from proOBJ_Object
> > > where contains(tbob_ObjData,'c')
> > > and 
> > > tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') = 1
> > > 
> > > but the time of execution is near the same for the same results because 
> > > full-text  CONTAINS() finds "c" in all records of the table and the 
> > > CONTAINS() not allow make more specific searches.
> > > 
> > > Is there a way to improve these response times?
> > > 
> > > I really appreciate your help.
> > > 
> > > 
> > >
date: Sat, 30 Aug 2008 08:52:02 -0700   author:   Bob

RE: Faster search with xquery contains()    
Bob
Maybe your xml is very short, test with some like this. 

<Usuario>
  <mbUSR_Usuario>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
    <tbus_Contrasena>Secure</tbus_Contrasena>
    <tbus_FechaCreacion>2007-05-07T11:53:38.880</tbus_FechaCreacion>
    <tbus_Nombre>pepito</tbus_Nombre>
    <tbus_ApellidoPater>perez</tbus_ApellidoPater>
    <tbus_ApellidoMater>pereira</tbus_ApellidoMater>
    <tbus_Rut>1215121</tbus_Rut>
    <tbus_Sexo>M</tbus_Sexo>
    <tbus_Profesion>Abogado</tbus_Profesion>
    <tbus_Direccion>buenas peras sn</tbus_Direccion>
    <tbus_Comuna>Santiago</tbus_Comuna>
    <tbus_Ciudad>santiago</tbus_Ciudad>
    <tbus_Pais>chile</tbus_Pais>
    <tbus_CodPostal>454879</tbus_CodPostal>
    <tbus_FonoCasa>15646545</tbus_FonoCasa>
    <tbus_FonoCelular>4546546</tbus_FonoCelular>
    <tbus_FechaNacimiento Año="1970" Mes="10" Dia="11">
      <Fecha>1970-10-11T00:00:00</Fecha>
      <Año>1970</Año>
      <Mes>10</Mes>
      <Dia>11</Dia>
    </tbus_FechaNacimiento>
    <tbus_EstadoCivil>S</tbus_EstadoCivil>
    <tbus_NumHijos>3</tbus_NumHijos>
    <tbus_Idioma>espanol</tbus_Idioma>
    <tbus_AceptaTerminos>S</tbus_AceptaTerminos>
    <tbus_ModificadoPor>nadie</tbus_ModificadoPor>
    <tbus_ID>1</tbus_ID>
  </mbUSR_Usuario>
  <mbUSR_XP1>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
    <tbxp1_tipo>1</tbxp1_tipo>
  </mbUSR_XP1>
  <mbUSR_XP2>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
    <tbxp2_tipo>1</tbxp2_tipo>
  </mbUSR_XP2>
  <mbUSR_XP3>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
    <tbxp3_tipo>1</tbxp3_tipo>
  </mbUSR_XP3>
  <mbUSR_XP4>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
    <tbxp4_tipo>1</tbxp4_tipo>
  </mbUSR_XP4>
  <mbUSR_XP5>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
    <tbxp5_tipo>1</tbxp5_tipo>
    <tbxp5_varchar1>rthreht</tbxp5_varchar1>
  </mbUSR_XP5>
  <mbUSR_XP6>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
    <tbxp6_tipo>1</tbxp6_tipo>
    <tbxp6_varchar1>hola</tbxp6_varchar1>
  </mbUSR_XP6>
  <mbUSR_XP7>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
    <tbxp7_tipo>1</tbxp7_tipo>
  </mbUSR_XP7>
  <mbUSR_AreasInteres>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
  </mbUSR_AreasInteres>
  <mbUSR_DatosEmpresa>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
  </mbUSR_DatosEmpresa>
  <mbUSR_Otros>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
  </mbUSR_Otros>
  <mbUSR_Boletines>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
    <tbbo_Boletin1>1</tbbo_Boletin1>
  </mbUSR_Boletines>
  <mbUSR_BoletinesPagados>
    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
  </mbUSR_BoletinesPagados>
</Usuario>

What got time for your query? 


bye


"Bob" wrote:

> I tested it out on a sample table, with 40,000 records I'd generated, and it 
> wasn't slow, with out without clustered index.
> 
> "sqlextreme" wrote:
> 
> > Bob 
> > Your query is more slow. In fact working with XML is very slow in SQL Server 
> > 2005.  
> > 
> > In my experience, especially when you excute for example "select xmlcolumn 
> > from MyTable" and the Xml Column is medium size, can will be very slow.
> > 
> > "Bob" wrote:
> > 
> > > I don't know if this will go any faster, but have you tried shredding the XML 
> > > column and then querying it, eg
> > > 
> > > SELECT *
> > > FROM
> > > 	(
> > > 	SELECT tbob_ObjData.value('(Usuario/mbUSR_Usuario/tbus_Email/text())[1]', 
> > > 'VARCHAR(100)' ) 
> > > 	FROM proOBJ_Object
> > > 	) x (email_address)
> > > WHERE email_address Like '%c%'
> > > 
> > > It's not a very selective search you're doing, combined with the use of the 
> > > wildcards, I'm not sure indexes are going to help much.
> > > 
> > > 
> > > "byteman" wrote:
> > > 
> > > > Hi
> > > > 
> > > > In our work we have a table with 37000 records (aprox). The table have a xml 
> > > > column, with  primary xml index and a secondary path index.
> > > > The xml structure is something like this:
> > > > <Usuario>
> > > >   <mbUSR_Usuario>
> > > >     <tbus_Email>ukel1ele@mail.cl</tbus_Email>
> > > >     ....
> > > >     ....
> > > >   </mbUSR_Usuario>
> > > > </Usuario>
> > > > 
> > > > I execute the following query to check if an XML value contains the letter 
> > > > "c" in tbus_Email:
> > > > 
> > > > select tbob_idObj
> > > > from proOBJ_Object
> > > > where 
> > > > tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') = 1 
> > > > 
> > > > this query returns 2099 records in 47 seconds, which is too long for our 
> > > > time requirements.
> > > > 
> > > > We also tried combine full-text search with xml querying, trying to speed up 
> > > > the response time:
> > > > 
> > > > select tbob_idObj
> > > > from proOBJ_Object
> > > > where contains(tbob_ObjData,'c')
> > > > and 
> > > > tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') = 1
> > > > 
> > > > but the time of execution is near the same for the same results because 
> > > > full-text  CONTAINS() finds "c" in all records of the table and the 
> > > > CONTAINS() not allow make more specific searches.
> > > > 
> > > > Is there a way to improve these response times?
> > > > 
> > > > I really appreciate your help.
> > > > 
> > > > 
> > > >
date: Mon, 1 Sep 2008 08:38:01 -0700   author:   sqlextreme

Re: Faster search with xquery contains()    
Are you actually trying to search using 'c' in Full-Text Search?  By default 
the 'c' would be stripped out as a noise word.  I assume you're using 
something other than 'c' in your FTS predicate?

-- 

========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"
http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X


"byteman"  wrote in message 
news:D40F8210-B7FD-4299-A8EE-8AACF6AAADCA@microsoft.com...
> Hi
>
> In our work we have a table with 37000 records (aprox). The table have a 
> xml
> column, with  primary xml index and a secondary path index.
> The xml structure is something like this:
> <Usuario>
>  <mbUSR_Usuario>
>    <tbus_Email>ukel1ele@mail.cl</tbus_Email>
>    ....
>    ....
>  </mbUSR_Usuario>
> </Usuario>
>
> I execute the following query to check if an XML value contains the letter
> "c" in tbus_Email:
>
> select tbob_idObj
> from proOBJ_Object
> where
> tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') 
> = 1
>
> this query returns 2099 records in 47 seconds, which is too long for our
> time requirements.
>
> We also tried combine full-text search with xml querying, trying to speed 
> up
> the response time:
>
> select tbob_idObj
> from proOBJ_Object
> where contains(tbob_ObjData,'c')
> and
> tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') 
> = 1
>
> but the time of execution is near the same for the same results because
> full-text  CONTAINS() finds "c" in all records of the table and the
> CONTAINS() not allow make more specific searches.
>
> Is there a way to improve these response times?
>
> I really appreciate your help.
>
>
>
date: Sat, 13 Sep 2008 11:19:29 -0400   author:   Michael Coles michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net

Google
 
Web ureader.com


    COPYRIGHT 2007, YARDI TECHNOLOGY LIMITED, ALL RIGHT RESERVE  |   contact us