Re: export to XML file - poor performance

From: STD <stefan.dyakov_at_gmail.com>
Date: 22 Jun 2006 07:18:23 -0700
Message-ID: <1150985903.361955.289520@g10g2000cwb.googlegroups.com>

Thanks for the reply :)

Yes the DB is 9i and about add_node:
add_node is just small function which verifies and inserts a new node in the xml
but i think the problem here is the datatype conversion.

  FUNCTION strip_invalid(p_text IN VARCHAR2) RETURN VARCHAR2 AS   BEGIN
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p_text, '&',
'&'), '<', '<'), '>', '>'), '''', '''),
'"', '"');

  END strip_invalid;

  FUNCTION add_node(p_node_value IN VARCHAR2, p_node_name IN VARCHAR2) RETURN CLOB AS
  BEGIN
    RETURN '<' || p_node_name || '>' || strip_invalid(p_node_value) ||
'</' || p_node_name || '>';

  END add_node;

  FUNCTION add_node(p_value_num IN NUMBER, p_node_name IN VARCHAR2) RETURN CLOB AS
  BEGIN
    RETURN '<' || p_node_name || '>' || to_char(p_value_num) || '</' || p_node_name || '>';
  END add_node;

  FUNCTION add_node(p_value_date IN DATE, p_node_name IN VARCHAR2) RETURN CLOB AS
  BEGIN
    RETURN '<' || p_node_name || '>' || to_char(p_value_date,
'MM/DD/YYYY HH24:MI') || '</' || p_node_name || '>';
  END add_node;

i tryied to use bulk collect without function calls just directly concatenating the strings and it worked much faster.

DECLARE type txmlrows IS TABLE OF VARCHAR2(4000); vxmlrows txmlrows;
vxml CLOB;
vrow VARCHAR2(4002);
nl constant CHAR(1) := CHR(10);

BEGIN SELECT '<pon>' || pon || '</pon><ver>' || ver || '</ver><verid>' || verid || '</verid><div>' || div || '</div><status>' || status ||
'</status><fname>' || fname || '</fname><lname>' || lname ||
'</lname><acctnum>' || acctnum || '</acctnum><type>' || type ||
'</type><comments>' || comments || '</comments><rpon>' || rpon ||
'</rpon><atn>' || atn || '</atn><ddd>' || ddd || '</ddd><erl>' || erl
|| '</erl><addr1>' || addr1 || '</addr1><addr2>' || addr2 ||
'</addr2><city>' || city || '</city><state>' || state ||
'</state><zip>' || zip || '</zip><pr>' || pr || '</pr><no>' || NO ||
'</no><sf>' || sf || '</sf><sd>' || sd || '</sd><sn>' || sn ||
'</sn><th>' || th || '</th><ss>' || ss || '</ss><dlo>' || dlo ||
'</dlo><mcity>' || mcity || '</mcity><mstate>' || mstate ||
'</mstate><mzip>' || mzip || '</mzip><comunity>' || community ||
'</comunity><pic>' || pic || '</pic><lpic>' || lpic || '</lpic><ipic>'
|| ipic || '</ipic><cnam>' || cnam || '</cnam><lty>' || lty ||
'</lty><adi>' || adi || '</adi><block>' || block || '</block><privacy>'
|| privacy || '</privacy><lnfn>' || lnfn || '</lnfn><lnln>' || lnln ||
'</lnln><tl>' || tl || '</tl><title1>' || title1 || '</title1><rc>' ||
rc || '</rc><rcsc>' || rcsc || '</rcsc><CLECAutomated>' || clecautomated || '</CLECAutomated>'
  bulk collect
  INTO vxmlrows
  FROM tmp_xml_orders;

  dbms_lob.createtemporary(vxml, TRUE, dbms_lob.CALL);   FOR i IN 1 .. nvl(vxmlrows.LAST, 0)
  LOOP
    vrow := vxmlrows(i) || nl;
    dbms_lob.writeappend(vxml, LENGTH(vrow), vrow);   END LOOP;
END;

This worked at about 0.5 sec compared to previous at 2 minutes. But i have to make some checks to the user data if it has invalid characters in it - like > < & .. , so probably i will lose some time there too.

The question is can i use this code to loop just once instead of loading first wirh select into and using a loop for concatenation?

About the temp tablespace i don't know
one simple loop shouldn't fill it. Probably the reason is that i'm making more than 40 function calls on a single row, and there is a parameters passing and returning values - probably makiung a type convertion every time.. Probably the function parameters are put into stack?

DA Morgan wrote:

> stefan.dyakov_at_gmail.com 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_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Received on Thu Jun 22 2006 - 09:18:23 CDT