OpenXML a Memory Hog?

Posted on 2009-07-04
Medium Priority
Last Modified: 2012-05-07

I have read that OpenXML reserves 1/8 of the total memory of the system it is on to perform its processes. I would like to use XML to simplify many processes, such as storing structured data in a table row.

I find it very easy to use, but I am concerned with ending up with a dead server because all the memory has been drained. I was trying to use the Chilkat Soft XML Parser for SQL Server, but it does not seem to be working and even though I bought their bundle, I cant get support.

Its important that I do the processing of the XML in SQL Server.
Question by:dilithiumtoys
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
LVL 40

Accepted Solution

mrjoltcola earned 750 total points
ID: 24777414
>>Chilkat Soft XML Parser for SQL Server, but it does not seem to be working and even though I bought their bundle, I cant get support.

You don't say. I also license their stuff and "similar" experience. Some good APIs but not so good customer service.

As to your question regarding XML.

1) I don't know anything about OpenXML specifically, so you may stop reading here and skip my post
2) XML in general is a memory hog, any way you slice it. Parsing XML is one of the least efficient things you can do in any language or platform. If you stick with SAX/event based parsers, you can skip most of the memory overhead, but often DOM is needed and that surely hogs memory.
3) In my experience, XML does not simply much of anything, given the alternatives. If it is structured data in a database, store it as such. You are absolutely right in your worry about performance, parsing XML out of a column is a very inefficient use of your powerful SQL Server engine. I've seen real world cases of huge documents causing even Oracle to run out of JVM memory with its builtin XML support.

My rule of thumb:

If the XML is meant to be parsed by an external tool, storing it as a LOB/text field in the database is perfect. You are only serving the document up to the tool.
If the XML is actually structured data that I need to access and/or correlate to my database tables, I do not store it as XML, I create an actual schema model for it so it is stored in an efficient form for retrieval.

Hope that helps.
LVL 23

Expert Comment

ID: 24778201
<<. I would like to use XML to simplify many processes, such as storing structured data in a table row.>>
The further away you stay from XML the better you will be in a database system.  

XML is a bad solution looking for a problem to solve.  Not only XML does not solve anything that would not be solved better without it but it also creates problems that would never exists if XML was simply ignored.

<<Its important that I do the processing of the XML in SQL Server.>>
As any hierarchic model inspired data representation, XML has no data manipulative capabilities and imposes prohibitive resource cost at runtime.  I'd suggest you simply stick to store its content on the database (as FILESTREAM for instance) and let an external parser do what you are looking.

My two cents...

Author Comment

ID: 24778385
I sure hope there are more posts. I have heard from the nays. what about the yeas?

For what I would do with XML, it does not make sense to a) serve it up to an outside process b) parse it elsewhere c) bring it back into SQL server so that I can use it.

One app I am building is for generating on the fly reports. The report schema is in XML (which needs to be parsed by "something") and the results have to be feed into the SQL server and the result has to be returned in XML.

The report has to be somewhat self-contained so that I am not constantly updating code to generate a new report. The XML schema contains the data to construct a variable map table. The variable map is a table variable containing dynamic data assigned to a name pair.

I use the variables to filter the results of the base tables that I attempting to get data from. The entire result is returned as XML so that style sheets can be applied.

I really need this to run on the SQL Server because imagine if I have 50 entries in my xml that need to be variables. I would need to execute 50 database calls over the wire. However if I process the XML on the server where the data is, then number of calls falls to 1.

I guess my main thing is not "IS XML BAD?" because for my purposes it is not. I want to know if OpenXML to parse the data is going to cause serious issues. Or is there another XML parser that will fit the bill.

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 750 total points
ID: 24779192

Author Closing Comment

ID: 31599789
The answers I received would be great if I had SQL 2005 and above. I have SQL 2000, and our budget does not allow for upgrade. The question was not answered very well. I wanted to know about the select * from OpenXML memory issues.
LVL 40

Expert Comment

ID: 24781154
Hi dilithiumtoys,

I can understand if you feel the question was not answered well. I did note in my response that it was not specific to OpenXML. You are under no obligation to close the question so soon and/or to accept any answers.

One problem is this is July 4 weekend in the US, so many people are not here to see the question. We can either re-open the question or you can ask a related question while being very specific in the wording what you want to see. And you can note "Please, only answers from people with OpenXML experience in SQL 2000"

I hope that helps.

There are many articles and links on the net about OpenXML performance issues, which I'm sure you've Googled already, and this supports my findings with other XML implementations such as Oracle. It is intrinsic to XML parsing itself, which is why I discouraged using it _if_ possible. Sometimes XML is the solution. We found it worked well for dynamic forms and reports. But it has to be tested. You cannot really go on someome else's advice here before trying it yourself. My XML is different than yours. You will certainly find the CPU load to be higher in a production app than if regular table structures are used, since an XML doc has to be re-parsed every time. The parse overhead is one of the problems. With regular tables, there is no parse.

I wish you good luck,



Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question