Parsing XML flat file performance issues

[mikedev]

Hi first time posting to any forum so apologies if I do anything wrong.

We have implemented the following technique for parsing a XML flat file.

http://www.oracle-base.com/articles/8i/ ... nts-8i.php

I was wondering if there is any way to reduce the time that the FOR LOOP takes to add new records to the table collection.
What I’m experiencing, I’m going to use employee as an example form the article. If I process 35 employees the average time it takes the FOR LOOP to process is around 1.5 seconds per employee. If we process 120 employees in a file the average time jumps to over 5 seconds an employee. So when we get to processing around 500 employees the process is taking about 4 ½ hours to complete.

When I did my test remove the insert to Oracle table code.

We are using 8i database.

Is that just the natural of the beast or can I do anything to improve the loading of the table collection?

Thanks in advance for any help that is provide.

[Tim...]

Hi.

It is unfortunate that parsing of XML using a DOM parser is done entirely in memory. To do this, the whole DOM tree has to be resolved in to memory in one go. As documents get bigger, the effort to parse them gets proportionately bigger, making them even slower.

For very large documents, DOM parsers are very inefficient and people tend to use event-based SAX parsers. Oracle do not support SAX parsers from PL/SQL.

The trick is to make the XML as small as possible. It is better to use attribute-based XML, rather than tag based, as it reduces the total size of the documents.

Code: Select all

Tag-Based:
<employee><id>1234</id><name>Tim Hall</name></employee>

Attribute-Based
<employee id="1234" name="Tim Hall" />

Much of the XML functionality in 8i is implemented in Java. In 9i and above it is implemented in the kernel (written in C) and is typically much faster.

Cheers

Tim...

[mikedev]

Thank you Tim!

You have been very helpful.

[Tim...]