CODEWITHSUNDEEP
xmloracle-databaseplsql
user3656368
user3656368

Reputation: 21

what is faster... DBMS_XMLDOM vs appendchildxml

I need to create a very large xml file (up to 100k nodes) in PL-SQL. Which way is faster ? DBMS_XMLDOM or appendchildxml ?

Upvotes: 2

Views: 566

Answers (1)

Pancho
Pancho

Reputation: 2193

We likewise use Oracle 11g R2, and we create an XML document for reporting purposes. The entire process ie. retrieval from the database, restructuring of the data and assembly the output XML document, was taking 130 seconds.

We assumed that the first phase, ie. the retrieval, would probably be taking the bulk of the time so we optimised this and reduced the total time by 40 seconds down to around 90 seconds.

We were surprised that it still remained "slow" - so we investigated the restructuring and assembly phases, and to our surprise found that the bulk of the time was being taken by the assembly phase, with each appendchildxml statement getting successively slower as the document grew.

We therefore stripped out the appendchildxml and replaced it entirely with dbms_xmldom code.

Happily, this change reduced the entire process to only 9 seconds ie. an order of magnitude reduction.

Based on this and other performance gains we have seen on the database with dbms_xmldom I would recommend using dbms_xmldom.

Upvotes: 1

Related Questions