Performance problem on a XML data type column

I have problems querying a Table with a XML typed column, someone could help
me?
Notable things are: the original schema is very large and is divided in
several other files (using the XML include instruction). Putting it all
together, the file is about 5000 lines (187 Kbytes).
A simple query like the following, consumes all te memory until the process
dies for 'not enough memory':
SELECT info.query('//patient') FROM test;
We are using SQL Server 2005, and the SQL Server Management Studio to run
the query.
Best regards,
Javier Diaz
javier.diaz@.dvc.esHi there.
I'm working with Javier on this and I would like to add a couple of things
that might be important.
When the query is executed, SQLServer process uses all of the CPU power till
the "not enough memory" error.
The schemas we are using are HL7 standard ones, so there's no room for any
modification to them.
If the schema is unregistered from the database, the query works perfectly.
With the schema registered there's no problem when inserting new rows or
updating existing ones.
Any help will be really appreciated, as this is proving to be a big show
stopper for us.
Many thanks in advance.
Best regards,
Ivan
"Javier Diaz" wrote:

> I have problems querying a Table with a XML typed column, someone could he
lp
> me?
> Notable things are: the original schema is very large and is divided in
> several other files (using the XML include instruction). Putting it all
> together, the file is about 5000 lines (187 Kbytes).
> A simple query like the following, consumes all te memory until the proces
s
> dies for 'not enough memory':
> SELECT info.query('//patient') FROM test;
> We are using SQL Server 2005, and the SQL Server Management Studio to run
> the query.
> Best regards,
> Javier Diaz
> javier.diaz@.dvc.es
>|||Do you have SP1 or SP2 of SQL Server 2005 installed. A lot of work went into
SP1 to improve processing of large schemas such as HL7.
Regards,
Galex Yen
"Ivan Clavijos" wrote:
> Hi there.
> I'm working with Javier on this and I would like to add a couple of things
> that might be important.
> When the query is executed, SQLServer process uses all of the CPU power ti
ll
> the "not enough memory" error.
> The schemas we are using are HL7 standard ones, so there's no room for any
> modification to them.
> If the schema is unregistered from the database, the query works perfectly
.
> With the schema registered there's no problem when inserting new rows or
> updating existing ones.
> Any help will be really appreciated, as this is proving to be a big show
> stopper for us.
> Many thanks in advance.
> Best regards,
> Ivan
> "Javier Diaz" wrote:
>|||I second Galex. There was a lot of work done on perf and memory consumption
in
SP1. There was significant improvement in particular for queries like yours
that
use the descendant-or-self axis.
I had written a post on my blog at the time SP1 was released and it contains
some examples of queries where the improvements were quite impressive . Here
is
the link. http://blogs.msdn.com/denisruc/arch.../20/580288.aspx
If you are using either SP1 or SP2 but are still experiencing perf/memory
consumption issues, please use the contact form on my blog to send me more i
nfo:
http://blogs.msdn.com/denisruc/contact.aspx
Thank you
Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Galex Yen [MSFT]" <Galex Yen [MSFT]@.discussions.microsoft.com> wrote in message
news:5C868B95-C4C5-45C2-ABBB-35CD5F264AD0@.microsoft.com...
> Do you have SP1 or SP2 of SQL Server 2005 installed. A lot of work went in
to
> SP1 to improve processing of large schemas such as HL7.
> Regards,
> Galex Yen
> "Ivan Clavijos" wrote:
>|||Thanks for your help, Galex and Denis :)
Installing SP2 allowed us to execute the query but using the full path. When
trying to execute it using the descendant-or-self path, the same problem was
happening.
Denis, I don't know if you need any additional information about this
problem (I forgot to mention that we are using MS SQL 2005 Express Edition)
as, at the moment, it's fine for us. As we are just doing a Proof of Concept
,
it doesn't matter if we cannot use descendant-or-self.
But, as I said, if you are interested in gathering some additional
information that we may provide to you, do not hesitate to ask.
Thanks a lot again.
Best regards,
Ivan|||Denis and Galex,
As Ivan said before the problem persists in SP2. Queries with descendatn,
like //Patient, result in poor performance. Queries like /A/B/C, worked fine
.
Thanks in advance for your help,
Javier
"Denis Ruckebusch [MSFT]" wrote:

> I second Galex. There was a lot of work done on perf and memory consumptio
n in
> SP1. There was significant improvement in particular for queries like your
s that
> use the descendant-or-self axis.
> I had written a post on my blog at the time SP1 was released and it contai
ns
> some examples of queries where the improvements were quite impressive . He
re is
> the link. http://blogs.msdn.com/denisruc/arch.../20/580288.aspx
> If you are using either SP1 or SP2 but are still experiencing perf/memory
> consumption issues, please use the contact form on my blog to send me more
info:
> http://blogs.msdn.com/denisruc/contact.aspx
> Thank you
> Denis Ruckebusch
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Galex Yen [MSFT]" <Galex Yen [MSFT]@.discussions.microsoft.com> wrote in message
> news:5C868B95-C4C5-45C2-ABBB-35CD5F264AD0@.microsoft.com...
>
>|||I'd like to see the schema and the query and find out if we can fix this. Ca
n I
contact you through the addresses listed on your posts?
If you want me to use another address send me an email through this contact
form:
http://blogs.msdn.com/denisruc/contact.aspx
Thank you
Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ivan Clavijos" <IvanClavijos@.discussions.microsoft.com> wrote in message
news:355717F2-35D4-4E01-8F24-421E5D017EAA@.microsoft.com...
> Thanks for your help, Galex and Denis :)
> Installing SP2 allowed us to execute the query but using the full path. Wh
en
> trying to execute it using the descendant-or-self path, the same problem w
as
> happening.
> Denis, I don't know if you need any additional information about this
> problem (I forgot to mention that we are using MS SQL 2005 Express Edition
)
> as, at the moment, it's fine for us. As we are just doing a Proof of Conce
pt,
> it doesn't matter if we cannot use descendant-or-self.
> But, as I said, if you are interested in gathering some additional
> information that we may provide to you, do not hesitate to ask.
> Thanks a lot again.
> Best regards,
> Ivan
>