Decrease processing time in parsing large xml file in php

Question

I have this problem in terms of processing time of a large xml files. By large, i mean 600MB on the average. Currently, It takes about 50 - 60 minutes to parse and insert the data into a database. I would like to ask for suggestions on how can I improve the processing time? Like goind down to 20 minutes.

Because with the current time it will take me 2.5 months to populate the database with the content from the xml. By the way I have 3000+ xml files with the average of 600mb. And my php script in command line thru cron job.

I have also read other questions like the one below, but I have not found any idea yet. What is the fastest XML parser in PHP?

I see that some have parsed files up to 2GB. I wonder how long are the processing time.

I hope you guys could lend your help. It would be much appreciated. Thanks.

I have this code:

$handler = $this;
$parser = xml_parser_create('UTF-8');
xml_set_object($parser, $handler);
xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, false);
xml_set_element_handler($parser, "startElement", "endElement");
xml_set_character_data_handler($parser, "cdata");

$fp = fopen($xmlfile, 'r');

while (!feof($fp)) {
    while (($data = fread($fp, 71680))){

    }
}

I put first the parse data in a temporary array. My mysql insert commands are inside the endElement function. There is a specific closing tag to trigger my insert command to the database.

Thanks for the response....

Answer 1

Without seeing any code, the very first thing I have to suggest is NOT to use either DOM or SimpleXMLElement as these load the whole thing into memory.

You need to use a stream parser like XMLReader.

---

EDIT:

Since you are already using a stream parser, you aren't going to get huge gains from changing parsers (I honestly don't know the difference in speed between XML Parser and XMLReader, since the latter uses libxml, it may be better but probably not worth it).

Next thing to look at is whether you're doing anything silly in your code; for that we'd need to see a more substantial overview of how you've implemented this.

You say you are putting data in a temporary array and calling MySQL insert once you reach a closing tag. Are you using prepared statements? Are you using transactions to do multiple inserts in bulk?

The right way to get at your bottleneck though is to run a profiler over your code. My favourite tool for the job is xhProf with XHGui. This will tell you what functions are running, how many times, for how long and how much memory they consume (and can display it all in a nice call-graph, very useful).

Use the instructions on that GitHub's README. Here's a tutorial and another useful tutorial (bear in mind this last one is for the profiler without the XHGui extensions that I linked to).

Answer 2

You only seem to need to parse and read the data and not edit the XML. With this mind, I would say using a SAX parser is the easier and faster way to do this.

SAX is an approach to parse XML documents, but not to validate them. The good thing is that you can use it with both PHP 4 and PHP 5 with no changes. In PHP 4, the SAX parsing is already available on all platforms, so no separate installation is necessary.

You basically define a function to be run when a start element is found and another to be run when an end element is found (you can also use one for attributes). And then you do whatever you want with the parsed data.

Parsing XML with SAX

<?
function start_element($parser, $element_name, $element_attrs) {
  switch ($element_name) {
    case 'KEYWORDS':
       echo '<h1>Keywords</h1><ul>';
       break;
    case 'KEYWORD':
       echo '<li>';
       break;
  }
}

function end_element($parser, $element_name) {
  switch ($element_name) {
    case 'KEYWORDS':
       echo '</ul>';
       break;
    case 'KEYWORD':
       echo '</li>';
       break;
  }
}


function character_data($parser, $data) {
  echo htmlentities($data);
}

$parser = xml_parser_create();
xml_set_element_handler($parser, 'start_element', 'end_element');
xml_set_character_data_handler($parser, 'character_data');

$fp = fopen('keyword-data.xml', 'r')
    or die ("Cannot open keyword-data.xml!");


while ($data = fread($fp, 4096)) {
  xml_parse($parser, $data, feof($fp)) or
   die(sprintf('XML ERROR: %s at line %d',
        xml_error_string(xml_get_error_code($parser)),
        xml_get_current_line_number($parser)));
}


xml_parser_free($parser);   
?>

Source: I worked on parsing and processing large amounts of XML data. EDIT: Better example

EDIT: Well, apparently you are already using a Sax Parser. As long as you are actually processing the file in an event driven way (Not having any additional overhead) you should be at top performance in this department. I would say there is nothing you can do to increase the parsing performance. If you are having performance issues I would suggest looking at what you are doing in your code to find performance bottlenekcs (Try using a php profiler like this one ). If you post your code here, we could give it a look! Cheers!

Answer 3

I have spent the last day or so tackling the same problem. I noticed that limiting the number of insert queries reduced the processing time quite significantly. You might have already done this but try collecting a batch of parsed data into a suitable data structure (I am using a simple array, but maybe a more suitable data structure could further reduce the cost?). Upon a collection of X sets insert the data in one go (INSERT INTO table_name (field_name) VALUES (set_1, set_2, set_n) )

Hope this helps anyone who might stumble upon this page. I am still working out other bottlenecks, if I find something new I will post it here.