OPENXML performance

Does anyone know that how to speed up the performance of using OPENXML? I
looked at the execution plan of my query and the remote scan takes up over
90% of the total costs. My XML passed in to the stored procedure only
contains 2 or 3 elements.
thanks,
Joseph
I forgot to mention that the remote scan happened when I ran the following
query:
INSERT INTO #TempTable
Select * From OPENXML(@.hDoc, '/Level1/Level2')
With
#TempTable
"Joseph" <joseph@.bluefield.com.hk> wrote in message
news:%236NpkWohEHA.3476@.tk2msftngp13.phx.gbl...
> Does anyone know that how to speed up the performance of using OPENXML? I
> looked at the execution plan of my query and the remote scan takes up over
> 90% of the total costs. My XML passed in to the stored procedure only
> contains 2 or 3 elements.
> thanks,
> Joseph
>
|||Hi Joseph,
From your descriptions, I am afraid I could not show you where or what to
be modified as it is very hard to troubleshooting issue in newsgroup.
However, I would like to show you some general idea on XML performance
tuning.
Based on my socpe, you should be aware of some of the following scalability
issues that are involved in using SQLXML:
Avoid OPENXML over Large XML Documents
Be aware that there are limitations to the amount of memory that is
available to the OPENXML construct over an XML document operation. This
operation builds a Document Object Model (DOM) in the SQL buffer space that
can be much larger than the original document size. Also, this operation is
limited to one eighth of the buffer space, and large XML documents may
consume this memory fairly quickly and cause an out-of-memory error on the
server. Do not create large systems based on this functionality without
conducting significant load testing. You might also want to use the XML
bulk load option if possible.
Avoid Large Numbers of Concurrent OPENXML Statements over XML Documents
You also have to consider the issue with OPENXML when you use OPENXML to
batch inserts. This is a fairly common operation because it is an effective
way to issue a group of inserts with one statement. Issuing a group of
inserts reduces the overhead of multiple insert statements and multiple
round trips. However, be aware that this approach may not be very scalable
because of the aforementioned memory limitations.
More detailed information could be found in the following documents
Optimizing SQLXML Performance
http://msdn.microsoft.com/library/de...us/dnsql2k/htm
l/sqlxml_optimperformance.asp
Chapter 14 - Improving SQL Server Performance
http://msdn.microsoft.com/library/de...us/dnpag/html/
scalenetchapt14.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||To slightly clarify Mingqing' statements, see below.
Best regards
Michael
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:3Bl7fvphEHA.2632@.cpmsftngxa10.phx.gbl...
> Hi Joseph,
> From your descriptions, I am afraid I could not show you where or what to
> be modified as it is very hard to troubleshooting issue in newsgroup.
> However, I would like to show you some general idea on XML performance
> tuning.
> Based on my socpe, you should be aware of some of the following
> scalability
> issues that are involved in using SQLXML:
> Avoid OPENXML over Large XML Documents
> Be aware that there are limitations to the amount of memory that is
> available to the OPENXML construct over an XML document operation. This
> operation builds a Document Object Model (DOM) in the SQL buffer space
> that
> can be much larger than the original document size. Also, this operation
> is
> limited to one eighth of the buffer space,
This is not quite correct. It is one eighth of the available MAIN memory
when sp_xml_preparedocument first loads the MSXML 2.6 dll.

> and large XML documents may
> consume this memory fairly quickly and cause an out-of-memory error on the
> server.
While this may happen, it still is fairly rare, unless you run on a low
memory machine. A DOM for a 100kB XML document normally uses 300 to 600kB
based on the structure.

> Do not create large systems based on this functionality without
> conducting significant load testing. You might also want to use the XML
> bulk load option if possible.
That and the following I fully agree with.

> Avoid Large Numbers of Concurrent OPENXML Statements over XML Documents
> You also have to consider the issue with OPENXML when you use OPENXML to
> batch inserts. This is a fairly common operation because it is an
> effective
> way to issue a group of inserts with one statement. Issuing a group of
> inserts reduces the overhead of multiple insert statements and multiple
> round trips. However, be aware that this approach may not be very scalable
> because of the aforementioned memory limitations.
> More detailed information could be found in the following documents
> Optimizing SQLXML Performance
> http://msdn.microsoft.com/library/de...us/dnsql2k/htm
> l/sqlxml_optimperformance.asp
> Chapter 14 - Improving SQL Server Performance
> http://msdn.microsoft.com/library/de...us/dnpag/html/
> scalenetchapt14.asp
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||In addition to the general comment.
OPENXML is not really executing "remotely", but is modelled after the OLEDB
rowset providers. It calls out to the XPath engine of MSXML that runs
in-proc with the server.
The scalability depends on the size of the data, availability of memory and
the type of XPath expressions that you are using. Normally, it should scale
linearly, but there are some XPath expression that in SQL Server 2000 are
worse (they should be better in the 64-bit version and SQL Server 2005 and
hopefully (no guarantees yet) in SP4.
Best regards
Michael
"Joseph" <joseph@.bluefield.com.hk> wrote in message
news:%236NpkWohEHA.3476@.tk2msftngp13.phx.gbl...
> Does anyone know that how to speed up the performance of using OPENXML? I
> looked at the execution plan of my query and the remote scan takes up over
> 90% of the total costs. My XML passed in to the stored procedure only
> contains 2 or 3 elements.
> thanks,
> Joseph
>