Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have a project in which I create an array of 200k+ lines w/ 25 fields that run the wp_insert_post and update_post_meta. To start it processes pretty fast the slows down a lot after 10k records or so.

Is there a more efficient way to do this? Is there a MySQL stored procedure that exists so I can create these posts directly using SQL queries and skip the PHP?

echo "get list of posts to be created in wordpress \n";
//RETURNS ALL OFFER DATA OF POSTS THAT NEED TO BE ADDED
$sql = "SELECT a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z FROM offer WHERE modified = '1';";
$rst = $wpdb->get_results($sql,ARRAY_N); //run query
if(is_null($rst)){
  echo mysql_error() . "\n";
}else{
  echo "done \n";
}
echo "creating posts \n";;
foreach($rst as $r){
  //print_r($r);
  $my_post = array(
       'post_title' => $r[0],
       'post_status' => 'publish',
       'post_author' => 72,
       'post_category' => array(16)
    );

  // Insert the post into the database
  $newOffer = wp_insert_post( $my_post );

  if(!$newOffer){
    echo "problem creating the post " . $r[0];
  }

  array_unshift($r,$newOffer); // so that the data positions matches when calling updateMetaData 

  if($newOffer){
    updateMetaData($newOffer,$r);
  }
}

function updateMetaData($id,$data){
  global $wpdb;
  $category = $wpdb->get_results('SELECT categoryName FROM category WHERE categoryIdentifier ="'.mysql_escape_string($data[8]).'"');
  if($category){
    wp_set_object_terms( $id, array($category[0]->categoryName,16), 'category');
  }

  update_post_meta($id,'a','yes');
  update_post_meta($id,'b',$data[1]);
  $year = substr($data[2],0, -4);
  $mon = substr($data[2], -4, 2);
  $day = substr($data[2], -2); 
  update_post_meta($id,'c',$mon."/".$day."/".$year." 12:00:00 AM");
  $year = substr($data[3],0, -4);
  $mon = substr($data[3], -4, 2);
  $day = substr($data[3], -2); 
  update_post_meta($id,'d',$mon."/".$day."/".$year." 12:00:00 AM");
  update_post_meta($id,'e',$data[4]);//description
  update_post_meta($id,'f',$data[5]);
  update_post_meta($id,'g',$data[6]);
  update_post_meta($id,'h',$data[7]);
  update_post_meta($id,'i',$data[8]);
  update_post_meta($id,'j',$data[9]);
  update_post_meta($id,'k',$data[10]);
  update_post_meta($id,'l',$data[11]);
  update_post_meta($id,'m',$data[12]);
  update_post_meta($id,'n',$data[13]);
  update_post_meta($id,'o',$data[14]);
  update_post_meta($id,'p',$data[15]);
  update_post_meta($id,'q',$data[16]);
  update_post_meta($id,'r',$data[17]);
  update_post_meta($id,'s',$data[18]);
  update_post_meta($id,'t',$data[19]);
  update_post_meta($id,'u',$data[20]);
  update_post_meta($id,'v',$data[21]);
  update_post_meta($id,'w',$data[22]);
  update_post_meta($id,'x',$data[23]);
  update_post_meta($id,'y',$data[24]);
  update_post_meta($id,'z',$data[25]);
  $year = null;
  $mon = null;
  $day = null;
  //echo "wordpress post meta updated.\n";
}
share|improve this question
    
Is there a MySQL stored procedure that exists so I can create these posts directly using SQL queries and skip the PHP? - As the crux of this question is SQL, it would be better asked of the SQL experts at StackOverflow. –  Chip Bennett Feb 21 '13 at 20:58
    
I ask here because the SQL experts aren't familiar with all of the steps that go into inserting a post into WP. –  Depthless Feb 22 '13 at 18:06
    
But you're not doing anything in WordPress. You're just working with SQL and a database table. –  Chip Bennett Feb 22 '13 at 18:27

1 Answer 1

There are two reasons I can think of that may cause negative impact

  1. wp_insert_post retrieves the post from the DB after writing it to it. this is not fun but should have only a constant impact, so I'm guessing it is not your main problem

  2. Each post is being stored in memory as part of the caching process, and in the end this makes your server choke when it runs out of physical memory and starts swapping. try calling wp_suspend_cache_addition which suspends caching in the beginning of your script.

wp_suspend_cache_addition(true);

echo "get list of posts to be created in wordpress \n";
share|improve this answer
    
1. Is there something better I can use than wp_insert_post? 2. The server isn't running out of physical memory. During the whole import it still has 2GB free. –  Depthless Feb 22 '13 at 16:41
    
1. don't think so, and have you tried the change? –  Mark Kaplun Feb 22 '13 at 16:48
    
I attempted the change. No increase in performance though :(. I also added the code that shows how the meta_data is inserted. I'm pretty sure this is what really slows it down. –  Depthless Feb 22 '13 at 17:54
    
Then you need to try to analyze where is the bottleneck. Maybe first comment out the meta data code to localize the cause. In any case try to replace update_post_meta' with add_post_meta`. Another option for the meta is to construct an SQL query for that, should not be very complicated as the meta table structure is simple. –  Mark Kaplun Feb 22 '13 at 19:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.