?
Solved

Date insertion from large XML document (stored into a clob parameter) into relation table very very very slow, using DBMS_XMLSAVE.INSERTXML

Posted on 2005-03-16
16
Medium Priority
?
1,524 Views
Last Modified: 2013-11-19
Hi Everybody!

I'm working with Oracle 9.2.0.5 on Microsoft Windows Server 2003 Enterprise Edition.
The server (a test server) is a Pentium 4  2.8 GHz, 1GB of RAM.

I use a procedure called PARITOP_TRAITERXMLRESULTMASSE to insert the data contained in the pXMLDOC clob parameter in the table pTABLENAME. (You can see the format of the XML document below). The first step on this procedure is to verify that the XML document is not empty. If not, the procedure needs to add a node in the document, in every <ROW> tag. This added node is named “RST_ID”. It’s the foreign key of each record. I can retrieve the value of each <RST_ID> node in an other table in which the data has been previously added (by the calling procedure). When each of the <ROW> elements has been treated, the PARITOP_INSERTXML procedure is called. This procedure uses DBMS_XMLSAVE.INSERTXML to insert the data contained in the XML document in the specified table.
(Below, you can see the code of my procedures.)
With this information, can you tell me why this treatment is very very very slow with a large XML document and how I can improve it?  

Thank you for your help!
Anne-Marie


CREATE OR REPLACE PROCEDURE "PARITOP_TRAITERXMLRESULTMASSE" (
  pPRC_ID           IN PARITOP_PARC.PRC_ID%TYPE,
  pRST_MONDE         IN PARITOP_RESULTAT.RST_MONDE%TYPE,
  pXMLDOC       IN CLOB,
  pTABLENAME    IN VARCHAR2)
AS
/*************************************************************************
 Objectif :Insérer le contenu du XML passé en paramètre (pXMLDOC)  à la table passée en paramètre (pTABLENAME)                   
 La table passée en paramètre doit être une table ayant comme clé étrangère le champs "RST_ID"  .                  
(Le noeud RST_ID est donc ajouté à tous les document XML. Ce rst_id est
 déterminé à partir de la table PARITOP_RESULTAT grâce à pPRC_ID et
 pRstMonde  fournis en paramètre)                                            
*/
  result_doc       CLOB;
  XMLDOMDOC        XDB.DBMS_XMLDOM.DOMDOCUMENT;
  NODE_ROWSET      DBMS_XMLDOM.DOMNODE;
  NODE_ROW         DBMS_XMLDOM.DOMNODE;
  vUE_ID           PARITOP_RESULTAT.UE_ID%TYPE;
  vRST_ID          PARITOP_RESULTAT.RST_ID%TYPE;
  nodeList         DBMS_XMLDOM.DOMNODELIST;
BEGIN
BEGIN
  vUE_ID := 0;
  vRST_ID := 0;
  XMLDOMDOC := DBMS_XMLDOM.NEWDOMDOCUMENT(pXMLDOC);
 
  IF NOT GESTXML_PKG.FN_PARITOP_DOCUMENT_IS_NULL(XMLDOMDOC) THEN
    NODE_ROWSET := DBMS_XMLDOM.item(DBMS_XMLDOM.GETCHILDNODES (DBMS_XMLDOM.MAKENODE(XMLDOMDOC)),0);
   
   for i in 0..dbms_xmldom.getLength(DBMS_XMLDOM.getchildnodes(NODE_ROWSET))-1 loop
      NODE_ROW := DBMS_XMLDOM.ITEM(DBMS_XMLDOM.GETCHILDNODES(NODE_ROWSET), i) ;
      nodeList := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(DBMS_XMLDOM.makeelement(NODE_ROW) , 'UE_ID');
      IF vUE_ID <> DBMS_XMLDOM.GETNODEVALUE(DBMS_XMLDOM.GETFIRSTCHILD(DBMS_XMLDOM.ITEM(nodeList, 0))) THEN
                  
            vUE_ID   := DBMS_XMLDOM.GETNODEVALUE(DBMS_XMLDOM.GETFIRSTCHILD(DBMS_XMLDOM.ITEM(nodeList, 0)));
   
                  --on ramasse le rst_id
                  SELECT RST_ID INTO vRST_ID
                  FROM PARITOP_RESULTAT RST
                                  WHERE RST.PRC_ID      =   pPRC_ID
                                   AND RST.UE_ID        =   vUE_ID
                                   AND RST.RST_MONDE    =   pRST_MONDE
                                   AND RST_A_SUPPRIMER  =   0;
      END IF;                                           
   
            GESTXML_PKG.PARITOP_ADDNODETOROW(XMLDOMDOC, NODE_ROW, 'RST_ID', vRST_ID);
     end loop;

     RESULT_DOC := ' '; --à garder, pour ne pas que ca fasse d'erreur lors du WriteToClob.
     dbms_xmldom.writeToClob(DBMS_XMLDOM.MAKENODE(XMLDOMDOC), RESULT_DOC);

     --Insertion du document XML dans la table "tableName"
     GESTXML_PKG.PARITOP_INSERTXML(RESULT_DOC, pTABLENAME);
     DBMS_XMLDOM.FREEDOCUMENT( XMLDOMDOC);
  end if;

      EXCEPTION
            […exception treatement…]
                END;
END;
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
The format of a XML clob is :
<ROWSET>
<ROW>
<PRC_ID>193</PRC_ID>
<UE_ID>8781</UE_ID>
<VEN_ID>6223</VEN_ID>
<RST_MONDE>0</RST_MONDE>
<CMP_SELMAN>0</CMP_SELMAN>
<CMP_INDICESELECTION>92.307692307692307</CMP_INDICESELECTION>
<CMP_PVRES>94900</CMP_PVRES>
<CMP_PVAJUSTE>72678.017699115066</CMP_PVAJUSTE>
<CMP_PVAJUSTEMIN>72678.017699115095</CMP_PVAJUSTEMIN>
<CMP_PVAJUSTEMAX>72678.017699115037</CMP_PVAJUSTEMAX>
<CMP_PV>148000</CMP_PV>
<CMP_VALROLE>129400</CMP_VALROLE>
<CMP_PVRESECART>4790</CMP_PVRESECART>
<CMP_PVRHAB>101778.01769911509</CMP_PVRHAB>
<CMP_UTILISE>1</CMP_UTILISE>
<CMP_TVM>1</CMP_TVM>
<CMP_PVA>148000</CMP_PVA>
</ROW>

<ROW>
<PRC_ID>193</PRC_ID>
<UE_ID>8781</UE_ID>
<VEN_ID>6235</VEN_ID>
<RST_MONDE>0</RST_MONDE>
<CMP_SELMAN>0</CMP_SELMAN>
<CMP_INDICESELECTION>76.92307692307692</CMP_INDICESELECTION>
<CMP_PVRES>117800</CMP_PVRES>
<CMP_PVAJUSTE>118080</CMP_PVAJUSTE>
<CMP_PVAJUSTEMIN>118080</CMP_PVAJUSTEMIN>
<CMP_PVAJUSTEMAX>118080</CMP_PVAJUSTEMAX>
<CMP_PV>172000</CMP_PV>
<CMP_VALROLE>134800</CMP_VALROLE>
<CMP_PVRESECART>0</CMP_PVRESECART>
<CMP_PVRHAB>147180</CMP_PVRHAB>
<CMP_UTILISE>1</CMP_UTILISE>
<CMP_TVM>1</CMP_TVM>
<CMP_PVA>172000</CMP_PVA>
</ROW>
</ROWSET>
-------------------------------------------------------------------------------------------------------------------------------------------
PARITOP_COMPARABLE TABLE :

  RST_ID               NUMBER(10)                   NOT NULL,
  VEN_ID               NUMBER(10)                   NOT NULL,
  CMP_SELMAN           NUMBER(1)                    NOT NULL,
  CMP_UTILISE          NUMBER(1)                    NOT NULL,
  CMP_INDICESELECTION  FLOAT(53)                    NOT NULL,
  CMP_PVRES            FLOAT(53)                    NULL,
  CMP_PVAJUSTE         FLOAT(53)                    NULL,
  CMP_PVRHAB           FLOAT(53)                    NULL,
  CMP_TVM              FLOAT(53)                    NULL
-------------------------------------------------------------------------------------------------------------------------------------------
ROCEDURE PARITOP_INSERTXML (xmlDoc IN clob, tableName IN VARCHAR2)
AS
   insCtx DBMS_XMLSave.ctxType;
   rowss number;
BEGIN
--permet d'insérer les champs du XML dans la table passée en paramètre.
--il suffit que les champs XML aient le même nom que les champs de la table
BEGIN
      insCtx := DBMS_XMLSave.newContext(tableName); -- get context handle
      DBMS_XMLSAVE.SETDATEFORMAT( insCtx, 'yyyy-MM-dd HH:mm:ss');--attention, case sensitive
      DBMS_XMLSAVE.setIgnoreCase(insCtx, 1);
      rowss := DBMS_XMLSAVE.INSERTXML(insCtx , xmlDoc);
      DBMS_XMLSave.closeContext(insCtx);

      EXCEPTION
      […]
      END;
END;
-------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE      PARITOP_ADDNODETOROW (
      XMLDOMDOC DBMS_XMLDOM.DOMDOCUMENT,
      NODE_ROW      dbms_xmldom.DOMNode,
      NOM_NOEUD VARCHAR2,
      VALEUR_NOEUD VARCHAR2)
AS
--PERMET D'AJOUTER UN NOEUD AVEC 1 SEULE VALEUR DANS une ROW D'UN XML.
--UTILE SURTOUT POUR LES CLÉS ÉTRANGÈRES

domElemAInserer DBMS_XMLDOM.DOMELEMENT;
NODE      dbms_xmldom.DOMNode;
NODE_TMP      dbms_xmldom.DOMNode;
BEGIN
      domElemAInserer  := DBMS_XMLDOM.createElement(XMLDOMDOC, NOM_NOEUD)  ;
      NODE := DBMS_XMLDOM.MAKENODE(domElemAInserer); --cast

      NODE := DBMS_XMLDOM.APPENDCHILD(NODE_ROW,NODE);

      NODE_TMP := DBMS_XMLDOM.MAKENODE(DBMS_XMLDOM.CREATETEXTNODE(XMLDOMDOC, VALEUR_NOEUD ) );


      NODE := DBMS_XMLDOM.APPENDCHILD(NODE,NODE_TMP );

END;
0
Comment
Question by:Modellium
[X]
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
  • 6
  • 4
  • 2
  • +1
16 Comments
 

Author Comment

by:Modellium
ID: 13565175
I was wondering I it could be faster if I use SAX?
How it works with PL/SQL if I want to insert almost all elements of the XML document in a single table?

thanks!
0
 
LVL 48

Expert Comment

by:schwertner
ID: 13567226
A running example:

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 13 14:56:19 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect xml/xml@test

SQL> CREATE TABLE empleados (empid   NUMBER PRIMARY KEY,
  2                          empname VARCHAR2(30),
  3                          empjob  VARCHAR2(30),
  4                          empsal  NUMBER);

Table created.

SQL> create or replace procedure InsertXML(xmlDoc IN VARCHAR2, tableName IN VARCHAR2) is
  2     insCtx DBMS_XMLSave.ctxType;
  3     rows number;
  4   begin
  5      insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
  6      rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document
  7      dbms_output.put_line(to_char(rows) || ' rows inserted');
  8      DBMS_XMLSave.closeContext(insCtx);            -- this closes the handle
  9  end;
 10  /

Procedure created.
SQL> exec InsertXML('<?xml version="1.0"?><ROWSET><ROW num="1"><EMPID>10</EMPID><EMPNAME>Perry Smith
</EMPNAME><EMPJOB>Manager</EMPJOB><EMPSAL>800</EMPSAL></ROW><ROW num="1"><EMPID>20</EMPID><EMPNAME>J
ohn Calvach</EMPNAME><EMPJOB>Principal Support Consultant</EMPJOB><EMPSAL>900</EMPSAL></ROW><ROW num
="1"><EMPID>30</EMPID><EMPNAME>Louis Bald</EMPNAME><EMPJOB>Technical Specialist</EMPJOB><EMPSAL>400<
/EMPSAL></ROW><ROW num="1"><EMPID>40</EMPID><EMPNAME>Anthony Flowers</EMPNAME><EMPJOB>Technical Team
 Leader</EMPJOB><EMPSAL>500</EMPSAL></ROW><ROW num="1"><EMPID>50</EMPID><EMPNAME>George Monk</EMPNAM
E><EMPJOB>Support Consultant</EMPJOB><EMPSAL>200</EMPSAL></ROW></ROWSET>','empleados');

PL/SQL procedure successfully completed.

SQL> select * from empleados;

     EMPID EMPNAME                        EMPJOB
---------- ------------------------------ ------------------------------
    EMPSAL
----------
        10 Perry Smith                    Manager
       800

        20 John Calvach                   Principal Support Consultant
       900

        30 Louis Bald                     Technical Specialist
       400


     EMPID EMPNAME                        EMPJOB
---------- ------------------------------ ------------------------------
    EMPSAL
----------
        40 Anthony Flowers                Technical Team Leader
       500

        50 George Monk                    Support Consultant
       200


SQL>



SQL> select SYS_XMLGEN(empid) FROM empleados;

SYS_XMLGEN(EMPID)
---------------------------------------------------------
<?xml version="1.0"?>
<EMPID>10</EMPID>

<?xml version="1.0"?>
<EMPID>20</EMPID>

<?xml version="1.0"?>
<EMPID>30</EMPID>

<?xml version="1.0"?>
<EMPID>40</EMPID>

SYS_XMLGEN(EMPID)

<?xml version="1.0"?>
<EMPID>50</EMPID>


SQL> select SYS_XMLAGG(SYS_XMLGEN(empid)) FROM empleados;
<?xml version="1.0"?>
<ROWSET>
<EMPID>10</EMPID>
<EMPID>20</EMPID>
<EMPID>30</EM


0
 

Author Comment

by:Modellium
ID: 13567986
Hi schwertner!
Thank you for your answer.
But I do not see where SYS_XMLAGG and SYS_XMLGEN functions could help to improve the execution speed of my PL-SQL code ?

Regards,
Anne-Marie
0
WordPress Tutorial 3: Plugins, Themes, and Widgets

The three most common changes you will make to your website involve the look (themes), the functionality (plugins), and modular elements (widgets).

In this article we will briefly define each again, and give you directions on how to install them.

 
LVL 48

Expert Comment

by:schwertner
ID: 13572406
As you see from this example I share with you my experience.
I came to the conclusion that this approach works fast.
XML due it syntax and structure is creates very long text and only the read procedure is
complex enough. I think this is the reason for the delay you experience.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13574201
See if this sample will work for you.  It seems to do the same thing you describe you need.  I haven't tested it on a large dataset so I can't guarentee it will be faster.  I also haven't checked it for possible memory leaks.  I have a procedure that would never free up memory.  It came down to a 'feature' that every time I would call "xmlparser.newParser", it would not automatically free up the memory from the old parser.  I had to explicitly call "freeparser".  So, when running against a large XML file, watch memory.  This may be a cause of the slowdown.

Just a couple of things:   I've heard talk that 9.2.0.5 is buggy.  If possible I would suggest mving to 9.2.0.6.  Also, we do a lot with XML and I jumped to 10g due to the fact that performance seems A LOT faster.  I've heard rumors that all of XDB was completely re-written.

Here's my testcase give it a try and let me know:
------------------------------------------------------------
drop table tab1;
 
create table tab1(
      col1 number,
      col2 varchar2(100),
      col3 varchar2(100)
)
/

drop table tab2;
 
create table tab2(
      col1 number,
      col2 varchar2(100)
)
/

insert into tab2 values(123,'Lookup value from tab2 for 123');
insert into tab2 values(456,'Lookup value from tab2 for 456');
commit;

create or replace procedure xmlinsert( xmlDoc IN clob) is
      insCtx DBMS_XMLSave.ctxType;
      rows number;

      doc dbms_xmldom.DOMDocument;
      Elem dbms_xmldom.DOMElement;
      node dbms_xmldom.DOMNode;
      node1 dbms_xmldom.DOMNode;
      Text                        dbms_xmldom.DOMText;
      NodeList                  dbms_xmldom.DOMNodeList;
      result_doc varchar2(32000);
      row_counter      number;
      num_nodes      number;
      junk varchar2(100);
      node_value varchar2(100);
begin

      doc := dbms_xmldom.newdomdocument(xmlDoc);
      dbms_xmldom.setVersion(doc, '1.0');
      NodeList := dbms_xmldom.getElementsByTagName(doc, 'tab1');
      num_nodes := dbms_xmldom.getLength(NodeList);

      for row_counter in 0..num_nodes-1 loop
            node := dbms_xmldom.item(NodeList, row_counter);
            doc := dbms_xmldom.makeDocument(node);

            node1 := dbms_xslprocessor.selectSingleNode(Node,'col1');
            node1 := dbms_xmldom.getFirstChild(node1);

        node_value := dbms_xmldom.getNodeValue(node1);
        select col2 into junk from tab2 where col1 = node_value;

            Elem := dbms_xmldom.createElement( doc, 'col2' );
            node := dbms_xmldom.appendChild(node,dbms_xmldom.makeNode(Elem));
            Text := dbms_xmldom.createTextNode(doc,junk);
            node := dbms_xmldom.appendChild(node,dbms_xmldom.makeNode(Text));
      end loop;

      dbms_xmldom.writeToBuffer(doc, result_doc);
 
      insCtx := DBMS_XMLSave.newContext('tab1');
      DBMS_XMLSave.SetIgnoreCase(insCtx,1);
      rows := DBMS_XMLSave.insertXML(insCtx, result_doc);
      DBMS_XMLSave.closeContext(insCtx);

end;
/

show errors

select * from tab1;

call xmlinsert('
<row>
<tab1>
      <col1>123</col1>
      <col3>Some Text</col3>
</tab1>
<tab1>
      <col1>456</col1>
      <col3>Some more Text</col3>
</tab1>
</row>
');

select * from tab1;

0
 

Author Comment

by:Modellium
ID: 13604223
Hi !
Thank you for your answer! I tried your exemple. It works well and fast when the XML document have few <tab1> elements.  When I use a larger dataset (with 23 027 <tab1> elements), It takes 9minutes 23 seconds to execute... :(

To run your exemple with a big dataset, I change
result_doc varchar2(32000);          in       result_doc clob;
dbms_xmldom.writeToBuffer(doc, result_doc);    in     dbms_xmldom.writeToclob(DBMS_XMLDOM.MAKENODE(doc), RESULT_DOC);    

and I loaded the large XML document from a file using the dbms_lob.LOADCLOBFROMFILE procedure.

The average size of my XML documents have 20 000 "records"...

Regards,
Anne-Marie
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13604444
Did you watch the process memory as it ran?

I know there were issues with the DOM in some early releases but thought those were resolved.  I forget the exact thing I was trying to do at the time but was informed that each time I looked for a specific offset in a list Oracle started each time at the top of the doc.

For example:
when I asked for node(1) Oracle would open the doc, jump to the top and loop until it finds subscript 1 which is fast.
things slowed down when I aasked for node(1000) and for you asking for node(20000).

I agree that SAX would be faster but don't think you will be able to use it since you are adding a node.

Do you have to use DBMS_XMLSave.insertXML?  If not then you might be able to use SAX and insert the rows as you find them (I probably won't be able to help since I've never used SAX).

I'll continue looking while you absorb this.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13605067
The issue is with the insertXML call and I'm betting it's the issue I described above.

I create an xml file with 23760 nodes as descirbed above.  I just kept copying:
<tab1>
     <col1>123</col1>
     <col3>Some Text</col3>
</tab1>
<tab1>
     <col1>456</col1>
     <col3>Some more Text</col3>
</tab1>

Mine takes about 9-10 minutes using 10g and a desktop machine:
I modified my example to read the clob from the fielsystem display some timings and here's what I received (modified code sample to follow):

Starting: 03/22/2005 14:50:52
File loaded into clob: 03/22/2005 14:50:53
Declaring new DOM doc: 03/22/2005 14:50:53
getting tab1 tags: 03/22/2005 14:50:53
start loop: 03/22/2005 14:50:55
end loop: 03/22/2005 14:51:27
start writing xml doc to clob: 03/22/2005 14:51:27
end writing xml doc to clob: 03/22/2005 14:51:27
start call to insertxml: 03/22/2005 14:51:27
end call to insertxml: 03/22/2005 15:01:56

PL/SQL procedure successfully completed.

Elapsed: 00:11:04.00

modified example code:
-----------------------------------------------------

drop table tab1;
 
create table tab1(
     col1 number,
     col2 varchar2(100),
     col3 varchar2(100)
)
/

drop table tab2;
 
create table tab2(
     col1 number,
     col2 varchar2(100)
)
/

insert into tab2 values(123,'Lookup value from tab2 for 123');
insert into tab2 values(456,'Lookup value from tab2 for 456');
commit;

drop directory xml_dir;
create directory xml_dir as 'C:\';

create or replace procedure xmlinsert( v_file_name IN varchar2) is
      v_bfile                   bfile := BFILENAME( 'XML_DIR' , v_file_name);
      xmlDoc                   clob;

      src_offset  number := 1;
      dst_offset  number := 1;
      cs_id       number := NLS_CHARSET_ID('UTF8'); /* 998 */
      lang_ctx    number := dbms_lob.default_lang_ctx;
      warning     number;


     insCtx DBMS_XMLSave.ctxType;
     rows number;

     doc dbms_xmldom.DOMDocument;
     Elem dbms_xmldom.DOMElement;
     node dbms_xmldom.DOMNode;
     node1 dbms_xmldom.DOMNode;
     Text                    dbms_xmldom.DOMText;
     NodeList               dbms_xmldom.DOMNodeList;
     result_doc clob;
     row_counter     number;
     num_nodes     number;
     junk varchar2(100);
     node_value varchar2(100);
begin

dbms_output.put_line('Starting: ' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
      dbms_lob.fileopen(v_bfile, DBMS_LOB.FILE_READONLY);
      dbms_lob.createtemporary(xmlDoc,TRUE);
      dbms_lob.open(xmlDoc,dbms_lob.lob_readwrite);
      dbms_lob.loadclobfromfile(xmlDoc, v_bfile, DBMS_LOB.GETLENGTH(v_bfile), dst_offset, src_offset, cs_id, lang_ctx,warning);
      dbms_lob.close(xmlDoc);
      dbms_lob.close(v_bfile);
dbms_output.put_line('File loaded into clob: ' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));




dbms_output.put_line('Declaring new DOM doc: ' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
     doc := dbms_xmldom.newdomdocument(xmlDoc);
     dbms_xmldom.setVersion(doc, '1.0');
dbms_output.put_line('getting tab1 tags: ' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
     NodeList := dbms_xmldom.getElementsByTagName(doc, 'tab1');
     num_nodes := dbms_xmldom.getLength(NodeList);

dbms_output.put_line('start loop: ' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
     for row_counter in 0..num_nodes-1 loop
          node := dbms_xmldom.item(NodeList, row_counter);
          doc := dbms_xmldom.makeDocument(node);

          node1 := dbms_xslprocessor.selectSingleNode(Node,'col1');
          node1 := dbms_xmldom.getFirstChild(node1);

        node_value := dbms_xmldom.getNodeValue(node1);
        select col2 into junk from tab2 where col1 = node_value;

          Elem := dbms_xmldom.createElement( doc, 'col2' );
          node := dbms_xmldom.appendChild(node,dbms_xmldom.makeNode(Elem));
          Text := dbms_xmldom.createTextNode(doc,junk);
          node := dbms_xmldom.appendChild(node,dbms_xmldom.makeNode(Text));
     end loop;
dbms_output.put_line('end loop: ' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));


dbms_output.put_line('start writing xml doc to clob: ' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
      dbms_lob.createtemporary(result_doc,TRUE);
      dbms_lob.open(result_doc,dbms_lob.lob_readwrite);
      xmldom.writeToClob(doc, result_doc);
      dbms_lob.close(result_doc);
dbms_output.put_line('end writing xml doc to clob: ' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));

 
dbms_output.put_line('start call to insertxml: ' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
     insCtx := DBMS_XMLSave.newContext('tab1');
     DBMS_XMLSave.SetIgnoreCase(insCtx,1);
     rows := DBMS_XMLSave.insertXML(insCtx, result_doc);
     DBMS_XMLSave.closeContext(insCtx);
dbms_output.put_line('end call to insertxml: ' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));

      dbms_lob.freetemporary(result_doc);
end;
/

show errors

select count(*) from tab1;

exec xmlinsert('q.xml');

select count(*) from tab1;



0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14420780
suggest 90-10 split (slightwv-schwertner).  sry schwertner but I don't feel your post is complete enough to help answer the question.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14430448
Do not delete.  I believe there is some useful information here.

I also had some open questions that were not answered.  I had not fully exhausted ways of possibly solving the performance issue for Modellium.

With no continued feedback from Modellium it is impossible to continue assistance.

I will wait for the requested feedback from Modellium before I can recommend a point refund.
 
0
 

Author Comment

by:Modellium
ID: 14430855
Hi,
for this problem with DBMS_XMLSAVE.INSERTXML, I didn't find any solution which improve the speed.
Slightwv suggested that the problem came from "DBMS_XMLSAVE.INSERTXML" method when the clob object contains a large number of XML elements . I agree with him, but no solution have been brought to solve it.
I read for SAX solution, but it doesn't apply as easily as the  "DBMS_XMLSAVE.INSERTXML" would (because my XML documents are made with the same definition as the tables definitions).

The short term solution I use is to call a different procedure for each and every documents I want to insert in a table. In those procedures,  I put each and every values in a single variable, and I do the Insert statement with those variable. This solution is VERY VERY fast (but not generic) compared to the DBMS_XMLSAVE.INSERTXML procedure. Here is an example :
----------------------
CREATE OR REPLACE PROCEDURE PARITOP_PUTCOMPARABLE_XML
(
      pPRC_ID                  IN PARITOP_PARC.PRC_ID%TYPE,
      pRST_MONDE            IN PARITOP_RESULTAT.RST_MONDE%TYPE,
      pDATA_COMPARABLE            IN CLOB
) IS

  vRST_ID            PARITOP_RESULTAT.RST_ID%TYPE;
  vUE_ID            PARITOP_RESULTAT.UE_ID%TYPE;
  vVEN_ID            PARITOP_VARAJUSTCOMP.VEN_ID%TYPE;
  vVJC_AJUSTMAX      PARITOP_VARAJUSTCOMP.VJC_AJUSTMAX%TYPE;
  vVJC_AJUSTMIN      PARITOP_VARAJUSTCOMP.VJC_AJUSTMIN%TYPE;
  vVJC_AJUST      PARITOP_VARAJUSTCOMP.VJC_AJUST%TYPE;
  vVAR_ID            PARITOP_VARAJUSTCOMP.VAR_ID%TYPE;

--pour traiter le XML :
  XMLDOMDOC      DBMS_XMLDOM.DOMDocument;
  nodeList            DBMS_XMLDOM.DOMNODELIST;
  tmpNode            DBMS_XMLDOM.DOMNode;
  grpDomElement      DBMS_XMLDOM.DOMElement;
  DomElem            DBMS_XMLDOM.DOMElement;
  dnnm            DBMS_XMLDOM.DOMNamedNodeMap;
  rowNode            DBMS_XMLDOM.DOMNode;

BEGIN
--INSERTION DES COMPARABLES ------------------------------------------------------

  XMLDOMDOC := DBMS_XMLDOM.NEWDOMDOCUMENT(pDATA_COMPARABLE);
  domElem := DBMS_XMLDOM.GETDOCUMENTELEMENT(XMLDOMDOC); --domElem du doc passé en paramètre
  tmpNode := DBMS_XMLDOM.MAKENODE(domElem);
  NodeList := DBMS_XMLDOM.GETCHILDNODES(tmpNode);

  tmpNode := dbms_xmldom.item(nodeList, 1);--sera le noeud <rs:data>
  NodeList := DBMS_XMLDOM.GETCHILDNODES(tmpNode);

  for i in 0..dbms_xmldom.getLength(NodeList)-1 loop --parcourir ce qu'il y a dans chaque row du rs
      rowNode := DBMS_XMLDOM.item(nodeList, i);
      dnnm := DBMS_XMLDOM.getAttributes(rowNode);   --positionné sur les attributs de la ligne

      tmpNode := DBMS_XMLDOM.getNamedItem(dnnm, 'UE_ID');
      vUE_ID := dbms_xmldom.getNodeValue(tmpNode);

      tmpNode := DBMS_XMLDOM.getNamedItem(dnnm, 'VEN_ID');
      vVEN_ID := dbms_xmldom.getNodeValue(tmpNode);

      tmpNode := DBMS_XMLDOM.getNamedItem(dnnm, 'CMP_SELMAN');
      vCMP_SELMAN   := dbms_xmldom.getNodeValue(tmpNode);

      tmpNode := DBMS_XMLDOM.getNamedItem(dnnm, 'CMP_UTILISE');
      vCMP_UTILISE := dbms_xmldom.getNodeValue(tmpNode);

      tmpNode := DBMS_XMLDOM.getNamedItem(dnnm, 'CMP_INDICESELECTION');
      vCMP_INDICESELECTION := dbms_xmldom.getNodeValue(tmpNode);

      tmpNode := DBMS_XMLDOM.getNamedItem(dnnm, 'CMP_PVRES');
      vCMP_PVRES := dbms_xmldom.getNodeValue(tmpNode);

      tmpNode := DBMS_XMLDOM.getNamedItem(dnnm, 'CMP_PVAJUSTE');
      vCMP_PVAJUSTE := dbms_xmldom.getNodeValue(tmpNode);


      tmpNode := DBMS_XMLDOM.getNamedItem(dnnm, 'CMP_PVRHAB');
      vCMP_PVRHAB := dbms_xmldom.getNodeValue(tmpNode);

      tmpNode := DBMS_XMLDOM.getNamedItem(dnnm, 'CMP_TVM');
      vCMP_TVM := dbms_xmldom.getNodeValue(tmpNode);

      SELECT RST_ID INTO vRST_ID
      FROM PARITOP_RESULTAT
      WHERE UE_ID = vUE_ID
      and PRC_ID = pPRC_ID
      AND RST_MONDE = pRST_MONDE
      AND RST_A_SUPPRIMER = 0;


      INSERT INTO PARITOP_COMPARABLE(RST_ID, VEN_ID, CMP_SELMAN, CMP_UTILISE, CMP_INDICESELECTION,       CMP_PVRES, CMP_PVAJUSTE, CMP_PVRHAB, CMP_TVM)
      VALUES(vRST_ID, vVEN_ID, vCMP_SELMAN, vCMP_UTILISE,       vCMP_INDICESELECTION,vCMP_PVRES,vCMP_PVAJUSTE, vCMP_PVRHAB, vCMP_TVM );

  end loop;

  DBMS_XMLDOM.FREEDOCUMENT(XMLDOMDOC);
---------------------------------------------------------------------
XML format (XML generated by the save procedure of an ado.recordset object)

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
      <s:Schema id="RowsetSchema">
            <s:ElementType name="row" content="eltOnly" rs:updatable="true">
                  <s:AttributeType name="PRC_ID" rs:number="1" rs:write="true">
                        <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="UE_ID" rs:number="2" rs:write="true">
                        <s:datatype dt:type="int" dt:maxLength="4" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="VEN_ID" rs:number="3" rs:write="true">
                        <s:datatype dt:type="int" dt:maxLength="4" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="RST_MONDE" rs:number="4" rs:write="true">
                        <s:datatype dt:type="ui1" dt:maxLength="1" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_SELMAN" rs:number="5" rs:write="true">
                        <s:datatype dt:type="boolean" dt:maxLength="2" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_INDICESELECTION" rs:number="6" rs:write="true">
                        <s:datatype dt:type="float" dt:maxLength="8" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_PVRES" rs:number="7" rs:write="true">
                        <s:datatype dt:type="float" dt:maxLength="8" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_PVAJUSTE" rs:number="8" rs:nullable="true" rs:write="true">
                        <s:datatype dt:type="float" dt:maxLength="8" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_PVAJUSTEMIN" rs:number="9" rs:nullable="true" rs:write="true">
                        <s:datatype dt:type="float" dt:maxLength="8" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_PVAJUSTEMAX" rs:number="10" rs:nullable="true" rs:write="true">
                        <s:datatype dt:type="float" dt:maxLength="8" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_PV" rs:number="11" rs:write="true">
                        <s:datatype dt:type="float" dt:maxLength="8" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_VALROLE" rs:number="12" rs:write="true">
                        <s:datatype dt:type="float" dt:maxLength="8" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_PVRESECART" rs:number="13" rs:write="true">
                        <s:datatype dt:type="float" dt:maxLength="8" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_PVRHAB" rs:number="14" rs:nullable="true" rs:write="true">
                        <s:datatype dt:type="float" dt:maxLength="8" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_UTILISE" rs:number="15" rs:write="true">
                        <s:datatype dt:type="boolean" dt:maxLength="2" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_TVM" rs:number="16" rs:write="true">
                        <s:datatype dt:type="float" dt:maxLength="8" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:AttributeType name="CMP_PVA" rs:number="17" rs:write="true">
                        <s:datatype dt:type="float" dt:maxLength="8" rs:precision="0" rs:fixedlength="true" rs:maybenull="false"/>
                  </s:AttributeType>
                  <s:extends type="rs:rowbase"/>
            </s:ElementType>
      </s:Schema>
      <rs:data>
            <z:row PRC_ID="9" UE_ID="136393" VEN_ID="120413" RST_MONDE="0" CMP_SELMAN="0" CMP_INDICESELECTION="40.923076923076898" CMP_PVRES="34524" CMP_PVAJUSTE="34620" CMP_PVAJUSTEMIN="34620" CMP_PVAJUSTEMAX="34620" CMP_PV="36000" CMP_VALROLE="39700" CMP_PVRESECART="0" CMP_PVRHAB="34620" CMP_UTILISE="1" CMP_TVM="0.95899999999999996" CMP_PVA="34524"/>
            <z:row PRC_ID="9" UE_ID="136393" VEN_ID="63024" RST_MONDE="0" CMP_SELMAN="0" CMP_INDICESELECTION="0" CMP_PVRES="42500" CMP_PVAJUSTE="42909" CMP_PVAJUSTEMIN="42909" CMP_PVAJUSTEMAX="42909" CMP_PV="42500" CMP_VALROLE="54000" CMP_PVRESECART="0" CMP_PVRHAB="42909" CMP_UTILISE="1" CMP_TVM="1" CMP_PVA="42500"/>
      </rs:data>
</xml>

 
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14430964
Thanks for the feedback.  Glad you came up with your own work around.

>>but no solution have been brought to solve it.

You never posted back.  Therefore, we never had a chance to continue assisting you in this effort.  Just to add one final comment before we end this thread:  You might also look into using the SQL functions like XMLSEQUENCE.  You might be able to do away with the DOM all together.

Lunchy,
I have no issue with a PAQ/refund.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 14457837
Closed, 500 points refunded.
modulo
Community Support Moderator
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
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
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

762 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