We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

OpenXML a Memory Hog?

dilithiumtoys
on
Medium Priority
584 Views
Last Modified: 2012-05-07
Hi,

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.
Comment
Watch Question

Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT

Commented:
<<. 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

Commented:
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.

Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
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.
Top Expert 2009

Commented:
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,

mrjoltcola


Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.