CODEWITHSUNDEEP
phpmysqlxml
rob melino
rob melino

Reputation: 781

writing large xml file to mysql table is too slow possibly due to foreach loop?

Hoping for some assistance here. I have a large xml file (called xml-file.xml in the code above). The problem I am having with the code above is that it is very slow to write everything to the database. I think the reason it is slow is because of the section that loops through the movies and writes only the movie name in a comma separated string to the table. My question - is there a more efficient way to convert the movie names to a comma separated string and then write to the table? Thanks in advance for any help!

The XML Markup:

    <users>
      <user>
        <id>7280462</id>
        <name>John</name>
        <movies>
          <name>
            <![CDATA[Jack]]>
          </name>
          <description>
            <![CDATA[comedy]]>
          </description>
          <name>
            <![CDATA[Superman]]>
          </name>
          <description>
            <![CDATA[action]]>
          </description>
          <name>
            <![CDATA[Ace Venture]]>
          </name>
          <description>
            <![CDATA[comedy]]>
          </description>
          <name>
            <![CDATA[Major League]]>
          </name>
          <description>
            <![CDATA[sports]]>
          </description>
        </movies>
      </user>
    </users>

My PHP code:

    $file = 'xml-file.xml';
    $users = simplexml_load_file($file);

    $num_rows = count($users);
    for ($j=0; $j < $num_rows; $j++) { 
        $userid = $users->user[$j]->id;
        $name = $users->user[$j]->name;

        //update table with userid and name
        $db->exec("INSERT INTO table (userid, name) VALUES ('$userid', '$name')");

            //loop through movies and write only movie name to table as comma separated string
            foreach ($users->user$j]->movies as $element) {
              foreach($element as $key => $val) {
                  if ($key != 'description') {
                       //echo "{$key}: {$val}";
                       $title = trim($val).',';
                      $db->exec("UPDATE table set movies = concat(movies,'','$title') where userid = '$userid'");
                    }
              }
            }

    }

Upvotes: 0

Views: 458

Answers (2)

andrewsi
andrewsi

Reputation: 10732

How about this:

for ($j=0; $j < $num_rows; $j++) { 
    $userid = $users->user[$j]->id;
    $name = $users->user[$j]->name;
    $titles = "";

    foreach ($users->user$j]->movies as $element) {
        foreach($element as $key => $val) {
            if ($key != 'description') {
                   $titles .= trim($val).',';
            }
        }
    }

    $titles = trim($titles, ",");

    $db->exec("INSERT INTO table (userid, name, movies) VALUES ('$userid', '$name','$titles')");

 }

It's generating all the SQL in one go, so you just need a single statement.

Upvotes: 1

Ed Heal
Ed Heal

Reputation: 59997

First retaionalise your database. This will avoid using concat etc. Check out Google for that.

You need to ensure that your queries are aware of any indexes or if there is not an index create one.

The EXPLAIN keyword in MySql is your friend here. It will tell you what is or is not happening.

In addition change the format of the XML and use DOM.

Have a tag "movie" that just contains the details about A movie.

i.e.

<movies>
    <movie>
       <name>Jack</name>
       <description>Comedy</descript>
     </movie>
     <movie>
        <name>Superman</name>
        <description>Action</description>
     </movie>
  ....
</movies>

Upvotes: 1

Related Questions