4 Replies Latest reply: Jan 7, 2013 4:43 AM by 983173 RSS

    XMLDOM.appendChild performance

    983173
      Hi All,
      I have a large xml (52,000 messages under the root element). There's about 5 elements under each Message element. Parsing each element takes about 2 minutes which is great. However, when I try to add two more elements under the message element using XMLDOM.appendChild , the execution time goes up to 11 minutes.
      DB Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
      Any ideas on how to make it run faster?
      Is there an issue with XMLDOM.appendChild
        • 1. Re: XMLDOM.appendChild performance
          odie_63
          Hi,
          Any ideas on how to make it run faster?
          Is there an issue with XMLDOM.appendChild
          Actually, there's an issue with the whole approach.
          Given your db version, you should stop using DOM and start using binary XMLType and related functionalities.

          Could you describe a little bit more what you want to do? A sample data with expected output would help.

          - What does "parsing" mean to you? Extracting XML data into variables or columns?
          - Do you want to add two more elements under each of the 52000 Messages?


          Here's a glimpse of what you can do :
          Connected to:
          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options
          
          SQL> create table tmp_xml of xmltype;
          
          Table created.
          Creating a sample document with 10000 messages...
          SQL> set timing on
          SQL> insert into tmp_xml
            2  select xmlelement("root",
            3           xmlagg(
            4             xmlelement("message",
            5               xmlforest(
            6                 'A'||to_char(level, 'fm09999') as "item1"
            7               , 'B'||to_char(level, 'fm09999') as "item2"
            8               , 'C'||to_char(level, 'fm09999') as "item3"
            9               , 'D'||to_char(level, 'fm09999') as "item4"
           10               , 'E'||to_char(level, 'fm09999') as "item5"
           11               )
           12             )
           13           )
           14         )
           15  from dual
           16  connect by level <= 10000 ;
          
          1 row created.
          
          Elapsed: 00:00:00.56
          SQL>
          Parsing into a relational structure...
          SQL> select x.*
            2  from tmp_xml t
            3     , xmltable(
            4         '/root/message'
            5         passing t.object_value
            6         columns item1 varchar2(6) path 'item1'
            7               , item2 varchar2(6) path 'item2'
            8               , item3 varchar2(6) path 'item3'
            9               , item4 varchar2(6) path 'item4'
           10               , item5 varchar2(6) path 'item5'
           11       ) x
           12  where rownum <= 10 ;
          
          ITEM1  ITEM2  ITEM3  ITEM4  ITEM5
          ------ ------ ------ ------ ------
          A00001 B00001 C00001 D00001 E00001
          A00002 B00002 C00002 D00002 E00002
          A00003 B00003 C00003 D00003 E00003
          A00004 B00004 C00004 D00004 E00004
          A00005 B00005 C00005 D00005 E00005
          A00006 B00006 C00006 D00006 E00006
          A00007 B00007 C00007 D00007 E00007
          A00008 B00008 C00008 D00008 E00008
          A00009 B00009 C00009 D00009 E00009
          A00010 B00010 C00010 D00010 E00010
          
          10 rows selected.
          
          Elapsed: 00:00:00.37
          SQL>
          Appending two more children to each message using XQuery Update (11.2.0.3 only) :
          SQL> update tmp_xml t
            2  set t.object_value =
            3         xmlquery(
            4           'copy $d := .
            5            modify (
            6              for $i in $d/root/message
            7              return insert node $e into $i
            8            )
            9            return $d'
           10          passing t.object_value
           11               ,  xmlconcat(
           12                    xmlelement("item6")
           13                  , xmlelement("item7")
           14                  ) as "e"
           15          returning content
           16         )
           17  ;
          
          1 row updated.
          
          Elapsed: 00:00:04.25
          SQL>
          SQL> set long 500
          SQL> set pages 100
          SQL> select object_value from tmp_xml ;
          
          OBJECT_VALUE
          --------------------------------------------------------------------------------
          <root>
            <message>
              <item1>A00001</item1>
              <item2>B00001</item2>
              <item3>C00001</item3>
              <item4>D00001</item4>
              <item5>E00001</item5>
              <item6/>
              <item7/>
            </message>
            <message>
              <item1>A00002</item1>
              <item2>B00002</item2>
              <item3>C00002</item3>
              <item4>D00002</item4>
              <item5>E00002</item5>
              <item6/>
              <item7/>
            </message>
            <message>
              <item1>A00003</item1>
              <item2>B00003</item2>
              <item3>C00003</item3>
              <item4>D00003</item4>
              <item5>E000
          
          
          Elapsed: 00:00:00.14
          Edited by: odie_63 on 7 janv. 2013 00:12
          • 2. Re: XMLDOM.appendChild performance
            983173
            Hi,
            Thanks for the quick reply.
            The input to my proc is this xml sent in as a blob.
            I have to extract the information in the 52000 messages, check each message against a table and perform insert/updates. The result of the insert update will be 2 values which is added to the xml and sent out as a clob.

            I used XMLType in 10g for the same XML. It took about an hour just to parse the elements. It used to get slower as the number of elements increase.
            • 3. Re: XMLDOM.appendChild performance
              odie_63
              I used XMLType in 10g for the same XML. It took about an hour just to parse the elements. It used to get slower as the number of elements increase.
              As far as XML is concerned, your current db version provides a lot more functionalities and performance than 10g.

              Try the approach I showed, first store the XML document into a (temp) XMLType table and do the parsing from there, you'll be surprised.

              If you'd care to post a clear test case, I'd be glad to show you how to get the best performance for your requirement.

              BTW, you may be interested in posting in the {forum:id=34} forum instead.

              Edited by: odie_63 on 7 janv. 2013 10:53
              • 4. Re: XMLDOM.appendChild performance
                983173
                Created a thread under XML DB XMLDOM.appendChild performance