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/default.asp?url=/library/en-us/dnsql2k/htm
l/sqlxml_optimperformance.asp
Chapter 14 - Improving SQL Server Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-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!