Re: faster way to create XML export
- From: Marco Gralike <Marco.Gralike@xxxxxxx>
- To: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
- Date: Wed, 3 Aug 2011 03:10:32 +0200
Ah, I see you have found it :-) (the C bit contra Java wrapper bit)
From: Jeremy Schneider
<jeremy.schneider@xxxxxxxxxxxxxx<mailto:jeremy.schneider@xxxxxxxxxxxxxx>>
Date: Wed, 3 Aug 2011 02:43:36 +0200
To: Marco Gralike <marco.gralike@xxxxxxx<mailto:marco.gralike@xxxxxxx>>
Cc: Oracle-L <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Subject: Re: faster way to create XML export
We just finished a test run with DBMS_XMLGEN on the same dataset -- it finished
in 2 sec. Still used dbms_xslprocessor.clob2file for filewriting. Looks like
this will do most everything I mentioned - just working on the last step now,
getting the XSLT headers. (Seems to be a function for this but it's not well
documented and didn't seem to work on our first try...)
Also, I noticed something in the oracle docs here:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14252/adx_j_xsu.htm#sthref546
"Note: For increased performance, consider using DBMS_XMLGen and DBMS_XMLStore
as alternatives to DBMS_XMLQuery and DBMS_XMLSave. The former packages are
written in C and are built in to the database kernel. You can also use SQL/XML
functions such as XML_Element for XML access in the database."
No kidding - way faster! Wish I'd seen this before! :)
-Jeremy
On Tue, Aug 2, 2011 at 6:34 PM, Jeremy Schneider
<jeremy.schneider@xxxxxxxxxxxxxx<mailto:jeremy.schneider@xxxxxxxxxxxxxx>> wrote:
Oh yeah, we also tested with the DBMS_XMLQuery package - it seemed to take
about as long as the Oracle Serializer java code.
On Tue, Aug 2, 2011 at 6:19 PM, Jeremy Schneider
<jeremy.schneider@xxxxxxxxxxxxxx<mailto:jeremy.schneider@xxxxxxxxxxxxxx>> wrote:
Well we're not making any 1GB files yet, but I can't really imagine what anyone
does with a 400MB file either. Anyway - we were doing some testing with a much
smaller "test" dataset (8MB file), and we tried Marco's idea. Here are the
results:
Original Code: 96 sec
Oracle Serializer: 40 sec
Marco's Code: 3 sec
:)
Sweet! However, it seems that there's an implicit conversion from XMLType to
CLOB and it's not immediately obvious how to control the XML that's generated.
I guess it's time for me to learn some more about XMLDB. :-/ Here's what
we're trying to figure out how to do with XMLType:
- xmlQuery.keepCursorState(true);
- xmlQuery.setMaxRows(pRowLimit_variable); // Note: we continue with cursor
and split output to multiple files
- xmlQuery.setRowTag(pRowHeader_variable);
- xmlQuery.setRowsetTag(pRowHeader_variable+"_MYSUFFIX");
- xmlQuery.setDateFormat("yyyy-MM-dd'T'HH:mm:ss");
- xmlQuery.setStylesheetHeader(pXSLtURI_variable);
Any tips would be appreciated...
-J
PS - this blog post by Marco is excellent:
http://www.liberidu.com/blog/?p=369
On Tue, Aug 2, 2011 at 4:41 PM, Marco Gralike
<Marco.Gralike@xxxxxxx<mailto:Marco.Gralike@xxxxxxx>> wrote:
ONE 1++GB in XML document, are you serious.
Wouldn't be surprised though. I see it more and more often. ;-)
No the stuff showed below is in C and/or even part of the C kernel. It is
possible to do in SQL and yes due to the fact that it is 1 XML document you
will get in trouble regarding those mentioned sizes per XML document. I am
guessing, although never really checked that if you switched it again for a C
based SAV serializer it would be even faster or does it reside in the JVM
database kernel?
I wonder which part picks up those big XML documents and tries to do something
useful with it. Work to be done with those documents afterwards most be very
resource intensive ;-)
--
http://www.ardentperf.com<http://www.ardentperf.com/>
+1 312-725-9249<tel:%2B1%20312-725-9249>
Jeremy Schneider
Chicago
--
http://www.ardentperf.com
+1 312-725-9249
Jeremy Schneider
Chicago
Other related posts: