Re: export to XML file - poor performance

stefan.dyakov@xxxxxxxxx wrote:
Hi all,
I'm trying to create PL/SQL procedure which will export data to xml
file.

the result structure should look like this

order
- action
- action
- action
....
order
order

On first stage I export the data to 2 temporary tables, and on second
I loop from the data and create the output file concatenating the
values for each row.

The procedure runs very slow. If i run it over 300 rowsit takes about 3
minutes.
5 seconds for filling the temp tables and all the the rest for looping
trought the results and concatenating the text.

if i try to run on a real data (about 22 000 rows) it can't finish at
all. the temporary tablespace just grows up to the disk free space and
the system hangs.


Any suggestions about improving the performance?

----------------------

The xml document is CLOB and the add_node just returns the value
enclosed in tags.

FOR order_rec IN get_orders_from_temp_table
LOOP
v_row := '<order>' || add_node(order_rec.pon, 'pon') ||
add_node(order_rec.ver, 'ver') || add_node(order_rec.verid,
'verid') || add_node(order_rec.div, 'div') ||
add_node(order_rec.status, 'status') ||..... '</order>' || CHR(10);
xmldoc := xmldoc || v_row;
END LOOP;


What version of Oracle

If 9i or higher don't use a cursor loop: Use BULK COLLECT
and tune the LIMIT clause

What is ADD_NODE?

Have you run DBMS_PROFILER? If not www.psoug.org, click on Morgan's
Library and look up a demo.

Why do you think the code snip you posted relates to the TEMP
tablespace filling up?
--
Daniel A. Morgan
University of Washington
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • export to XML file - poor performance
    ... I'm trying to create PL/SQL procedure which will export data to xml ... I loop from the data and create the output file concatenating the ...
    (comp.databases.oracle.misc)
  • Re: XML Index on xml variable?
    ... The hypothetical performance difference between using a temp table and a ... slightly higher cost for logging and locking), ... of creating an XML index is much higher than the cost of inserting your XML ...
    (microsoft.public.sqlserver.xml)
  • Re: Convert String data from Web Service to XML
    ... Are you using Xlang or external .net assembly to load xml? ... XmlDocument temp2 = ... why not use temp instead of temp2, ... I am trying to Convert String data from Web Service to XML ...
    (microsoft.public.biztalk.general)
  • Re: SSIS: ET flat text file to multi-level hierachal XML.
    ... I don't know whether MS intend to release an XML destination adapter. ... You are right though in that it may be easier for you to ask the feed providers to drop to you multiple csv files, load them into the pipeline, use a merge join perhaps then eventually use a Script component as a destination and craft the XML yourself. ... I had to extract the formatted report flat text files in to ... SQL Server temp tables, then use lookups and OLE DB SQL commands to ...
    (microsoft.public.sqlserver.dts)
  • Re: XML Index on xml variable?
    ... Creating an XML Index on XML variables is not supported. ... The hypothetical performance difference between using a temp table and a ... DECLARE @MyVar xml ...
    (microsoft.public.sqlserver.xml)
Loading